postgresqltimetimestamp-with-timezonetruncation

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

Solution

  • 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