PostgreSQL: DATE Data Type

In PostgreSQL, the DATE type is used to store date values. PostgreSQL uses the date format as 'yyyy-mm-dd' e.g. '2022-12-25' is the default format for inserting data into the DATE column.

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 1000-01-01 to 9999-12-31.

PostgreSQL, supports the CURRENT_DATE function that gives the current date. You can use CURRENT_DATE for setting the DEFAULT value of the DATE column.

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 invoice_date column.

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 Invoice table.

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.

SELECT CURRENT_DATE;

Get Current Date and Time

To get the current date and time along with the time zone, use the now() function of PostgreSQL.

SELECT now();

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 DATE

SELECT now()::date;

DATE Formats

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 CURRENT_DATE in dd-mon-yyyy format.

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 Employee table.

The following query gets the number of days of service provided by all employees of the Employee table.

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;