Re: Daylight savings time confusion - Mailing list pgsql-general

On 3/15/2010 2: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!
>
> 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
>

My first thought is the server is using libraries that don't know the
DST was brought forward 3 weeks earlier than last year,  its clock is
all confused.

i would check the time on Postgresql Server making sure it read out
correctly.  below was run on pg 8.4  windows 2008 server

Select current_timestamp, current_timestamp at time zone 'UTC';
"2010-03-15 16:43:11.382-04";"2010-03-15 20:43:11.382"


All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by
ourproprietary quotation system. Quotations received via any other form of communication will not be honored. 

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other
informationproprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it
addresses.If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified
thatany unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have
receivedthis e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this
e-mailimmediately. 
Thank you.