Re: timestamp and timestamptz

On Wed, Apr 15, 2020 at 7:50 PM Niels Jespersen <NJN(at)dst(dot)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.
>

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/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>