PostgreSQL CURRENT_DATE Function

Summary: in this tutorial, you will learn how to use the PostgreSQL CURRENT_DATE function to get the current date.

Introduction to the PostgreSQL CURRENT_DATE function

The CURRENT_DATE function returns the current date in the default time zone of the database session.

Here is the basic syntax of the CURRENT_DATE function:

CURRENT_DATE

The CURRENT_DATE function returns a DATE value that represents the current date.

Note that the CURRENT_DATE function returns the current date without any time information.

PostgreSQL CURRENT_DATE function examples

Let’s explore some examples of using the CURRENT_DATE function.

1) Basic PostgreSQL CURRENT_DATE function example

The following example shows how to use the CURRENT_DATE function to get the current date:

SELECT CURRENT_DATE;

Output:

current_date
--------------
 2024-01-26
(1 row)

2) Using the PostgreSQL CURRENT_DATE for date-based filtering

You can use the CURRENT_DATE in the WHERE clause to filter data based on the current date.

For example, you can retrieve the rentals placed today by comparing the rental date in the rental table with the result of the CURRENT_DATE function:

SELECT
  *
FROM
  rental
WHERE
  rental_date = CURRENT_DATE;

3) Calculating ages

First, create a new table called employees with the date_of_birth column and insert some data into the table:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    date_of_birth DATE NOT NULL
);

INSERT INTO employees (name, date_of_birth)
VALUES
    ('John Doe', '1992-05-15'),
    ('Jane Smith', '1995-08-22'),
    ('Bob Johnson', '1998-11-10')
RETURNING *;

Output:

id |    name     | birth_date
----+-------------+------------
  1 | John Doe    | 1992-05-15
  2 | Jane Smith  | 1995-08-22
  3 | Bob Johnson | 1998-11-10
(3 rows)


INSERT 0 3

Second, calculate the age of employees using the CURRENT_DATE function:

SELECT
  name,
  date_of_birth,
  CURRENT_DATE as today,
  (CURRENT_DATE - date_of_birth) / 365 AS age
FROM
  employees
ORDER BY
  name;

Output:

name     | date_of_birth |   today    | age
-------------+---------------+------------+-----
 Bob Johnson | 1998-11-10    | 2024-01-26 |  25
 Jane Smith  | 1995-08-22    | 2024-01-26 |  28
 John Doe    | 1992-05-15    | 2024-01-26 |  31
(3 rows)

4) Using the PostgreSQL CURRENT_DATE function as the default value of a column

In practice, you often use the CURRENT_DATE function as a default value of a column. For example:

First, create a table called delivery:

CREATE TABLE delivery(
  delivery_id SERIAL PRIMARY KEY,
  product VARCHAR(255) NOT NULL,
  delivery_date DATE DEFAULT CURRENT_DATE
);

In the delivery table, the delivery_dateis set with the default value generated by the CURRENT_DATE function.

Second, insert a new row into the delivery table:

INSERT INTO delivery(product)
VALUES
  ('Sample screen protector');

In this INSERT statement, we do not specify the delivery date. Therefore, PostgreSQL uses the current date as the default value.

Third, verify the insert:

SELECT * FROM delivery;

Output:

delivery_id |         product         | delivery_date
-------------+-------------------------+---------------
           1 | Sample screen protector | 2024-01-26
(1 row)

The output indicates that the statement inserted the current date into the delivery_date column.

Note that you will see a different value in the delivery_date column, depending on when you execute the query.

Summary

• Use the PostgreSQL CURRENT_DATE function to retrieve the current date.