Daylight savings time confusion

From: "Rob Richardson" <Rob(dot)Richardson(at)rad-con(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Daylight savings time confusion
Date: 2010-03-15 19:40:51
Message-ID: 04A6DB42D2BA534FAC77B90562A6A03D012F4992@server.rad-con.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Greetings!

Our database monitors the progression of steel coils through the
annealing process. The times for each step are recorded in wallclock
time (US eastern time zone for this customer) and in UTC time. During
standard time, the difference will be 5 hours, and during daylight
savings time the difference will be 4 hours.

I just looked at the record for a charge for which heating started just
after 9:00 Saturday night, less than 3 hours before the change to
daylight savings time. The UTC time stored for this event is six hours
later!

The function that writes these times first stores the UTC time in a
variable named UTCTimestamp:

select into UTCTimestamp current_timestamp at time zone 'UTC';

Then, later in the function, the two times get written into the record
(along with some other stuff):

update charge set
status=ChargeStatus,fire_date=current_timestamp,
fire_date_utc=UTCTimestamp, fire_user=FurnaceTender,
updated_by=UserId,updated_date=current_timestamp where charge=ChargeNum;

Can someone explain why fire_date is 2010-03-13 21:39:51.744 and
fire_date_utc is 2010-03-14 03:39:51.744 for this record?

There is another charge that began firing five and a half hours before
the DST switch. The difference between its fire_date and fire_date_utc
times is five hours, as expected.

RobR

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Magnus Hagander 2010-03-15 19:47:15 Re: Installing Postgresql on Windows XP embedded
Previous Message Vick Khera 2010-03-15 19:27:38 Re: hardware for a server