{"id":737,"date":"2024-12-08T10:18:52","date_gmt":"2024-12-08T03:18:52","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=737"},"modified":"2025-01-02T13:52:01","modified_gmt":"2025-01-02T06:52:01","slug":"postgresql-date","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-date\/","title":{"rendered":"PostgreSQL DATE Data Type"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn about the PostgreSQL DATE<\/code> data type and how to use it to store date data in a table.<\/p>\n\n\n\n

Getting started with the PostgreSQL DATE data type #<\/a><\/h2>\n\n\n\n

In PostgreSQL, you use the DATE<\/code> data type to store date without time in a table. Here’s the syntax for defining a DATE<\/code> column:<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> table_name<\/span> (\n\u00a0 \u00a0\u00a0column_name<\/span> DATE<\/span>,\n    ...\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
A date column can store a date between 4713 BC and 5874897 AD. PostgreSQL uses a 4-byte to store a date value.<\/p>\n\n\n\n
CREATE<\/span> TABLE<\/span> table_name<\/span> (\n    column_name<\/span> DATE<\/span> DEFAULT<\/span> default_date\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 the current date as the default value, you can use the CURRENT_DATE<\/code> function as follows:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> table_name<\/span> (\n    column_name<\/span> DATE<\/span> DEFAULT<\/span> CURRENT_DATE<\/span>\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Date Input #<\/a><\/h3>\n\n\n\n
PostgreSQL accepts various date formats, but it is recommended to use ISO 8601, which is yyyy-mm-dd<\/code> for date input. For example, 2024-12-31<\/code> represents December 31, 2024<\/code>.<\/p>\n\n\n\n
Date Output #<\/a><\/h3>\n\n\n\n
PostgreSQL allows you to set various date output formats like the date input formats. The default is the ISO 8601 format.<\/p>\n\n\n\n
PostgreSQL DATE data type example #<\/a><\/h2>\n\n\n\n
The following example creates a table<\/a> called stock_adjustments<\/code> that includes a DATE<\/code> column:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> stock_adjustments (\n  id INT<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n  product_name VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n  quantity INT<\/span> NOT<\/span> NULL<\/span>,\n  adjustment_date DATE<\/span> NOT<\/span> NULL<\/span> DEFAULT<\/span> CURRENT_DATE<\/span>\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The stock_adjustments<\/code> table has an adjustment_date<\/code> column representing the adjustment date. Its value defaults to the current date.<\/p>\n\n\n\n
If you don’t supply a date when inserting, PostgreSQL inserts the current date into the adjustment date column.<\/p>\n\n\n\n
Inserting default date values #<\/a><\/h2>\n\n\n\n
The following statement inserts<\/a> a new row into the stock_adjustments<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  stock_adjustments (product_name, quantity)\nVALUES<\/span>\n  ('iPhone 16 Pro'<\/span>, 5<\/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
 id | product_name  | quantity | adjustment_date\n----+---------------+----------+-----------------<\/span>\n  1<\/span> | iPhone 16<\/span> Pro |        5<\/span> | 2024<\/span>-12<\/span>-08<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this INSERT<\/code> statement, we don’t provide a value for the adjustment date column; it defaults to the date on the day we run the statement.<\/p>\n\n\n\n
The output of the date column is in the ISO 8601 format, which is yyyy-mm-dd<\/code>. The adjustment date is 2024-12-08<\/code>, which represents December 08, 2024<\/code>.<\/p>\n\n\n\n
Inserting date values #<\/a><\/h2>\n\n\n\n
The following example inserts a new row into the stock_adjustments<\/code> table with an input date value:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  stock_adjustments (product_name, quantity, adjustment_date)\nVALUES<\/span>\n  ('Galaxy S23 Ultra'<\/span>, 2<\/span>, '2024-12-09'<\/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
 id |   product_name   | quantity | adjustment_date\n----+------------------+----------+-----------------<\/span>\n  2<\/span> | Galaxy S23 Ultra |        2<\/span> | 2024<\/span>-12<\/span>-09<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, we explicitly use a date input in ISO 8601 format: 2024-12-09<\/code>, representing December 09, 2024<\/code>.<\/p>\n\n\n\n
Extracting day, month, and year from a date #<\/a><\/h2>\n\n\n\n
The extract()<\/code> function extracts a field which can be a day, month, and year from a date value.<\/p>\n\n\n\n
For example, the following statement uses the extract()<\/code> function to extract day, month, and year from the adjustment date column:<\/p>\n\n\n
SELECT<\/span>\n  product_name,\n  quantity,\n  adjustment_date,\n  extract(day<\/span> from<\/span>  adjustment_date) d,\n  extract(month<\/span> from<\/span> adjustment_date) m,\n  extract(year<\/span> from<\/span> adjustment_date) y\nFROM<\/span>\n  stock_adjustments\nORDER<\/span> BY<\/span>\n  adjustment_date;<\/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
   product_name   | quantity | adjustment_date | d | m  |  y\n------------------+----------+-----------------+---+----+------<\/span>\n iPhone 16<\/span> Pro    |        5<\/span> | 2024<\/span>-12<\/span>-08<\/span>      | 8<\/span> | 12<\/span> | 2024<\/span>\n Galaxy S23 Ultra |        2<\/span> | 2024<\/span>-12<\/span>-09<\/span>      | 9<\/span> | 12<\/span> | 2024<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Formatting date values #<\/a><\/h2>\n\n\n\n
To format a date value to a specific format, you use the to_char()<\/code> function.<\/p>\n\n\n\n
For example, the following statement retrieves the stock adjustment data and formats the date as \"Mon dd, yyyy\"<\/code> format:<\/p>\n\n\n
SELECT<\/span>\n  product_name,\n  quantity,\n  adjustment_date,\n  to_char (adjustment_date, 'Month dd, yyyy'<\/span>) formatted_date\nFROM<\/span>\n  stock_adjustments\nORDER<\/span> BY<\/span>\n  adjustment_date;<\/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
   product_name   | quantity | adjustment_date |   formatted_date\n------------------+----------+-----------------+--------------------<\/span>\n iPhone 16<\/span> Pro    |        5<\/span> | 2024<\/span>-12<\/span>-08<\/span>      | December  08<\/span>, 2024<\/span>\n Galaxy S23 Ultra |        2<\/span> | 2024<\/span>-12<\/span>-09<\/span>      | December  09<\/span>, 2024<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Adding\/removing days from a date #<\/a><\/h2>\n\n\n\n
To add a day to a date, you use the +<\/code> operator:<\/p>\n\n\n
date<\/span> + days<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Likewise, to subtract days from a date, you use the -<\/code> operator:<\/p>\n\n\n
date<\/span> - days<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, the following statement adds 3 days to the adjustment dates:<\/p>\n\n\n
SELECT<\/span>\n  product_name,\n  quantity,\n  adjustment_date,\n  adjustment_date + 3<\/span> approval_date\nFROM<\/span>\n  stock_adjustments\nORDER<\/span> BY<\/span>\n  approval_date;<\/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
   product_name   | quantity | adjustment_date | approval_date\n------------------+----------+-----------------+---------------<\/span>\n iPhone 16<\/span> Pro    |        5<\/span> | 2024<\/span>-12<\/span>-08<\/span>      | 2024<\/span>-12<\/span>-11<\/span>\n Galaxy S23 Ultra |        2<\/span> | 2024<\/span>-12<\/span>-09<\/span>      | 2024<\/span>-12<\/span>-12<\/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
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
To set a default value for a DATE<\/code> column, you use the DEFAULT<\/a><\/code> constraint:<\/p>\n\n\n