PostgreSQL 11 – default timestamp column

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? Could anyone explain in some details please.

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?

Answer

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.