Re: timestamp and timestamptz

What is the exact format of the timestamp in the CSV? As long as it is in a "fully qualified" format, i.e. includes the time-zone offset, then you will have no problem as the data represents a point in time.

It is easier to conceptualize "time stamp with time zone" (timestamptz) as actually representing a "point in time". If your client is set to CET then the data will display as desired.

-Steve

On Wed, Apr 15, 2020 at 10:50 AM Niels Jespersen <NJN@dst.dk> 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.

 

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.

 

Regards Niels Jespersen