PostgreSQL: DATE Data Type
In PostgreSQL, the
DATE type is used to store date values.
PostgreSQL uses the date format as
'2022-12-25' is the default format for inserting data into the
DATE datatype takes 4 bytes of storage. The minimum and maximum range for
date datatype in PostgreSQL is
4713 BC to
5874897 AD or
PostgreSQL, supports the
CURRENT_DATE function that gives the current date. You can use
CURRENT_DATE for setting the DEFAULT value of the
Let's create an
Invoice table with the
invoice_date column as the
DATE data type. As
DEFAULT CURRENT_DATE clause is sepcified for the
invoice_date column, it accepts the current date as the default value.
CREATE TABLE invoice ( id SERIAL NOT NULL PRIMARY KEY, header_text VARCHAR(50) NOT NULL, invoice_date DATE DEFAULT CURRENT_DATE );
Now let's insert data to the
Invoice table, the first insert is without specifying
invoice_date, and the second is with
invoice_date. While inserting data into the
Invoice table if you do not specify data for the
invoice_date column, it will take the default value which is the current date (today's date), and store it in the
INSERT INTO Invoice(header_text) VALUES('Invoice for Customer A'); INSERT INTO Invoice(header_text, invoice_date) VALUES('Invoice for Customer B', DATE '2022-10-06');
Let's validate it by selecting data from
PostgreSQL Date functions
PostgreSQL provides some
DATE functions for fetching different date values.
Get Current Date
To get the current date, use the
CURRENT_DATE function as below.
Get Current Date and Time
To get the current date and time along with the time zone, use the
now() function of PostgreSQL.
To get only the date part out of the full date and time, use double colons (::) or the
CAST operator to convert the
DATETIME value to
To output, the date in a specific format, use the
TO_CHAR() function of PostgreSQL. The
TO_CHAR() function takes two arguments, the first argument is a date that you want to format, and the second is a template that defines the output format.
For example, below displays
SELECT TO_CHAR(CURRENT_DATE, 'dd-mon-yyyy');
Get Interval between two dates
Use minus (-) operator to get interval between two dates.
Consider the following
The following query gets the number of days of service provided by all employees of the
SELECT first_name, last_name, (current_date - hire_date) AS service_provided FROM Employee;
To get service provided in form of year, month and days, use the
AGE() function provided by PostgreSQL as below. The
AGE() function takes the date value as the input parameter, subtracts the date provided from the current date, and provides the result in the format of year, month and days.
SELECT first_name, last_name, AGE(hire_date) AS service_provided FROM Employee;
Extract Date Fields
PostgreSQL provides the
EXTRACT() function to extract the year, quarter, month, week, and day from a date value.
SELECT CURRENT_DATE, EXTRACT(YEAR FROM CURRENT_DATE) AS Year, EXTRACT(MONTH FROM CURRENT_DATE) AS Month, EXTRACT(DAY FROM CURRENT_DATE) AS Day, EXTRACT(QUARTER FROM CURRENT_DATE) AS Quarter, EXTRACT(WEEK FROM CURRENT_DATE) AS Week;