There are two representations for timestamp in postgres, timestamp, and timestamptz. Both store dates in the db as UTC, and the only difference between the two is that timestamptz uses to timezone to format the data for display. But I think this obscures what I would say is slightly unintuitive behavior when working with timestamps in postgres.

Converting between timestamp and timestamptz

Consider the following query:

postgres=# select 
pg_typeof(timestamp '2022-01-01' at time zone 'Pacific/Honolulu'), 
pg_typeof(timestamptz '2022-01-01' at time zone 'Pacific/Honolulu');
        pg_typeof         |          pg_typeof
--------------------------+-----------------------------
 timestamp with time zone | timestamp without time zone

I think it makes sense that a timestamp at time zone becomes a timestamptz, but not so much that a timestamptz at time zone would be converted back to a timestamp! This has implications for methods like date or date part, because these operate on the ‘display’ value.

For example:

postgres=# select date(timestamptz '2022-01-01T00:00+00:00');
    date
------------
 2021-12-31

The reason that the date of this timestamp is December 31st, as opposed to January 1st, is that when converted to the default timezone on my postgres db, the date is December 31st. We can verify this:

postgres=# select timestamptz '2022-01-01T00:00+00:00';
      timestamptz
------------------------
 2021-12-31 19:00:00-05
(1 row)

This means that the default timezone has a lot of bearing on the output of date, or date_part! Because of this, it’s best not to use timestamptz when dealing with methods like these, as they lead to unexpected outputs.

Implicit coercion between timestamp and timestamptz

Let’s create a table with one timestamp field and one timestamptz field.

postgres=# create table if not exists tt (
	timestamp_col timestamp
	timestamptz_col timestamptz
);

Let’s insert a value without a time zone into both of these fields.

postgres=# insert into tt(timestamp_col, timestamptz_col)
values ('2022-01-01T00:00', '2022-01-01T00:00')
returning *;
    timestamp_col    |    timestamptz_col
---------------------+------------------------
 2022-01-01 00:00:00 | 2022-01-01 00:00:00-05

In the timestamp field, the value of UTC midnight, January 1st is recorded, which makes sense. In the timestamptz field however, the value of UTC 5am is actually recorded, because the inserted timestamp is assumed to be in the default time zone of the db.

postgres=# show timezone;
     TimeZone
------------------
 America/New_York
(1 row)

Now let’s insert a timestamp with a time zone in both of the fields:

postgres=# insert into tt(timestamp_col, timestamptz_col)
values ('2022-01-01T00:00+01:00', '2022-01-01T00:00+01:00')
returning *;
    timestamp_col    |    timestamptz_col
---------------------+------------------------
 2022-01-01 00:00:00 | 2021-12-31 18:00:00-05

In the timestamp column, the time zone part of the input is simply dropped, so UTC midnight is saved. In the timestamptz column, we see that the field is set to exactly the same time as the input. Ie, 2022-01-01T00:00+01:00 == 2021-12-31 18:00:00-05.