date_trunc drops timezone

[waitingkuo]

Describe the bug

date_trunc drops timezone while the input is timestamptz

To Reproduce

❯ select date_trunc('hour', timestamptz '2000-01-01T00:00:00Z');
+------------------------------------------------------+
| datetrunc(Utf8("hour"),Utf8("2000-01-01T00:00:00Z")) |
+------------------------------------------------------+
| 2000-01-01T00:00:00                                  |
+------------------------------------------------------+
1 row in set. Query took 0.002 seconds.

Expected behavior

2000-01-01T00:00:00Z

Additional context

No response

[waitingkuo]

@tustvold i think this issue depends on #5952 as well

[Weijun-H]

I am willing to handle this issue this weekend.

[Weijun-H]

The problem probably does not arise from the usage of date_trunc, but rather from the utilization of timestamp and timestamptz.

❯ select  timestamp '2000-01-01T00:00:00+08:00';
+-----------------------------------+
| Utf8("2000-01-01T00:00:00+08:00") |
+-----------------------------------+
| 1999-12-31T16:00:00               |
+-----------------------------------+
1 row in set. Query took 0.000 seconds.
❯ select  timestamptz '2000-01-01T00:00:00+08:00';
+-----------------------------------+
| Utf8("2000-01-01T00:00:00+08:00") |
+-----------------------------------+
| 1999-12-31T16:00:00Z              |
+-----------------------------------+

But I did not find the related stuff about the timestamptz definition 🤔.

[tustvold]

I think there are two related issues:

• The output always lacks a timezone
• The truncation should occur in the timestamp's timezone, not UTC

❯ set timezone to '+08:00';
0 rows in set. Query took 0.001 seconds.
❯ select  timestamptz '2000-01-01T00:00:00';
+-----------------------------+
| Utf8("2000-01-01T00:00:00") |
+-----------------------------+
| 2000-01-01T00:00:00+08:00   |
+-----------------------------+
❯ select date_trunc('day', timestamptz '2000-01-01T00:00:00');
+-----------------------------------------------------+
| date_trunc(Utf8("day"),Utf8("2000-01-01T00:00:00")) |
+-----------------------------------------------------+
| 1999-12-31T00:00:00                                 |
+-----------------------------------------------------+
1 row in set. Query took 0.002 seconds.

The output in the final case should be 2000-01-01T00:00:00+08:00