How to convert timestamp to timestamptz postgres?

798 Asked by alexGONZALEZ in SQL Server , Asked on Sep 29, 2022

I have a decently-sized (~50k rows) time-series database running on Postgres, with some other structured data (in another database instance) which is much smaller.

Stupidly, when I initially designed the thing I had all the fields as TIMESTAMP WITHOUT TIME ZONE, and now I'm paying for it with annoying time-zone related bugs. I want everything to be explicit, so I want to convert the field to TIMESTAMP WITH TIME ZONE. I realise that this doesn't store extra information, and all my timestamps are already in UTC, so the migration should be trivial, but I was wondering if there are any complications / potential tripping blocks that will prove problematic (this is a production database with customers relying on it)?

Answered by Amit raj

Pay attention that the correct time zone (UTC in your case) is applied during the conversion. If you are not explicit about this, the time zone of the current session is assumed - typically not UTC.

  ALTER TABLE tbl ALTER ts_column TYPE timestamptz USING ts_column AT TIME ZONE 'UTC';

Check a possible column default for sanity, too. Any expression working with data type timestamp (like LOCALTIMESTAMP or now()::timestamp) is subject to the same problem. To change:

  ALTER TABLE tbl ALTER ts_column SET DEFAULT now();  -- or current_timestamp

Obviously, statements writing to the table also need to use timestamptz now - or you have another instance of the same problem with automatic conversion from the type timestamp [without time zone]. Since this is a production DB, best do it all in a single transaction to avoid race conditions - or even a single statement:

ALTER TABLE tbl

  ALTER ts_column TYPE timestamptz postgres USING ts_column AT TIME ZONE 'UTC'

, ALTER ts_column SET DEFAULT now();

Your Answer

Answer (1)

Ranjana

In PostgreSQL, you can convert a timestamp (without time zone) to a timestamptz (timestamp with time zone) using the AT TIME ZONE syntax. The AT TIME ZONE construct is used to interpret a timestamp without time zone as a timestamp with time zone.

Here's how you can do it:

Basic Conversion

Assuming you have a timestamp column and you want to convert it to timestamptz by interpreting it in a specific time zone:

  SELECT timestamp_column AT TIME ZONE 'UTC' AS timestamptz_columnFROM your_table;

This example interprets the timestamp_column as being in UTC time zone.

Example Usage

Let's say you have the following table:

  CREATE TABLE example_table (    id SERIAL PRIMARY KEY,    timestamp_column TIMESTAMP);INSERT INTO example_table (timestamp_column)VALUES ('2024-07-10 12:00:00'),       ('2024-07-10 15:30:00');To convert the timestamp_column to timestamptz, interpreting it in UTC:

  SELECT timestamp_column,       timestamp_column AT TIME ZONE 'UTC' AS timestamptz_columnFROM example_table;

Adjusting to Your Local Time Zone

If you want to convert the timestamp to timestamptz in your local time zone, you need to specify your local time zone:

  SELECT timestamp_column,       timestamp_column AT TIME ZONE 'America/New_York' AS timestamptz_columnFROM example_table;

Changing the Data Type of a Column

If you want to permanently change the data type of a column from timestamp to timestamptz, you can use the ALTER TABLE statement with the USING clause:

  ALTER TABLE example_tableALTER COLUMN timestamp_columnSET DATA TYPE TIMESTAMPTZUSING timestamp_column AT TIME ZONE 'UTC';

This will convert all existing timestamp values to timestamptz using the specified time zone.

Detailed Example

Here's a complete example, including table creation, insertion, conversion, and alteration of the column data type:

  -- Create tableCREATE TABLE example_table (    id SERIAL PRIMARY KEY,    timestamp_column TIMESTAMP);-- Insert sample dataINSERT INTO example_table (timestamp_column)VALUES ('2024-07-10 12:00:00'),       ('2024-07-10 15:30:00');-- Select with conversion to timestamptzSELECT timestamp_column,       timestamp_column AT TIME ZONE 'UTC' AS timestamptz_columnFROM example_table;-- Alter the column to permanently change its type to timestamptzALTER TABLE example_tableALTER COLUMN timestamp_columnSET DATA TYPE TIMESTAMPTZUSING timestamp_column AT TIME ZONE 'UTC';-- Verify the changeSELECT * FROM example_table;

This example demonstrates how to handle the conversion both temporarily (for queries) and permanently (for altering table structure). Adjust the time zone as needed to fit your requirements.

2 Weeks