Thread: date_trunc problems crossing DST border

[ PostgreSQL 7.4.5, Linux (Debian sarge) ]

The date_trunc function is making some off-by-one errors when working at
a DST border.

=> select date_trunc('minute', '2004-10-31 01:00:00-05'::timestamptz);
       date_trunc
------------------------
 2004-10-31 01:00:00-04


Expecting 2004-10-31 01:00:00-05, same error happens with minutes after
1 am.  The timezone error also happens when truncating to minutes,
seconds, hours.  I suspect this is happening because there are two
01:00:00 local time this day because of the "fall back" DST switch.

--
    -Cheetah
"Reality is that which, when you stop believing in it, doesn't go away".
                -- Philip K. Dick
GPG pubkey fingerprint: A57F B354 FD30 A502 795B 9637 3EF1 3F22 A85E 2AD1

"Matthew \"Cheetah\" Gabeler-Lee" <cheetah@fastcat.org> writes:
> [ PostgreSQL 7.4.5, Linux (Debian sarge) ]
> The date_trunc function is making some off-by-one errors when working at
> a DST border.

> => select date_trunc('minute', '2004-10-31 01:00:00-05'::timestamptz);
>        date_trunc
> ------------------------
>  2004-10-31 01:00:00-04

This is not an off-by-one error.  What it's doing is taking the
truncated timestamp value as current local time (ie, it recomputes
the appropriate timezone offset), and so you get the equivalent of
'2004-10-31 01:00:00'::timestamptz which is interpreted as DST.

(That surprises me in itself --- I thought the rule for ambiguous
times was to use the local-standard-time interpretation --- but
the point here is about date_trunc.)

I think it is reasonable for it to be recalculating the TZ offset with
trunc levels of DAY or more, since otherwise you might get a result that
should be local midnight and isn't.  Arguably the recalculation is wrong
for levels of HOUR or less though.  Normally it would make no difference,
and where it does make a difference the result is evidently surprising.

            regards, tom lane