On Wed, Apr 15, 2020 at 11:06 AM Steve Crawford < scrawf...@pinpointresearch.com> wrote:
> 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 <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. >> >> >> >> 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 >> > Oops - sorry for top-post. Darn GMail. -Steve
