From: Christoph Haller <ch@rodos.fzk.de>
To: pgsql-sql@postgresql.org
Subject: Re: TIMESTAMP / summertime
Date: Thu, 07 Apr 2005 14:26:36 +0200
Message-ID: <425526FC.F9AA64C6@rodos.fzk.de> (raw)
References: <4253FB61.2050502@numerixtechnology.de>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ch@rodos.fzk.de
Subject: Re: TIMESTAMP / summertime
In-Reply-To: <425526FC.F9AA64C6@rodos.fzk.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox