Обсуждение: Timestamps

Hi all,

i have a Debian Server here which is using an NTP server for time
synchronization. At the DST shifts, the server time is correctly set.
In the database on the server i have a table with a column which
contains timestamps but the type of the column is char(30). The
timestamps in this column are compared with the local time on the
server to check if one of the entries is "too old". The problem is
that when the shift to summertime is made (one hour to the future) the
check returns that all entries are too old.
So i need to store the timestamp in the database independent of the
configured timezone or DST and a SQL statement to get the entries
which are too old. The timestamps also have to be shown in a html site
using jsp in local time so i would also need another statement that
performs the conversion to the currently configured timezone and DST
setting.

thx,
Peter

Peter Bauer wrote:
> Hi all,
>
> i have a Debian Server here which is using an NTP server for time
> synchronization. At the DST shifts, the server time is correctly set.
> In the database on the server i have a table with a column which
> contains timestamps but the type of the column is char(30).

I'm assuming you know "don't do that". I'm curious why char(30) was chosen.

 > The
> timestamps in this column are compared with the local time on the
> server to check if one of the entries is "too old". The problem is
> that when the shift to summertime is made (one hour to the future) the
> check returns that all entries are too old.
> So i need to store the timestamp in the database independent of the
> configured timezone or DST and a SQL statement to get the entries
> which are too old. The timestamps also have to be shown in a html site
> using jsp in local time so i would also need another statement that
> performs the conversion to the currently configured timezone and DST
> setting.

Try using "timestamp with time zone". See the manuals for details.

--
   Richard Huxton
   Archonet Ltd