PostgreSQL – Date Data Type

PostgreSQL supports a DATE data type to store date values. It takes 4 bytes of storage and ranges from 4713 BC to 5874897 AD. PostgreSQL uses the yyyy-mm-dd format for storing and inserting date values. 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.

Syntax: variable_name DATE

Let’s look into some examples for better understanding.
Example 1:
In this example we will create a new employee table that consists of employee_id, first_name, last_name, birth_date, and hire_date columns, and the data types of the birth_date and hire_date columns are DATE.

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
);

Now insert some data to the tables using the below command:

INSERT INTO employees (first_name, last_name, birth_date, hire_date)
VALUES ('Raju', 'Kumar', '1996-12-02', '2020-01-01'),
       ('Nikhil', 'Aggarwal', '1997-08-24', '2019-01-01'),
       ('Anshul', 'Aggarwal', '1994-05-11', '2017-01-01');

Now that the data has been inserted let’s verify them by using the below command:

SELECT * FROM employees;

Output:

Example 2:
In the same table that we created in example 1, we will now query the age of the employees from the already present data using the command below:

SELECT
    employee_id,
    first_name,
    last_name,
    AGE(birth_date)
FROM
    employees;

Output:

Note: In the above example notice the use of AGE() function. To calculate age at the current date in years, months, and days, you use the AGE() function.

Last Updated : 28 Aug, 2020