Re: timestamp and timestamptz

[Magnus Hagander <magnus@xxxxxxxxxxxx>]

On Wed, Apr 15, 2020 at 7:50 PM Niels Jespersen <NJN@xxxxxx> wrote:

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.

Yes, you should.

 

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;

As long as you use option 1:

SELECT read_time

will return the time in CET (as a timestamptz) after you've set timezone to 'cet'. If you set timezone to 'utc' it will directly return utc.

SELECT read_time AT TIME ZONE 'utc'

will return the time in UTC (as a timestamp)

And just make sure you have done a "set time zone 'utc'" before you *load* the data, and everything should just work automatically.

--

 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/