On Wed, Apr 15, 2020 at 7:50 PM Niels Jespersen <n...@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. >
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/>
