{"id":766,"date":"2024-12-08T17:45:26","date_gmt":"2024-12-08T10:45:26","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=766"},"modified":"2025-01-02T14:13:17","modified_gmt":"2025-01-02T07:13:17","slug":"postgresql-timestamp-with-time-zone","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-timestamp-with-time-zone\/","title":{"rendered":"PostgreSQL TIMESTAMP WITH TIME ZONE Data Type"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn how to use the PostgreSQL TIMESTAMP WITH TIME ZONE<\/code> data type to handle timestamp values with time zone.<\/p>\n\n\n\n

Getting Started with the PostgreSQL TIMESTAMP WITH TIME ZONE data type #<\/a><\/h2>\n\n\n\n

In PostgreSQL, you can use the TIMESTAMP WITH TIME ZONE<\/code> data type to store time zone-aware timestamp values.<\/p>\n\n\n\n

Here’s the syntax for defining a column with the type TIMESTAMP WITH TIME ZONE<\/code>:<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> table_name<\/span>(\n\u00a0 \u00a0\u00a0column_name<\/span> TIMESTAMP<\/span> WITH TIME ZONE<\/span>,\n    ...\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
TIMESTAMPTZ<\/code> is the shorthand of the TIMESTAMP WITH TIME ZONE<\/code>, so you can use it to save some typing:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> table_name<\/span>(\n\u00a0 \u00a0\u00a0column_name<\/span> TIMESTAMPTZ<\/span>,\n    ...\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
When you insert a timestamp into a TIMESTAMPTZ<\/code> column, PostgreSQL converts it to UTC for storage. This conversion ensures the stored timestamp is consistent and not affected by time zone differences.<\/p>\n\n\n\n
When you retrieve a timestamp from a TIMESTAMPTZ<\/code> column, PostgreSQL converts the stored UTC timestamp back to the time zone of your session. This conversion means that the time you receive is adjusted to the time zone set by your database session.<\/p>\n\n\n\n
It’s important to note that PostgreSQL does not store time zones in the TIMESTAMPTZ<\/code> column but does the time zone conversion when stored and retrieved.<\/p>\n\n\n\n
You can set the time zone of your database session using the SET TIME ZONE<\/code> statement. For example:<\/p>\n\n\n
SET<\/span> TIME<\/span> ZONE<\/span> 'America\/New_York'<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The TIMESTAMPTZ<\/code> can automatically handle daylight saving time changes and other time zone-related adjustments.<\/p>\n\n\n\n
In practice, you’ll often use the TIMESTAMP WITH TIME ZONE<\/code> data type in applications that handle time across time zones, such as scheduling apps, international transactions, and logging events.<\/p>\n\n\n\n
Getting the current timestamp with a time zone #<\/a><\/h2>\n\n\n\n
The CURRENT_TIMESTAMP<\/code> function returns the current time with a time zone.<\/p>\n\n\n
SELECT<\/span> CURRENT_TIMESTAMP<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
       current_timestamp<\/span>\n-------------------------------<\/span>\n 2024<\/span>-12<\/span>-08<\/span> 02<\/span>:42<\/span>:59.773965<\/span>-08<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
It shows the current time in the 'US\/Pacific'<\/code> time zone.<\/p>\n\n\n\n
To set the time zone of your database session to a different one, you use the SET TIME ZONE<\/code> statement:<\/p>\n\n\n
SET<\/span> TIME<\/span> ZONE<\/span> 'America\/New_York'<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you get the current timestamp again, you’ll see the time is adjusted to the ‘America\/New_York’ time zone:<\/p>\n\n\n
SELECT<\/span> CURRENT_TIMESTAMP<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
       current_timestamp<\/span>\n-------------------------------<\/span>\n 2024<\/span>-12<\/span>-08<\/span> 05<\/span>:43<\/span>:11.587639<\/span>-05<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
To get a list of supported time zone names, you use the following statement:<\/p>\n\n\n
SELECT<\/span>\n  name<\/span>,\n  abbrev,\n  utc_offset,\n  is_dst\nFROM<\/span>\n  pg_timezone_names;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Setting a default value for a TIMESTAMPTZ column #<\/a><\/h2>\n\n\n\n
To set a default value for a TIMESTAMPTZ<\/code> column, you can use a DEFAULT constraint:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> table_name<\/span>(\n\u00a0 \u00a0\u00a0column_name<\/span> TIMESTAMPTZ<\/span> DEFAULT<\/span> CURRENT_TIMESTAMP<\/span>\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, if you insert or update the TIMESTAMPTZ<\/code> column without providing a value, PostgreSQL will use the current timestamp returned by the CURRENT_TIMESTAMP<\/code> function for inserting or updating.<\/p>\n\n\n\n
PostgreSQL TIMESTAMPTZ data type example #<\/a><\/h2>\n\n\n\n
First, create a table<\/a> called inventory_scans<\/code> that tracks scanned products in the inventory:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> inventory_scans (\n  scan_id INT<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n  warehouse_id INT<\/span> NOT<\/span> NULL<\/span>,\n  product_id INT<\/span> NOT<\/span> NULL<\/span>,\n  serial_no VARCHAR<\/span>(25<\/span>) NOT<\/span> NULL<\/span>,\n  quantity INT<\/span> NOT<\/span> NULL<\/span>,\n  scanned_at TIMESTAMPTZ<\/span> NOT<\/span> NULL<\/span> DEFAULT<\/span> CURRENT_TIMESTAMP<\/span>\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, insert some rows<\/a> into the inventory_scans<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  inventory_scans (warehouse_id, product_id, serial_no, quantity)\nVALUES<\/span>\n  (1<\/span>, 1<\/span>, 'F2LZK3H8N72Q'<\/span>, 1<\/span>),\n  (2<\/span>, 1<\/span>, 'R58N40Y1A2B3'<\/span>, 1<\/span>) \nRETURNING<\/span> *;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 scan_id | warehouse_id | product_id |  serial_no   | quantity |          scanned_at\n---------+--------------+------------+--------------+----------+-------------------------------\n       1 |            1 |          1 | F2LZK3H8N72Q |        1 | 2024-12-08 05:44:11.998853-05\n       2 |            2 |          1 | R58N40Y1A2B3 |        1 | 2024-12-08 05:44:11.998853-05<\/code><\/span><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n