Re: TIMESTAMP / summertime

T E Schmitz wrote:
>
> Hello,
>
> I *detest* British summertime. This year it took me two days to adjust.
> Now I am realizing that my program might need some adjusting too:
>
> Joking aside, I need some advice regarding TIMESTAMP colums and I can't
> quite get my head round this at the moment:
>
> I created a table TRANSAKTION with a TIMESTAMP column without qualifying
> "with/without time zone".
> My understanding is that this is equivalent to "TIMESTAMP without time
> zone"? (I am using Postgres 7.4.)
>
> I am accessing the database via a Java client program. The DB access
> code is generated by an O/R mapper. Client and server are in the same
> timezone.
>
> One of the things I need to do select records from TRANSAKTION, which
> fall within a certain time period, specified in days: e.g. 1st Mar 2005
> to 31st Mar 2005. In other words, I want to grab TRANSAKTIONs >= 1 Mar
> 00:00 and < 1 Apr 00:00.
>
> The generated WHERE clause is:
>
> WHERE (TRANSAKTION.THE_TIME>={ts '2005-03-01 00:00:00.0'} AND
> TRANSAKTION.THE_TIME<{ts '2005-04-01 01:00:00.0'})
>
> Should it be '2005-04-01 00:00:00.0' or 2005-04-01 01:00:00.0' ??
>
> Also, in autumn, when the clocks go back, I need to be able to
> distinguish between the two double hours.
>
> Sorry, if I sound confused. Unfortuantely, this is what I am ;-)
>
> --
>
> Regards/Gruß,
>
> Tarlika Elisabeth Schmitz
>
IIRC the countless threads regarding timezone adjustments,
which AFAIK include DST settings (Daylight Saving Time resp.
summertime),
this cannot ever be handled correctly, if one does not
specify the column in question as "with time zone".
In addition, IIRC in versions later than 7.4,
the whole timezone calculations have been re-written,
because using the UNIX tztab utilities ended up with
various mistakes on many UNIX derivates.
You might want to search the archive on "DST".
HTH

Regards, Christoph

Forgot to reply to the list, sorry.