Skip to content

date_trunc drops timezone #5976

Description

@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

Activity

  1. waitingkuo commented on Apr 12, 2023

    @waitingkuo
    ContributorAuthor

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

  2. Weijun-H commented on Jun 30, 2023

    @Weijun-H
    Member

    I am willing to handle this issue this weekend.

  3. Weijun-H commented on Jun 30, 2023

    @Weijun-H
    Member

    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 🤔.

  4. tustvold commented on Jun 30, 2023

    @tustvold
    Contributor

    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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't working

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions