Re: Daylight savings time confusion

On 03/15/2010 12:40 PM, Rob Richardson wrote:
> 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!

First, the time change occurs at 2:00 am Sunday morning which is 5 five
hours after 9:00 pm Saturday. Second the timestamps below show a start
time of 39 minutes after 9 which a little more than just after:) Are you
sure about the time?

>
> 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?

Some other process updated either field?

>
> 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
>


--
Adrian Klaver
adrian.klaver@gmail.com