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.
Contact Us