Re: date_trunc problems crossing DST border

"Matthew \"Cheetah\" Gabeler-Lee" <cheetah(at)fastcat(dot)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