PostgreSQL – Date Data Type
Last Updated :
28 Aug, 2020
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.
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...