2

I have a table like this.

create table public.test123
(id int not null primary key, dt timestamp null default clock_timestamp());

I then insert into it from 2 different client sessions, the first session is from PG Admin, the second one is from DBeaver.

  1. In the first session when I run show timezone; it returns UTC.
    So this session's timezone is UTC. So from this session I do an insert as follows.

     insert into public.test123(id) values (1);

  2. In the second session I have timezone America/New_York and I do this.

     insert into public.test123(id) values (2);

I run the two inserts just a few seconds apart from each other.

But I get two very different values in the DB e.g.

    id|dt                 |
    --|-------------------|
     1|2020-06-05 14:38:18|
     2|2020-06-05 10:38:26|

I always thought that in such scenario the clock_timestamp() call is executed on the server, and the timezone of the client session should not matter. And I expected to get two values which are a few seconds apart, and not 4 hours apart.

What am I missing?

And also, is there any way to get a timestamp independent of the client session's timezone?

How can I create a column with default timestamp values which are really independent on the client session's timezone?

Improve this question
1
  • Seems the solution is to create the column as timestamp with time zone. I am not sure I quite get it, but this seems to work fine for my use case. By default timestamp means timestamp without time zone. Commented Jun 5, 2020 at 15:43

2 Answers 2

Reset to default
6

tl;dr: Switch to timestamptz.

I'll refer to the timestamp types by their short names: timestamp without time zone is timestamp and timestamp with time zone is timestamptz.

Both timestamp and timestamptz store an absolute point in time. timestamp with time zone also records the time zone it was entered in.

For example, 2020-06-05 20:22:48Z and 2020-06-05 13:22:48-0700 both represent the same moment in time, but the first is in Universal Coordinated Time and the other is 7 hours offset (Pacific Daylight Time).

timestamptz will store 2020-06-05 13:22:48-0700 as 2020-06-05 20:22:48, the absolute point in time, but also remember that it was entered with 7 hours offset. When you retrieve it you'll get 2020-06-05 13:22:48-0700 which is the same time as 2020-06-05 20:22:48Z.

timestamp will ignore the time zone completely. It stores 2020-06-05 13:22:48-0700 as 2020-06-05 13:22:48, no time zone, no conversion, and that's it.

On to the specific problem: dt timestamp null default clock_timestamp()

clock_timestamp() returns timestamptz, but you're storing it in a timestamp. Postgres will cast one to the other by simply dropping the time zone. It will not convert it. If the time zone is in UTC and it's 2020-06-05 20:22:48Z it will store 2020-06-05 20:22:48. If the time zone is in America/New York and it's 2020-06-05 16:22:48-0400 it will store 2020-06-05 16:22:48.

And also... is there any way to get a timestamp independent of the client session's timezone?

They are all independent of the client's time zone, it's the type conversion that's a problem. Switch to timestamptz.

Time zones are complicated, but here's two strategies to deal with them when moving timestamps in and out of the database.

  1. Use timestamp and always work in UTC.
  2. Use timestamptz and always include a time zone.

The former is simpler, and many frameworks take this approach. But I prefer the latter because, as complex and infuriating as they are, people like the time to be vaguely in sync with the Sun.

Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

Thank you, that explains it. I suspected it's something like this. I just needed someone to clarify it explicitly and in some details. Thanks.
So timezone and timezonetz - internally they don't store the number of milliseconds since the epoch. Right? If they did... then both datatypes would work in my use case, I think. So internally they just store date value and time value (and timezonetz also stores the tz), is that understanding correct?
@peter.petrov No. Both timezone and timezonetz do store microseconds since an epoch (not 1970). timezonetz also stores the time zone. The time zone only affects how the timestamp is presented. See 8.5. Date/Time Types and 8.5.3 Time Zones.
But if they do, then why the client session timezone matters? The number of millis/micros since the epoch is (almost) the same in my two inserts. Then when I read the two in NY timezone why do I see values different by 4 hours? By epoch I mean that moment of time "00:00 UTC 1/1/1970".
@pdem Yes, timestamptz does not store the time zone (it's a misleading name, but that's the SQL standard). It uses the time zone to convert to UTC and stores that. When you retrieve it, it converts to your local time zone. If you want to retain the time zone, you need to store it and apply it yourself. timestamps are always stored as UTC.
|
1

Yes, timestamp with time zone, which is an absolute timestamp, would be the correct data type in your case.

If you use timestamp without time zone, the result of clock_timestamp(), which is a timestamp with time zone, is converted (cast implicitly) to a timestamp without time zone according to the current setting of timezone.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.