From: | Steve Crawford <scrawford(at)pinpointresearch(dot)com> |
---|---|
To: | Niels Jespersen <NJN(at)dst(dot)dk> |
Cc: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: timestamp and timestamptz |
Date: | 2020-04-15 18:06:56 |
Message-ID: | CAEfWYyzDjwuozw2SQRkWqRWFFAf9oPJvvhdU70s3V6UTx-ymaw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Apr 15, 2020 at 11:06 AM Steve Crawford <
scrawford(at)pinpointresearch(dot)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 <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.
>>
>>
>>
>> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Lewis | 2020-04-15 20:44:55 | Re: GENERATED STORED columns and table rewrites? |
Previous Message | Steve Crawford | 2020-04-15 18:06:23 | Re: timestamp and timestamptz |