Convert timestamp without timezone into timestamp with timezone

Question

I have a column that is of type nullable timestamp without time zone. It is stored in my Postgres database in this format: 2021-06-24 11:00:00. And I would like to convert it to a nullable timestamp with time zone type such that it would be displayed as 2021-06-24 11:00:00.000-00. Notice that there is no timezone conversion.

The solution should also allow to convert from timestamp with time zone to timestamp without time zone. I did some research and was not able to find anything.

Ils

Answer 1

You're looking for AT TIME ZONE, which is a little confusing but can do everything you need it to.

To convert a timestamp known to be UTC to a timestamptz:

SELECT '2021-06-24 11:00:00'::timestamp AT TIME ZONE 'UTC';
=> 2021-06-24 06:00:00-05

All timestamptz values are stored internally in Postgres in UTC, and do not retain the timezone they were inserted at, so they will then show up in whatever your connection timezone is. You can cast a timestamptz back to a timestamp in your desired zone by using AT TIME ZONE again (as Erwin pointed out below, AT TIME ZONE always switches between timestamp and timestamptz):

SELECT '2021-06-24 11:00:00-00'::timestamptz AT TIME ZONE 'UTC';
=> 2021-06-24 11:00:00

SELECT '2021-06-24 11:00:00-00'::timestamptz AT TIME ZONE 'America/Chicago';
=> 2021-06-24 06:00:00

Dinesen

This would return the modified value, right? What about if I would like to modify the existing timestamp without time zone with the converted new type of timestamp with time zone? – Ils

@AlexChen - do you mean you want to convert your column from timestamp to timestamptz? If so, dba.stackexchange.com/questions/134385/… should have the info you need. Note how it also uses AT TIME ZONE to ensure the cast happens the right way during the conversion. – Dinesen

There are a couple of misunderstandings. Too many for a comment, I added an answer. – Ostracism

Very useful answer now! – Ostracism

Answer 2

Assuming a table tbl with a timestamp column ts and timestamps that represent UTC time, the solution is:

ALTER TABLE tbl ALTER COLUMN ts type timestamptz USING ts AT TIME ZONE 'UTC';

db<>fiddle here

You may have to adapt the column default, too. See:

• Convert Postgres TIMESTAMP to TIMESTAMPTZ

Misunderstanding 1 (question)

I have a column that is of type nullable timestamp without time zone. It is stored in my Postgres database in this format: 2021-06-24 11:00:00.

timestamp or timestamptz values are never stored in any format. Formatting is in the domain of display, and largely independent from the storage type. What's stored is an 8-byte integer quantity with microsecond resolution representing a point in time. The display you show is the standard Postgres text representation using ISO 8601 (also unambiguous for input). Clients using the binary protocol might choose a different representation. Even clients using the text protocol might choose to reformat differently.

Misunderstanding 2 (question)

I would like to convert it to a nullable timestamp with time zone type such that it would be displayed as 2021-06-24 11:00:00.000-00.

The displayed time offset is not determined by the data type. You'll only see timezone -00 appended if that's true for the current TimeZone setting (typically UTC). Else, you get a different offset, and the timestamp is shifted accordingly.

Misunderstanding 3 (answer)

To represent it in UTC (the output value here looks like it has no timezone, but the type is timestamptz):

SELECT '2021-06-24 11:00:00'::timestamp AT TIME ZONE 'UTC' AT TIME
ZONE 'UTC';
=> 2021-06-24 11:00:00

No, the type of the output value is timestamp. timestamptz in default text representation never "looks" like timestamp. timestamp never "has" a time zone. Not even timestamptz "has" a time zone, it's just displayed for a given time zone. Neither of the two types stores any time zone information at all. See:

• Time zone storage in data type "timestamp with time zone"

Misunderstanding 4 (answer)

To cast a timestamptz back to a timestamp at UTC time:

SELECT ('2021-06-24 11:00:00-00'::timestamptz AT TIME ZONE 'UTC')::timestamp;
=> 2021-06-24 11:00:00

No. Just:

SELECT '2021-06-24 11:00:00-00'::timestamptz AT TIME ZONE 'UTC';

The result is type timestamp. No additional cast.
The AT TIME ZONE construct converts between timestamp and timestamptz. The resulting type is always switched from the input.

Basics here:

• Ignoring time zones altogether in Rails and PostgreSQL

Ostracism

Thanks for the corrections, Erwin. I use AT TIME ZONE often to convert from timestamp to timestamptz, but I clearly wasn't well-informed about its full usage and didn't do my research. I've updated my answer to remove the incorrect information. – Dinesen

Sorry for waking the dead... Will ALTER TABLE tbl ALTER COLUMN ts type timestamptz USING ts AT TIME ZONE 'UTC'; lock the table? – Dionnadionne