Postgresql date_trunc with time zone shifts zone by 1 hr

Question

we are using Postgresql 9.4 and i noticed a strange behavior when using date_trunc. The time zone in result is shifted by 1hr:

select date_trunc('year','2016-08-05 04:01:58.372486-05'::timestamp with time zone);
       date_trunc
------------------------
2016-01-01 00:00:00-06

There is no such behavior when truncating to for example day:

select date_trunc('day','2016-08-05 04:01:58.372486-05'::timestamp with time zone);
       date_trunc
------------------------
2016-08-05 00:00:00-05

Is this expected behavior? If so what is the logic behind that?

Answer 1

The date_trunc(text, timestamptz) variant seems a bit under-documented, so here are my findings:

1) below the day precision (first parameter) the time zone offset of the result is always the same as the second parameters' offset.

2) at or above day precision, the time zone offset is recalculated, according to the current TimeZone configuration parameter (which can be set with set time zone '...' or set TimeZone to '...'). The recalculated offset is always the same as it would be on that exact time-instant with the same TimeZone configuration parameter in effect. So, f.ex. when the TimeZone parameter contains DST information, then the offset is aligned accordingly. However, when the actual TimeZone parameter does not contain DST informations (such as a fix offset), the result's time zone offset is untouched.

All-in-all, the date_trunc(text, timestamptz) function can be simulated with the date_trunc(text, timestamp) variant and the at time zone operators:

date_trunc('month', tstz)

should be equivalent to:

date_trunc('month', tstz at time zone current_setting('TimeZone')) at time zone current_setting('TimeZone'))

At least, that's what I thought. As it turned out, there are some TimeZone configuration settings, which are problematic. Because:

PostgreSQL allows you to specify time zones in three different forms:

• A full time zone name, for example America/New_York. The recognized time zone names are listed in the pg_timezone_names view (see Section 50.80). PostgreSQL uses the widely-used IANA time zone data for this purpose, so the same time zone names are also recognized by much other software.

• A time zone abbreviation, for example PST. Such a specification merely defines a particular offset from UTC, in contrast to full time zone names which can imply a set of daylight savings transition-date rules as well. The recognized abbreviations are listed in the pg_timezone_abbrevs view (see Section 50.79). You cannot set the configuration parameters TimeZone or log_timezone to a time zone abbreviation, but you can use abbreviations in date/time input values and with the AT TIME ZONE operator.

(The third is the fix offset, or its POSIX form, but that's not important here).

As you can see, abbreviations cannot be set to TimeZone. But there are some abbreviations, which is also considered a full time zone name, f.ex. CET. Thus, set time zone 'CET' will succeed, but will actually use CEST in the summer time. But at time zone 'CET' will always refer to the abbreviation, which is a fixed offset from UTC (and never CEST, for that one can use at time zone 'CEST'; but set time zone 'CEST' is invalid).

Here is a full list of time zone settings, which has incompatible meanings when they are used in set time zone vs. when they are used in at time zone (as of 9.6):

CET
EET
MET
WET

With the following script, you can check for your version:

create or replace function incompatible_tz_settings()
  returns setof text
  language plpgsql
as $func$
declare
  cur cursor for select name from pg_timezone_names;
begin
  for rec IN cur loop
    declare
      r pg_timezone_names;
    begin
      r := rec;
      execute format('set time zone %L', (r).name);
      if exists(select 1
                from   generate_series(current_timestamp - interval '12 months', current_timestamp + interval '12 months', interval '1 month') tstz
                where  date_trunc('month', tstz) <> date_trunc('month', tstz at time zone (r).name) at time zone (r).name) then
        return next (r).name;
      end if;
    end;
  end loop;
end
$func$;

http://rextester.com/GBL17756

Answer 2

It is expected to have two variants of date_trunc: one for timestamp and one for timestamptz, because the doc says:

All the functions and operators described below that take time or timestamp inputs actually come in two variants: one that takes time with time zone or timestamp with time zone, and one that takes time without time zone or timestamp without time zone. For brevity, these variants are not shown separately.

Should you want to better understand timestamp and timestamptz, read first the great answer here.

Then about date_trunc. According to my experiments and interpretation of various SO answers (like this one), everything behaves as if, when receiving a timestamptz, date_trunc first converts it to a timestamp. This conversion returns a timestamp in local time. Then truncation is performed: keep only the date and drop the hours/min/seconds.

To avoid this conversion (thanks pozs), provide a timestamp (not timestamptz) to date_trunc:

date_trunc('day', TIMESTAMPTZ '2001-07-16 23:38:40Z' at time zone 'UTC')

the part at time zone 'UTC' says "convert this timestamptz to a timestamp in UTC time" (the hour isn't affected by this conversion). Then date_trunc returns 2001-07-16 00:00:00.