PostgreSQL CURRENT_DATE

Summary: In this tutorial, you’ll learn how to get the current date using the PostgreSQL CURRENT_DATE function.

PostgreSQL CURRENT_DATE Function Overview #

The CURRENT_DATE function retrieves the current date based on the server’s time zone and system clock. It is useful for filtering records, performing date calculations, and tracking time-sensitive data.

The syntax of the CURRENT_DATE function is straightforward:

CURRENT_DATECode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The CURRENT_DATE function does not require parentheses () or parameters.

Getting the Current Date of the Server #

Step 1: Show the current timezone of the server

SHOW TIMEZONE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Step 2: Use the CURRENT_DATE function to get the current date

SELECT CURRENT_DATE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Inserting the Current Date into a Table Column #

To insert the current date into a date column, use the CURRENT_DATE function. For example:

INSERT INTO
  transactions (
    product_id,
    warehouse_id,
    user_id,
    type,
    quantity,
    transaction_date
  )
VALUES
  (1, 1, 1, 'receipt', 10, CURRENT_DATE),
  (2, 1, 1, 'receipt', 20, CURRENT_DATE)
RETURNING
  *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Filtering Records Based on the Current Date #

Retrieve transactions that occur today using the CURRENT_DATE function:

SELECT
  *
FROM
  transactions
WHERE
  transaction_date = CURRENT_DATE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Setting a Default Value for a Table Column #

You can set a default value for a date column using the CURRENT_DATE function. For example:

CREATE TABLE tasks (
  task_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  task_name VARCHAR(255) NOT NULL,
  completed BOOL DEFAULT FALSE,
  completion_date DATE DEFAULT CURRENT_DATE
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

When inserting a row without specifying a value for the completion_date column, PostgreSQL automatically fills it with the current date:

INSERT INTO
  tasks (task_name)
VALUES
  ('Learn the CURRENT_DATE function')
RETURNING
  task_name,
  completed,
  completion_date;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

            task_name            | completed | completion_date
---------------------------------+-----------+-----------------
 Learn the CURRENT_DATE function | f         | 2025-03-15Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Calculating Date Differences #

You can use CURRENT_DATE to calculate date differences. For example:

Step 1: Insert a row into the tasks table

INSERT INTO
  tasks (task_name, completion_date)
VALUES
  ('Master PostgreSQL', '2025-03-31');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Step 2: Calculate the number of days to complete a task (assuming today is March 15, 2025)

SELECT
  task_name,
  completion_date - CURRENT_DATE AS days
FROM
  tasks;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

            task_name            | days
---------------------------------+------
 Learn the CURRENT_DATE function |    0
 Master PostgreSQL               |   16Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the PostgreSQL CURRENT_DATE function to retrieve the current date based on the server’s time zone.
• The CURRENT_DATE function is helpful for filtering records, performing date calculations, and setting default values for date columns.

Quiz #