PostgreSQL MAKE_DATE Date/Time Function

PostgreSQL MAKE_DATE Date/Time Function

The PostgreSQL MAKE_DATE function is used to create a date from year, month, and day fields. This function is essential for constructing date values from individual components, which can be useful in various date-based calculations and data manipulations.

Syntax

MAKE_DATE(year int, month int, day int)

The MAKE_DATE function has the following components:

  • year: The year component of the date (negative years signify BC).
  • month: The month component of the date.
  • day: The day component of the date.

Example PostgreSQL MAKE_DATE Queries

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

1. Basic MAKE_DATE Example

SELECT MAKE_DATE(2013, 7, 15) AS constructed_date;

This query creates a date from the year 2013, month 7, and day 15, resulting in '2013-07-15'.

2. MAKE_DATE with BC Year

SELECT MAKE_DATE(-44, 3, 15) AS constructed_date;

This query creates a date from the year 44 BC, month 3, and day 15, resulting in '0044-03-15 BC'.

3. MAKE_DATE with Column Values

SELECT id, name, MAKE_DATE(year, month, day) AS birthdate
FROM people;

This query retrieves the id, name, and constructs a birthdate from the year, month, and day columns 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 MAKE_DATE function to construct dates from individual components.

Step 1: Creating a Table

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

CREATE TABLE people (
    id SERIAL PRIMARY KEY,
    name TEXT,
    year INT,
    month INT,
    day INT
);

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

Step 2: Inserting Data into the Table

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

INSERT INTO people (name, year, month, day)
VALUES ('Alice', 1990, 5, 20),
       ('Bob', 1985, 12, 3),
       ('Charlie', 2000, 7, 15);

Here, we insert data into the people table.

Step 3: Using the MAKE_DATE Function

This step involves using the MAKE_DATE() function to construct dates from the individual components in the people table.

Construct birthdate from year, month, and day columns:

SELECT id, name, MAKE_DATE(year, month, day) AS birthdate
FROM people;

This query constructs the birthdate from the year, month, and day columns for each row in the people table.

Conclusion

The PostgreSQL MAKE_DATE() function is a fundamental tool for constructing date values from individual year, month, and day components. Understanding how to use the MAKE_DATE() function and its syntax is essential for accurate date construction in PostgreSQL databases.