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.