Repository: risingwavelabs/risingwave (language: python)

Issue:
support function `timezone(zone, timestamp)`
### Is your feature request related to a problem? Please describe.

The function `timezone(zone, timestamp)` is equivalent to the SQL-conforming construct `timestamp AT TIME ZONE zone`.
https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT
We have the latter

### Describe the solution you'd like

_No response_

### Describe alternatives you've considered

_No response_

### Additional context

_No response_
Sqlsmith: Sql feature generation
As we add more features, these should be tested via sqlsmith as well.

SQL Features:
- [ ] Generate other table functions: `generate_series`, `unnest`. See: https://github.com/singularity-data/risingwave/issues/4540
- [ ] Generate array_agg, jsonb_agg, jsonb_object_agg
- [ ] Generate select from System Tables
- [ ] Generate `NATURAL JOIN`
- [ ] Generate `JOIN` with `USING`
- [ ] Generate query w/o `GROUP BY`
- [ ] Randomly toggle session variables
- [ ] Generate `pow`/`exp`.
- [ ] Generate TopN by group: https://www.risingwave.dev/docs/current/sql-pattern-topn/
- [ ] Generate jsonb exprs, see: https://github.com/risingwavelabs/risingwave/issues/7714
- [ ] #7132
- [x] Generate order-by-clauses with `NULLS { FIRST | LAST }` (will be done in https://github.com/risingwavelabs/risingwave/pull/10864/commits/000cbc11cbc04f20abfd127b1bf4b3fd24e75c44)
- [ ] Generate `max`,`min`,`count` for `timestamptz`
- [ ] Generate `bit_and`, `bit_or` and `big_xor` in batch queries, and `bit_xor` in streaming queries.
- [ ] Generate array range access.
- [ ] https://github.com/risingwavelabs/risingwave/issues/10241
- [ ] Generate window functions other than `row_number`, `rank`.
- [ ] Generate `ALTER` DDL statements.\
- [ ] https://github.com/risingwavelabs/risingwave/issues/10516
- [ ] Generate temporal filter in sqlsmith


Discussion / hints:
Hi @lmatz, im new to this but interested in the issue. Do you have any pointers where to start?
Hi @dttung2905


Appreciate your interest in contributing to RW!
https://github.com/risingwavelabs/risingwave/blob/main/src/expr/impl/src/scalar/extract.rs is a time-related function, you can mimic the way how it is integrated into the codebase.

Feel free to assign the issue to yourself! Thanks
@lmatz thank you for the pointer. I will take a look at it today. You can assign it to me 🙏


Fix patch:
diff --git a/src/expr/impl/src/scalar/timestamptz.rs b/src/expr/impl/src/scalar/timestamptz.rs
index 7dcc718d728ef..06f9f2a7ffba4 100644
--- a/src/expr/impl/src/scalar/timestamptz.rs
+++ b/src/expr/impl/src/scalar/timestamptz.rs
@@ -41,6 +41,14 @@ pub fn f64_sec_to_timestamptz(elem: F64) -> Result<Timestamptz> {
     Ok(Timestamptz::from_micros(micros))
 }
 
+#[function("at_time_zone(timestamptz, varchar) -> timestamp")]
+pub fn timestamptz_at_time_zone(input: Timestamptz, time_zone: &str) -> Result<Timestamp> {
+    let time_zone = Timestamptz::lookup_time_zone(time_zone).map_err(time_zone_err)?;
+    let instant_local = input.to_datetime_in_zone(time_zone);
+    let naive = instant_local.naive_local();
+    Ok(Timestamp(naive))
+}
+
 #[function("at_time_zone(timestamp, varchar) -> timestamptz")]
 pub fn timestamp_at_time_zone(input: Timestamp, time_zone: &str) -> Result<Timestamptz> {
     let time_zone = Timestamptz::lookup_time_zone(time_zone).map_err(time_zone_err)?;
@@ -96,14 +104,6 @@ pub fn str_to_timestamptz(elem: &str, time_zone: &str) -> Result<Timestamptz> {
     })
 }
 
