Introduction to the PostgreSQL CURRENT_DATE function

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

Here is the basic syntax of theCURRENT_DATE function:

CURRENT_DATE

TheCURRENT_DATE function returns aDATE value that represents the current date.

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

PostgreSQL CURRENT_DATE function examples

Let’s explore some examples of using theCURRENT_DATE function.

1) Basic PostgreSQL CURRENT_DATE function example

The following example shows how to use theCURRENT_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 theCURRENT_DATE in theWHERE clause to filter data based on the current date.

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

SELECT  *FROM  rentalWHERE  rental_date = CURRENT_DATE;

3) Calculating ages

First, create a new table calledemployees with thedate_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)INSERT0 3

Second, calculate the age of employees using theCURRENT_DATE function:

SELECT  name,  date_of_birth,  CURRENT_DATE as today,  (CURRENT_DATE - date_of_birth)/ 365 AS ageFROM  employeesORDER 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 theCURRENT_DATE function as a default value of a column. For example:

First,create a table calleddelivery:

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

In thedelivery table, thedelivery_dateis set with the default value generated by theCURRENT_DATE function.

Second,insert a new row into thedelivery table:

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

In thisINSERT 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 thedelivery_date column.

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

Summary

• Use the PostgreSQLCURRENT_DATE function to retrieve the current date.

Last updated onJanuary 26, 2024

On this page

• Introduction to the PostgreSQL CURRENT_DATE function
• PostgreSQL CURRENT_DATE function examples
• Summary