Skip site navigation (1) Skip section navigation (2)

Re: date_trunc problems crossing DST border

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Matthew \"Cheetah\" Gabeler-Lee" <cheetah(at)fastcat(dot)org>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: date_trunc problems crossing DST border
Date: 2004-10-31 15:56:37
Message-ID: 28047.1099238197@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
"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

In response to

pgsql-bugs by date

Next:From: Martin PittDate: 2004-10-31 21:59:57
Subject: psql: set \encoding according to the current locale
Previous:From: Ralph HeinkelDate: 2004-10-31 15:37:26
Subject: possible bug using combination of 'serial' and rule

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group