{"id":756,"date":"2024-12-08T16:06:03","date_gmt":"2024-12-08T09:06:03","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=756"},"modified":"2025-01-02T14:04:44","modified_gmt":"2025-01-02T07:04:44","slug":"postgresql-timestamp","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-timestamp\/","title":{"rendered":"PostgreSQL TIMESTAMP Data Type"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn how to use the PostgreSQL TIMESTAMP<\/code> data type to store date and time values in the database.<\/p>\n\n\n\n

Getting Started with the PostgreSQL TIMESTAMP Data Type #<\/a><\/h2>\n\n\n\n

In PostgreSQL, the TIMESTAMP<\/code> data type allows you to store both date and time in the database. The TIMESTAMP<\/code> data type does not include any time zone data.<\/p>\n\n\n\n

It means that when you change the time zone of your PostgreSQL server, the TIMESTAMP<\/code> values stored in the database won’t automatically change.<\/p>\n\n\n\n

If you want to store date and time with a time zone, you can use the TIMESTAMPTZ<\/a><\/code> data type instead.<\/p>\n\n\n\n

PostgreSQL uses 8 bytes to store a TIMESTAMP<\/code> value. The valid range of the TIMESTAMP<\/code> is from 4713 BC<\/code> to 294276 AD<\/code>.<\/p>\n\n\n\n

Here’s the syntax for defining a TIMESTAMP<\/code> column:<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> table_name<\/span>(\n\u00a0 \u00a0\u00a0column_name<\/span> TIMESTAMP<\/span>\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you want to set a default value for a TIMESTAMP<\/code> column, you can apply a DEFAULT<\/code> constraint with the default value returned by the LOCALTIMESTAMP<\/code> function:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> table_name<\/span>(\n\u00a0 \u00a0\u00a0column_name<\/span> TIMESTAMP<\/span> DEFAULT<\/span> LOCALTIMESTAMP<\/span>\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that the LOCALTIMESTAMP<\/code> returns the local date and time based on the local time of the PostgreSQL server.<\/p>\n\n\n\n
PostgreSQL uses the ISO 8601 standard for both input and output timestamps:<\/p>\n\n\n
yyyy-mm-dd hh:mm:ss<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
ISO 8601 uses the upper letter T<\/code> to separate the date and time:<\/p>\n\n\n
yyyy-mm-dd<strong>T<\/strong>hh:mm:ss<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
PostgreSQL accepts this format for input. But it uses space rather than the uppercase letter T<\/code> for readability.<\/p>\n\n\n
yyyy-mm-dd hh:mm:ss<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Additionally, PostgreSQL supports other reasonable timestamp formats that are not covered in this tutorial.<\/p>\n\n\n\n
In practice, you should use the TIMESTAMP<\/code> data type to store timestamps only when you save and retrieve them from the database and you’re not doing any calculations. Otherwise, you should use the TIMESTAMPTZ<\/code> instead.<\/p>\n\n\n\n
PostgreSQL TIMESTAMP Data Type example #<\/a><\/h2>\n\n\n\n
The TIMESTAMP<\/code> data type is suitable for storing local time without caring about the time zone.<\/p>\n\n\n\n
For example, you can use the TIMESTAMP<\/code> data type to store local time like local theater shows. It doesn’t matter in which time zone.<\/p>\n\n\n\n
First, create a table<\/a> called events<\/code>, which stores local events like theater shows.<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> events (\n  id INT<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n  title VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n  location<\/span> VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n  start_at TIMESTAMP<\/span> NOT<\/span> NULL<\/span>,\n  end_at TIMESTAMP<\/span> NOT<\/span> NULL<\/span>\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span>\n  events (title, location<\/span>, start_at, end_at)\nVALUES<\/span>\n  (\n    '\"O\" by Cirque du Soleil'<\/span>,\n    'O Theatre, Bellagio'<\/span>,\n    '2024-12-08 19:00:00'<\/span>,\n    '2024-12-08 21:30:00'<\/span>\n  ),\n  (\n    'David Copperfield'<\/span>,\n    'David Copperfield Theater, MGM Grand'<\/span>,\n    '2024-12-07 16:00:00'<\/span>,\n    '2024-12-07 19:00:00'<\/span>\n  ),\n  (\n    'Awakening'<\/span>,\n    'Awakening Theater, Wynn Las Vegas'<\/span>,\n    '2024-12-06 19:00:00'<\/span>,\n    '2024-12-06 21:00:00'<\/span>\n  ) \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
 id |          title          |               location               |      start_at       |       end_at\n----+-------------------------+--------------------------------------+---------------------+---------------------\n  1<\/span> | \"O\"<\/span> by Cirque du Soleil | O Theatre, Bellagio                  | 2024<\/span>-12<\/span>-08<\/span> 19<\/span>:00<\/span>:00<\/span> | 2024<\/span>-12<\/span>-08<\/span> 21<\/span>:30<\/span>:00<\/span>\n  2<\/span> | David Copperfield       | David Copperfield Theater, MGM Grand | 2024<\/span>-12<\/span>-07<\/span> 16<\/span>:00<\/span>:00<\/span> | 2024<\/span>-12<\/span>-07<\/span> 19<\/span>:00<\/span>:00<\/span>\n  3<\/span> | Awakening               | Awakening Theater, Wynn Las Vegas    | 2024<\/span>-12<\/span>-06<\/span> 19<\/span>:00<\/span>:00<\/span> | 2024<\/span>-12<\/span>-06<\/span> 21<\/span>:00<\/span>:00<\/span><\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, calculate the duration of each event by subtracting the start time from the end time:<\/p>\n\n\n
SELECT<\/span>\n  title,\n  end_at - start_at duration\nFROM<\/span>\n  events;<\/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
          title          | duration\n-------------------------+----------<\/span>\n \"O\" by<\/span> Cirque du Soleil | 02<\/span>:30<\/span>:00<\/span>\n David Copperfield       | 03<\/span>:00<\/span>:00<\/span>\n Awakening               | 02<\/span>:00<\/span>:00<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/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
Second, insert some rows<\/a> into the events<\/code> table:<\/p>\n\n\n