PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Does date_trunc go through 2 transformations even before truncation?

I was surprised select date_trunc('day','2021-02-16 20:00+04'::timestamptz); returned 2021-02-17 00:00:00+08 in postgres.

It’s like we moved forward in time from 16th to 17th.

I want to check my understanding and find sources explaining this (ideally official).

The session has set timezone = 'Asia/Singapore'; --utc+8

My understanding

There are 2 transformations happening before truncation:

  1. 20:00+04 turns into 16:00+00 stored as an 8 bit integer (epoch time microseconds) postgres uses to represent instants in physical time (as opposed to civil time mentioned in https://errorprone.info/docs/time)
  2. When date_trunc runs, 16:00+00 becomes next day 00:00+08 because date_trunc interprets in local time (session timezone)

I tested the hypothesis by changing input to 2021-02-16 20:00+05, which outputs 2021-02-16 00:00:00+08. (no day increment).

I guess this is because the 1st transformtion went backwards 1 more hour to 15:00+00, so the 2nd transformation only reached 11pm of day 16th instead of 12am on 17th?

I couldn’t find any docs mentioning this double interpretation behaviour. Closest is below:

When the input value is of type timestamp with time zone, the truncation is performed with respect to a particular time zone; for example, truncation to day produces a value that is midnight in that zone. By default, truncation is done with respect to the current TimeZone setting, but the optional time_zone argument can be provided to specify a different time zone.

https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC

Tags:

2

Answers


  1. 0

    What you are seeing:

    set timezone = 'Asia/Singapore';

--Casting to timestamptz rotates the timestamp to localtime.
select '2021-02-16 20:00+04'::timestamptz;
      timestamptz       
------------------------
 2021-02-17 00:00:00+08


--Using the returned value from above.
--date_trunc to 'day' lands on Midnight.
select date_trunc('day', '2021-02-17 00:00:00+08'::timestamptz);
       date_trunc       
------------------------
 2021-02-17 00:00:00+08
    Login or Signup to reply.
  2. 0

    The answer is already in the docs that you have quoted. Specifically:

    By default, truncation is done with respect to the current TimeZone setting, but the optional time_zone argument can be provided to specify a different time zone.

    https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC

    The timestamp 2021-02-16 20:00+04 is the same instant as 2021-02-17 00:00+08. As you have set the timezone to Asia/Shanghai, the truncation happens with respect to the date that this instant represents in Asia/Shanghai, which was the 17th.

    If you want to truncate the timestamp in a different timezone then you can provide the optional time_zone argument. For example:

    postgres=# select date_trunc('day', '2021-02-16 20:00+04'::timestamptz, 'Asia/Yerevan');
       date_trunc
------------------------
 2021-02-16 04:00:00+08
(1 row)

    NB. Asia/Yerevan has been used here as the timezone of Armenia is 00+04.

    Note that the returned instant is not midnight in the output timezone. That is because when 2021-02-16 20:00:00+04 is truncated to the day in Asia/Yerevan you get 2021-02-16 00:00:00+04. As you have set the timezone to Asia/Shanghai, this instant is then shifted into that timezone when outputted. Thus, returning 2021-02-16 04:00:00+08.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search