Re: Daylight Savings Time handling on persistent connections

On Sunday 31 October 2004 11:44 am, Tom Lane wrote:
> Randall Nortman <postgreslists@wonderclown.com> writes:
> > Ah, I see now.  PostgreSQL is behaving a bit differently than I
> > expected.  The timestamp string above is ambiguous in the
> > timezone US/Eastern -- it could be EST or EDT.  I was expecting
> > PostgreSQL to resolve this ambiguity based on the current time
> > when the SQL statement is processed
>
> I think this would be a very bad thing for it to do.  It might seem
> to make sense for a timestamp representing "now", but as soon as
> you consider a timestamp that isn't "now" it becomes a sure way to
> shoot yourself in the foot.

Would it help to add the PG locale TZ to the insert statement? For
example the following queries return the TZ as text.

select to_char(now(),'tz');
 to_char
---------
 pst

select to_char(now()-'3 days'::interval,'tz');
 to_char
---------
 pdt

So the following might fix this particular situation:
insert into sensor_readings_numeric (...) values (...,'2004-10-31
01:00:00 ' || to_char(now(),'tz'),...)

I realize that it assumes that the data is being inserted at the time
it was taken so a reading taken just before DST changes and inserted
just after will be incorrect but it may work for this particular app.

Of course the better solution is to have the application generate a
fully-qualified timestamp with time zone. Generating all the
timestamps in UTC and explicitly specifying that in the insert is
probably the easiest way to go. Your queries will still have your
local-appropriate TZ:

select '2004-10-31 00:00:00+00'::timestamptz;
      timestamptz
------------------------
 2004-10-30 17:00:00-07

select '2004-11-01 00:00:00+00'::timestamptz;
      timestamptz
------------------------
 2004-10-31 16:00:00-08


Cheers,
Steve