Often in PostgreSQL you may want to create a date column from year, month and day values.
You can use the make_date() function with the following syntax to do so:
SELECT *,
make_date(year_start, month_start, day_start) AS start_date
FROM emp;
This particular example creates a new column named start_date that uses the integer values from the year_start, month_start and day_start columns of the emp table to create a date.
The following example shows how to use this syntax in practice.
Example: Create Date Column from Year, Month and Day in PostgreSQL
Suppose that we create the following table named emp that contains information about when various employees started working at some company:
-- create table named 'emp'
CREATE TABLE emp (
id INT,
year_start INTEGER,
month_start INTEGER,
day_start INTEGER
);
-- add rows to the table
INSERT INTO emp VALUES (1, 2015, 10, 19);
INSERT INTO emp VALUES (2, 2017, 4, 12);
INSERT INTO emp VALUES (3, 2017, 9, 10);
INSERT INTO emp VALUES (4, 2020, 12, 27);
INSERT INTO emp VALUES (5, 2023, 5, 18);
-- view all rows in table
SELECT * FROM emp;
id | year_start | month_start | day_start
----+------------+-------------+-----------
1 | 2015 | 10 | 19
2 | 2017 | 4 | 12
3 | 2017 | 9 | 10
4 | 2020 | 12 | 27
5 | 2023 | 5 | 18
Suppose that we would like to create a new column named start_date that contains the starting date for each employee.
We can use the following syntax to do so:
SELECT *,
make_date(year_start, month_start, day_start) AS start_date
FROM emp;
id | year_start | month_start | day_start | start_date
----+------------+-------------+-----------+------------
1 | 2015 | 10 | 19 | 2015-10-19
2 | 2017 | 4 | 12 | 2017-04-12
3 | 2017 | 9 | 10 | 2017-09-10
4 | 2020 | 12 | 27 | 2020-12-27
5 | 2023 | 5 | 18 | 2023-05-18
The resulting table contains a new column named start_date that shows the starting date for each employee.
You can use similar syntax to create a date column based on values from year, month and day columns in your own table.
Note: You can find the complete documentation for the make_date() function in PostgreSQL here.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Select All Records from Yesterday
PostgreSQL: Select All Records from Last 7 Days
PostgreSQL: Calculate Difference Between Dates in Days
