Summary: this tutorial discusses PostgreSQL DATE data type and shows you how to use some handy date functions to handle dates values.
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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.
1 2 3 4 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 | 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'); |
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:
1 | SELECT NOW()::date; |
Another way to get current date is to use the CURRENT_DATE as follows:
1 | SELECT CURRENT_DATE; |
The result is in the format: yyyy-mm-dd. However, you can output a date value to various formats.
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:
1 | SELECT TO_CHAR(NOW() :: DATE, 'dd/mm/yyyy'); |
1 2 3 4 | to_char ------------ 23/06/2016 (1 row) |
Or to display a date in the format like Jun 22, 2016, you use the following statement:
1 | SELECT TO_CHAR(NOW() :: DATE, 'Mon dd, yyyy'); |
1 2 3 4 | to_char -------------- Jun 23, 2016 (1 row) |
Get the interval between two dates
To get the interval between two dates, you use the minus (-) operator. For example, to get the days of service of the employees, you use subtract the values in the hire_date column from the date of today as the following query:
1 2 3 4 5 6 | SELECT first_name, last_name, now() - hire_date as diff FROM employees; |
1 2 3 4 5 6 | 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) |
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.
1 2 3 4 5 6 7 | SELECT employee_id, first_name, last_name, AGE(birth_date) FROM employees; |
1 2 3 4 5 6 | 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:
1 2 3 4 5 6 7 | SELECT employee_id, first_name, last_name, age('2015-01-01',birth_date) FROM employees; |
1 2 3 4 5 6 | 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) |
Extract year, quarter, month, week, day from a date value
To get the year, quarter, month, week, day from a date value from a date value, you use the EXTRACT() function. The following statement extracts the birth dates of employee into year, month, and day:
1 2 3 4 5 6 7 8 9 | 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; |
1 2 3 4 5 6 | 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 data type and some handy function to handle a date value.