-#[function("at_time_zone(timestamptz, varchar) -> timestamp")]
-pub fn timestamptz_at_time_zone(input: Timestamptz, time_zone: &str) -> Result<Timestamp> {
-    let time_zone = Timestamptz::lookup_time_zone(time_zone).map_err(time_zone_err)?;
-    let instant_local = input.to_datetime_in_zone(time_zone);
-    let naive = instant_local.naive_local();
-    Ok(Timestamp(naive))
-}
-
 /// This operation is zone agnostic.
 #[function("subtract(timestamptz, timestamptz) -> interval")]
 pub fn timestamptz_timestamptz_sub(l: Timestamptz, r: Timestamptz) -> Result<Interval> {
diff --git a/src/frontend/src/binder/expr/function/builtin_scalar.rs b/src/frontend/src/binder/expr/function/builtin_scalar.rs
index 66c28b0ba24d6..7570f72c5d095 100644
--- a/src/frontend/src/binder/expr/function/builtin_scalar.rs
+++ b/src/frontend/src/binder/expr/function/builtin_scalar.rs
@@ -210,7 +210,7 @@ impl Binder {
                 ("scale", raw_call(ExprType::Scale)),
                 ("min_scale", raw_call(ExprType::MinScale)),
                 ("trim_scale", raw_call(ExprType::TrimScale)),
-
+                // date and time
                 (
                     "to_timestamp",
                     dispatch_by_len(vec![
@@ -223,8 +223,16 @@ impl Binder {
                 ("make_date", raw_call(ExprType::MakeDate)),
                 ("make_time", raw_call(ExprType::MakeTime)),
                 ("make_timestamp", raw_call(ExprType::MakeTimestamp)),
-                ("to_date", raw_call(ExprType::CharToDate)),
                 ("make_timestamptz", raw_call(ExprType::MakeTimestamptz)),
+                ("timezone", rewrite(ExprType::AtTimeZone, |mut inputs|{
+                    if inputs.len() == 2 {
+                        inputs.swap(0, 1);
+                        Ok(inputs)
+                    } else {
+                        Err(ErrorCode::ExprError("unexpected arguments number".into()).into())
+                    }
+                })),
+                ("to_date", raw_call(ExprType::CharToDate)),
                 // string
                 ("substr", raw_call(ExprType::Substr)),
                 ("length", raw_call(ExprType::Length)),


Test patch:
diff --git a/e2e_test/batch/functions/timezone.slt.part b/e2e_test/batch/functions/timezone.slt.part
new file mode 100644
index 0000000000000..344e00e735dc0
--- /dev/null
+++ b/e2e_test/batch/functions/timezone.slt.part
@@ -0,0 +1,112 @@
+statement ok
+SET TimeZone to 'UTC';
+
+query T
+SELECT timezone('Europe/Moscow', '2011-03-27 02:00:00'::timestamp);
+----
+2011-03-26 23:00:00+00:00
+
+query T
+SELECT timezone('Europe/Moscow', '2011-03-27 02:59:59'::timestamp);
+----
+2011-03-26 23:59:59+00:00
+
+query T
+SELECT timezone('Europe/Moscow', '2011-03-27 03:00:00'::timestamp);
+----
+2011-03-26 23:00:00+00:00
+
+query T
+SELECT timezone('Europe/Moscow', '2014-10-26 00:59:59'::timestamp);
+----
+2014-10-25 20:59:59+00:00
+
+query T
+SELECT timezone('Europe/Moscow', '2014-10-26 01:00:00'::timestamp);
+----
+2014-10-25 22:00:00+00:00
+
+query T
+SELECT timezone('Europe/Moscow', '2014-10-26 01:00:01'::timestamp);
+----
+2014-10-25 22:00:01+00:00
+
+query T
+SELECT timezone('Asia/Shanghai', '2024-11-20 20:00:00 UTC'::timestamptz);
+----
+2024-11-21 04:00:00
+
+query T
+SELECT timezone('Asia/Shanghai', '2023-02-28 20:00:00 UTC'::timestamptz);
+----
+2023-03-01 04:00:00
+
+query T
+SELECT timezone('Asia/Shanghai', '2024-02-28 20:00:00 UTC'::timestamptz);
+----
+2024-02-29 04:00:00
+
+query T
+SELECT timezone('Europe/Moscow', '2011-03-26 22:00:00 UTC'::timestamptz);
+----
+2011-03-27 01:00:00
+
+query T
+SELECT timezone('Europe/Moscow', '2011-03-26 23:00:00 UTC'::timestamptz);
+----
+2011-03-27 03:00:00
+
+query T
+SELECT timezone('Europe/Moscow', '2011-03-26 23:00:01 UTC'::timestamptz);
+----
+2011-03-27 03:00:01
+
+query T
+SELECT timezone('Europe/Moscow', '2011-03-27 01:00:00 UTC'::timestamptz);
+----
+2011-03-27 05:00:00
+
+query T
+SELECT timezone('Europe/Moscow', '2011-03-27 02:00:00 UTC'::timestamptz);
+----
+2011-03-27 06:00:00
+
+query T
+SELECT timezone('Europe/Moscow', '2011-03-27 02:59:59 UTC'::timestamptz);
+----
+2011-03-27 06:59:59
+
+query T
+SELECT timezone('Europe/Moscow', '2011-03-27 03:00:00 UTC'::timestamptz);
+----
+2011-03-27 07:00:00
+
+query T
+SELECT timezone('Europe/Moscow', '2014-10-25 21:59:59 UTC'::timestamptz);
+----
+2014-10-26 01:59:59
+
+query T
+SELECT timezone('Europe/Moscow', '2014-10-25 22:00:00 UTC'::timestamptz);
+----
+2014-10-26 01:00:00
+
+query T
+SELECT timezone('Europe/Moscow', '2014-10-25 22:59:59 UTC'::timestamptz);
+----
+2014-10-26 01:59:59
+
+query T
+SELECT timezone('Europe/Moscow', '2014-10-25 23:00:00 UTC'::timestamptz);
+----
+2014-10-26 02:00:00
+
+query T
+SELECT timezone('Europe/Moscow', '2014-10-25 23:00:01 UTC'::timestamptz);
+----
+2014-10-26 02:00:01
+
+query T
+SELECT timezone('Europe/Moscow', '2014-10-26 02:00:01 UTC'::timestamptz);
+----
+2014-10-26 05:00:01
diff --git a/src/tests/regress/data/schedule b/src/tests/regress/data/schedule
index d1cfd9ab86253..54cf97a5ed4d6 100644
--- a/src/tests/regress/data/schedule
+++ b/src/tests/regress/data/schedule
@@ -12,4 +12,4 @@ test: strings date time timestamp interval
 test: case arrays delete
 test: jsonb jsonb_jsonpath
 test: regex
-test: contrib-pgcrypto-rijndael
+test: contrib-pgcrypto-rijndael
\ No newline at end of file