to_char function has Daylight Savings Bug

Started by Dana Burdalmost 23 years ago2 messagesbugs
Jump to latest
#1Dana Burd
DBurd@cpr.org

to_char(timestamp, 'SSSS') has a Daylight Savings bug. It includes the
missing 2am hour in it's "seconds from midnight count" for DST begin day
(4/6/2003), and does not include the extra hour for DST end day
(10/26/2003).

both seconds1 and seconds2 below should be 7200. Note 4/6/2003 is start of
daylight savings for MST7MDT.

select '4/6/2003 3:00:00'::timestamp,
'4/6/2003 00:00:00'::timestamp,
to_char('4/6/2003 3:00:00'::timestamp,'SSSS') AS seconds1,
trunc(extract(epoch from '4/6/2003 3:00:00'::timestamp)-
extract(epoch from '4/6/2003 00:00:00'::timestamp)) AS
seconds2;

timestamptz | timestamptz | seconds1 | seconds2
------------------------+------------------------+----------+----------
2003-04-06 03:00:00-06 | 2003-04-06 00:00:00-07 | 10800 | 7200
(1 row)

Included explicit TZ info, gives the same.

select '4/6/2003 3:00:00 MDT'::timestamp,
'4/6/2003 00:00:00 MST'::timestamp,
to_char('4/6/2003 3:00:00 MDT'::timestamp,'SSSS') AS seconds1,
trunc(extract(epoch from '4/6/2003 3:00:00 MDT'::timestamp)-
extract(epoch from '4/6/2003 00:00:00 MST'::timestamp)) AS
seconds2;

timestamptz | timestamptz | seconds1 | seconds2
------------------------+------------------------+----------+----------
2003-04-06 03:00:00-06 | 2003-04-06 00:00:00-07 | 10800 | 7200
(1 row)

VERSION

% psql -V
psql (PostgreSQL) 7.2.3

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Dana Burd (#1)
Re: to_char function has Daylight Savings Bug

Dana Burd <DBurd@cpr.org> writes:

to_char(timestamp, 'SSSS') has a Daylight Savings bug. It includes the
missing 2am hour in it's "seconds from midnight count" for DST begin day
(4/6/2003), and does not include the extra hour for DST end day
(10/26/2003).

It's not readily apparent to me that this is a bug. The SSSS value is
evidently being calculated as HH*3600+MM*60+SS, so it corresponds to the
nominal time of 3:00:00 AM. I think it would be rather surprising if
SSSS *didn't* correspond to HH:MM:SS.

However, to_char() is intended to slavishly imitate Oracle's to_char(),
warts and all. What does Oracle do with this situation?

regards, tom lane