7

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Alex Chen
  • 113
  • 1
  • 2
  • 7

2 Answers2

8

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:

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:

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    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. – Robert Nubel Jul 16 '21 at 16:07
  • Sorry for waking the dead... Will `ALTER TABLE tbl ALTER COLUMN ts type timestamptz USING ts AT TIME ZONE 'UTC';` lock the table? – Daniel Dec 12 '22 at 07:08
3

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
Robert Nubel
  • 7,104
  • 1
  • 18
  • 30
  • 1
    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`? – Alex Chen Jul 15 '21 at 13:27
  • @AlexChen - do you mean you want to convert your column from timestamp to timestamptz? If so, https://dba.stackexchange.com/questions/134385/convert-postgres-timestamp-to-timestamptz 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. – Robert Nubel Jul 15 '21 at 15:01
  • 1
    There are a couple of misunderstandings. Too many for a comment, I added an answer. – Erwin Brandstetter Jul 16 '21 at 15:40
  • Very useful answer now! – Erwin Brandstetter Jul 16 '21 at 16:14