PostgreSQL DATE Data Type

Summary: in this tutorial, you’ll learn about the PostgreSQL DATE data type and how to use it to store date data in a table.

Getting started with the PostgreSQL DATE data type #

In PostgreSQL, you use the DATE data type to store date without time in a table. Here’s the syntax for defining a DATE column:

CREATE TABLE table_name (
    column_name DATE,
    ...
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

A date column can store a date between 4713 BC and 5874897 AD. PostgreSQL uses a 4-byte to store a date value.

To set a default value for a DATE column, you use the DEFAULT constraint:

CREATE TABLE table_name (
    column_name DATE DEFAULT default_date
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

If you want to set the current date as the default value, you can use the CURRENT_DATE function as follows:

CREATE TABLE table_name (
    column_name DATE DEFAULT CURRENT_DATE
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Date Input #

PostgreSQL accepts various date formats, but it is recommended to use ISO 8601, which is yyyy-mm-dd for date input. For example, 2024-12-31 represents December 31, 2024.

Date Output #

PostgreSQL allows you to set various date output formats like the date input formats. The default is the ISO 8601 format.

PostgreSQL DATE data type example #

The following example creates a table called stock_adjustments that includes a DATE column:

CREATE TABLE stock_adjustments (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_name VARCHAR(255) NOT NULL,
  quantity INT NOT NULL,
  adjustment_date DATE NOT NULL DEFAULT CURRENT_DATE
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The stock_adjustments table has an adjustment_date column representing the adjustment date. Its value defaults to the current date.

If you don’t supply a date when inserting, PostgreSQL inserts the current date into the adjustment date column.

Inserting default date values #

The following statement inserts a new row into the stock_adjustments table:

INSERT INTO
  stock_adjustments (product_name, quantity)
VALUES
  ('iPhone 16 Pro', 5) 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 id | product_name  | quantity | adjustment_date
----+---------------+----------+-----------------
  1 | iPhone 16 Pro |        5 | 2024-12-08Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this INSERT statement, we don’t provide a value for the adjustment date column; it defaults to the date on the day we run the statement.

The output of the date column is in the ISO 8601 format, which is yyyy-mm-dd. The adjustment date is 2024-12-08, which represents December 08, 2024.

Inserting date values #

The following example inserts a new row into the stock_adjustments table with an input date value:

INSERT INTO
  stock_adjustments (product_name, quantity, adjustment_date)
VALUES
  ('Galaxy S23 Ultra', 2, '2024-12-09') 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 id |   product_name   | quantity | adjustment_date
----+------------------+----------+-----------------
  2 | Galaxy S23 Ultra |        2 | 2024-12-09Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example, we explicitly use a date input in ISO 8601 format: 2024-12-09, representing December 09, 2024.

Extracting day, month, and year from a date #

The extract() function extracts a field which can be a day, month, and year from a date value.

For example, the following statement uses the extract() function to extract day, month, and year from the adjustment date column:

SELECT
  product_name,
  quantity,
  adjustment_date,
  extract(day from  adjustment_date) d,
  extract(month from adjustment_date) m,
  extract(year from adjustment_date) y
FROM
  stock_adjustments
ORDER BY
  adjustment_date;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

   product_name   | quantity | adjustment_date | d | m  |  y
------------------+----------+-----------------+---+----+------
 iPhone 16 Pro    |        5 | 2024-12-08      | 8 | 12 | 2024
 Galaxy S23 Ultra |        2 | 2024-12-09      | 9 | 12 | 2024Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Formatting date values #

To format a date value to a specific format, you use the to_char() function.

For example, the following statement retrieves the stock adjustment data and formats the date as "Mon dd, yyyy" format:

SELECT
  product_name,
  quantity,
  adjustment_date,
  to_char (adjustment_date, 'Month dd, yyyy') formatted_date
FROM
  stock_adjustments
ORDER BY
  adjustment_date;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

   product_name   | quantity | adjustment_date |   formatted_date
------------------+----------+-----------------+--------------------
 iPhone 16 Pro    |        5 | 2024-12-08      | December  08, 2024
 Galaxy S23 Ultra |        2 | 2024-12-09      | December  09, 2024Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Adding/removing days from a date #

To add a day to a date, you use the + operator:

date + daysCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Likewise, to subtract days from a date, you use the - operator:

date - daysCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example, the following statement adds 3 days to the adjustment dates:

SELECT
  product_name,
  quantity,
  adjustment_date,
  adjustment_date + 3 approval_date
FROM
  stock_adjustments
ORDER BY
  approval_date;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

   product_name   | quantity | adjustment_date | approval_date
------------------+----------+-----------------+---------------
 iPhone 16 Pro    |        5 | 2024-12-08      | 2024-12-11
 Galaxy S23 Ultra |        2 | 2024-12-09      | 2024-12-12Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use PostgreSQL DATE type to store dates in a table.
• Use the extract() function to extract a day, month, and year from a date.
• Use the to_char() function to format a date.
• Use the + operator to add a number of days to a date.
• Use - operator to subtract a number of days from a date.

Quiz #