Re: timestamp and timestamptz

Niels Jespersen <NJN(at)dst(dot)dk> writes:

> Hello all
>
>
>
> We have some data that have entered a timestamp column from a csv. The data in the csv are in utc. We want to access the data in
> our native timezone (CET).
>
>
>
> I am considering a few alternatives:
>
>
>
> 1. Early in the process, convert to timestamptz and keep this datatype.
>
> 2. Early in the process, convert to timestamp as understood in CET. This will imply by convention that the data in the timestamp
> column represents CET. Users will need to be told that data represents CET, even if data is somwhere in the future kept in another
> country in another timezone.
>
>
>
> I probably should choose 1 over 2. But I am a bit hesitant, probably because we almost never have used timestamptz.
>
>
>
> Can we agree that the below query is selecting both the original utc timestamps and 2 and 1 (as decribed above)?
>
>
>
> set timezone to 'cet';
>
> select read_time read_time_utc, (read_time at time zone 'utc')::timestamp read_time_cet, (read_time at time zone 'utc')::timestamptz
> read_time_tz from t limit 10;
>
>
>
> We are on Postgres 12.
>
>
>

Keep your life simple - just go with option 1. Keep all timestamps in
UTC and let clients deal with it in whatever way they need to. This will
also help deal with issues associated with daylight savings time (DST
can be a real pain as different locations have it and others don't and
the 'switchover' date is subject to political whims and can change).
Your option 2 will cause all sorts of issues and keep in mind that most
larger countries have multiple timezones, so even if your data is all
associated with a single country, you can potentially have multiple
conversion routines required. On most *nix systems, clock time is UTC as
well, so having everything in UTC really helps when you want to do
diagnosis across your database and system log files etc.

--
Tim Cross