PostgreSQL CURRENT_DATE Date/Time Function

PostgreSQL CURRENT_DATE Date/Time Function

The PostgreSQL CURRENT_DATE function is used to get the current date. This function is essential for retrieving the current date for various date-based calculations and comparisons.

Syntax

CURRENT_DATE

The CURRENT_DATE function does not take any arguments and returns a date representing the current date.

Example PostgreSQL CURRENT_DATE Queries

Let's look at some examples of PostgreSQL CURRENT_DATE function queries:

1. Basic CURRENT_DATE Example

SELECT CURRENT_DATE AS current_date;

This query retrieves the current date.

2. CURRENT_DATE with Calculations

SELECT CURRENT_DATE + INTERVAL '1 day' AS tomorrow;

This query adds one day to the current date to get the date for tomorrow.

3. CURRENT_DATE with Column Values

SELECT id, name, birthdate, CURRENT_DATE - birthdate AS age
FROM people;

This query retrieves the id, name, birthdate, and the age calculated as the difference between the current date and the birthdate for each row in the people table.

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the CURRENT_DATE function to perform date-based calculations.

Step 1: Creating a Table

This step involves creating a new table named people to store people's data, including their birthdates.

CREATE TABLE people (
    id SERIAL PRIMARY KEY,
    name TEXT,
    birthdate DATE
);

In this example, we create a table named people with columns for id, name, and birthdate.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the people table.

INSERT INTO people (name, birthdate)
VALUES ('Alice', '1980-05-15'),
       ('Bob', '1990-10-25'),
       ('Charlie', '2000-12-30');

Here, we insert data into the people table.

Step 3: Using the CURRENT_DATE Function

This step involves using the CURRENT_DATE function to perform date-based calculations in the people table.

Retrieve current date:

SELECT CURRENT_DATE AS current_date;

This query retrieves the current date.

Calculate tomorrow's date:

SELECT CURRENT_DATE + INTERVAL '1 day' AS tomorrow;

This query adds one day to the current date to get the date for tomorrow.

Calculate age from birthdate:

SELECT id, name, birthdate, CURRENT_DATE - birthdate AS age
FROM people;

This query calculates the age for each person based on their birthdate and the current date.

Conclusion

The PostgreSQL CURRENT_DATE function is a fundamental tool for retrieving the current date and performing date-based calculations. Understanding how to use the CURRENT_DATE function and its syntax is essential for accurate date-based operations in PostgreSQL databases.