August 2, 2023
Summary: this tutorial discusses PostgreSQL DATE data type and shows you how to use some handy date functions to handle dates values.
Table of Contents
Introduction to the PostgreSQL DATE data type
To store date values, you use the PostgreSQL DATE data type. PostgreSQL uses 4 bytes to store a date value. The lowest and highest values of the DATE data type are 4713 BC and 5874897 AD.
When storing a date value, PostgreSQL uses the yyyy-mm-dd format e.g., 2000-12-31. It also uses this format for inserting data into a date column.
If you create a table that has a DATE column and you want to use the current date as the default value for the column, you can use the CURRENT_DATE after the DEFAULT keyword.
For example, the following statement creates the documents table that has the posting_date column with the DATE data type. The posting_date column accepts the current date as the default value.
DROP TABLE IF EXISTS documents;
CREATE TABLE documents (
document_id serial PRIMARY KEY,
header_text VARCHAR (255) NOT NULL,
posting_date DATE NOT NULL DEFAULT CURRENT_DATE
);
INSERT INTO documents (header_text)
VALUES('Billing to customer XYZ');
SELECT * FROM documents;
The following shows the output of the query above. Note that you may get a different posting date value based on the current date of the database server.
document_id | header_text | posting_date
-------------+-------------------------+--------------
1 | Billing to customer XYZ | 2016-06-23
(1 row)
PostgreSQL DATE functions
For the demonstration, we will create a new employees table that consists of employee_id, first_name, last_name, birth_date, and hire_date columns, where the data types of the birth_date and hire_date columns are DATE.
DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
employee_id serial PRIMARY KEY,
first_name VARCHAR (255),
last_name VARCHAR (355),
birth_date DATE NOT NULL,
hire_date DATE NOT NULL
);
INSERT INTO employees (first_name, last_name, birth_date, hire_date)
VALUES ('Shannon','Freeman','1980-01-01','2005-01-01'),
('Sheila','Wells','1978-02-05','2003-01-01'),
('Ethel','Webb','1975-01-01','2001-01-01');
1) Get the current date
To get the current date and time, you use the built-in NOW() function. However, to get the date part only (without the time part), you use the double colons (::) to cast a DATETIME value to a DATE value.
The following statement returns the current date of the database server:
SELECT NOW()::date;
Another way to get current date is to use the CURRENT_DATE as follows:
SELECT CURRENT_DATE;
The result is in the format: yyyy-mm-dd. However, you can output a date value to various formats.
2) Output a PostgreSQL date value in a specific format
To output a date value in a specific format, you use the TO_CHAR() function. The TO_CHAR() function accepts two parameters. The first parameter is the value that you want to format, and the second one is the template that defines the output format.
For example, to display the current date in dd/mm/yyyy format, you use the following statement:
SELECT TO_CHAR(NOW() :: DATE, 'dd/mm/yyyy');
to_char
------------
23/06/2016
(1 row)
Or to display a date in the format like Jun 22, 2016, you use the following statement:
SELECT TO_CHAR(NOW() :: DATE, 'Mon dd, yyyy');
to_char
--------------
Jun 23, 2016
(1 row)
3) Get the interval between two dates
To get the interval between two dates, you use the minus (-) operator.
The following example gets service days of employees by subtracting the values in the hire_date column from today’s date:
SELECT
first_name,
last_name,
now() - hire_date as diff
FROM
employees;
first_name | last_name | diff
------------+-----------+---------------------------
Shannon | Freeman | 4191 days 08:25:30.634458
Sheila | Wells | 4922 days 08:25:30.634458
Ethel | Webb | 5652 days 08:25:30.634458
(3 rows)
4) Calculate ages in years, months, and days
To calculate age at the current date in years, months, and days, you use the AGE() function.
The following statement uses the AGE() function to calculate the ages of employees in the employees table.
SELECT
employee_id,
first_name,
last_name,
AGE(birth_date)
FROM
employees;
employee_id | first_name | last_name | age
-------------+------------+-----------+-------------------------
1 | Shannon | Freeman | 36 years 5 mons 22 days
2 | Sheila | Wells | 38 years 4 mons 18 days
3 | Ethel | Webb | 41 years 5 mons 22 days
(3 rows)
If you pass a date value to the AGE() function, it will subtract that date value from the current date. If you pass two arguments to the AGE() function, it will subtract the second argument from the first argument.
For example, to get the age of employees on 01/01/2015, you use the following statement:
SELECT
employee_id,
first_name,
last_name,
age('2015-01-01',birth_date)
FROM
employees;
employee_id | first_name | last_name | age
-------------+------------+-----------+--------------------------
1 | Shannon | Freeman | 35 years
2 | Sheila | Wells | 36 years 10 mons 24 days
3 | Ethel | Webb | 40 years
(3 rows)
5) Extract year, quarter, month, week, day from a date value
To get the year, quarter, month, week, day from a date value, you use the EXTRACT() function.
The following statement extracts the year, month, and day from the birth dates of employees:
SELECT
employee_id,
first_name,
last_name,
EXTRACT (YEAR FROM birth_date) AS YEAR,
EXTRACT (MONTH FROM birth_date) AS MONTH,
EXTRACT (DAY FROM birth_date) AS DAY
FROM
employees;
employee_id | first_name | last_name | year | month | day
-------------+------------+-----------+------+-------+-----
1 | Shannon | Freeman | 1980 | 1 | 1
2 | Sheila | Wells | 1978 | 2 | 5
3 | Ethel | Webb | 1975 | 1 | 1
(3 rows)
In this tutorial, you have learned about the PostgreSQL DATE datatype and some handy functions to handle a date value.
See more
PostgreSQL Tutorial: Data Types
PostgreSQL Documentation: Date/Time Types