Exploring Postgres Date Formats and Their Different Functions

In this article, we will explore Postgres dates, the different date data types, uses and functions.

Date and timestamps are helpful for data analysis and storing data for checking when an event actually took place. For example, when you have purchase and sales orders, monthly or quarterly earnings and more. Date formats vary across countries, therefore it can be a complicated task for those who are new to database management and are working with date columns. The format or data type of the date column should always match the user’s input. Additionally, you should convert the date format display per your user’s requirements.

Postgres has a variety of supported data types. Before proceeding further, I suggest you refer to Exploring the Various Postgres Data Types to understand these in further detail.

Postgres DATE data type

Postgres uses the DATE data type for storing different dates in YYYY-MM-DD format. It uses 4 bytes for storing a date value in a column.

  • Lowest Date: 4713 BC
  • Highest Date: 5874897 BC
  • Date format: YYYY-MM-DD ( Ex. 2021-01-01)

You can design a Postgres table with a DATE column and use the keyword DEFAULT CURRENT_DATE to use the current system date as the default value in this column.

CREATE TABLE SalesOrders (
Order_id serial PRIMARY KEY,
Orderdetails VARCHAR (255) NOT NULL,
OrderDate DATE NOT NULL DEFAULT CURRENT_DATE
);
INSERT INTO SalesOrders (Orderdetails)
VALUES('Sample Order for customer ABC');

SELECT * FROM SalesOrders;

As shown below, the SQL inserts a value for the [OrderDate] column automatically with the current system date in the YYYY-MM-DD format.

Query editor

Postgres DATE functions

We often need to modify the date format or do calculations on existing values stored in the Postgres table. For example, in India, the usual date format is DD-MM-YYYY. Therefore, when an Indian user views the data, we likely will want him to view data in the format he is most familiar with. In this case, SQL Functions plays a vital role.

TO_CHAR() function

This function is helpful for giving the output of a Postgres date value in a specified format. It accepts the following two parameters.

  • Input date: This is the date that you want to convert into a specific format.
  • Date format: This is where you specify the new date format.

The following query converts existing date values stored in the [SalesOrders] table into DD-MM-YYYY format.

SELECT Orderdetails,OrderDate as ExistingDateformat,
to_char(OrderDate,'DD-MM-YYYY') As NewDateFormat FROM SalesOrders;

Data output messages

The supported values in the to_char() function are as shown below.

YYYY Year in four digits
YYY Last three-digits of a year
YY Last two digits of a year
MONTH Month name in capital letters
Month Month name with the first letter capitalized
month Month name in lowercase letters
MON/Mon/mon Month abbreviation in all capital, first letter capitalized and all lowercase letters, respectively
MM Month number (01-12)
DAY/Day/day Day name in all capital, first letter capitalized and all lowercase letters, respectively
DDD Day of the year (001 to 366)
DD Day of the month (01 to 31)
D Day of the week (Sunday (1) to Saturday (7))
W Week of the month
WW Week of the year

A few examples of different date formats are specified in the following SQL.

SELECT Orderdetails,OrderDate as ExistingDateformat,
to_char(OrderDate,'DD-MM-YYYY') As DDMMYYYY,
to_char(OrderDate,'DD-MM-YY') As DDMMYY,
to_char(OrderDate,'DD.MM.YY') As "DD.MM.YY",
to_char(OrderDate,'MM/DD/YYYY') As "MM/DD/YYYY",
to_char(OrderDate,'DAY MM/DD/YYYY') As "MM/DD/YYYY",
to_char(OrderDate,'DDD MM/DD/YYYY') As "DDD MM/DD/YYYY"
FROM SalesOrders

Query history

Now() function

The Now() function returns the current system timestamp (date and time).

Query editor history

You can specify double colons (::) to cast a DATETIME value to a DATE value.

Postgres Query editor

You can combine TO_CHAR() and the Now() function to convert the current timestamp into the specified format.

SELECT TO_CHAR(NOW() :: DATE, 'dd-mm-yyyy');

Postgres date

Minus and interval operator

You can use the minus (-) operator to calculate the difference between two dates. For example, the query below returns the interval between the current timestamp and [Orderdate] from the SalesOrders table.

SELECT Orderdate,now() as currentdate,
now()-Orderdate as Interval FROM SalesOrders
where order_id=2;

Postgres data output

You can also specify an interval for returning the date after a specified period. For example, the below SQL query gives the following values.

  • For future date, specify interval value 2 hours from the current timestamp: now() + interval ‘2 hour’
  • For future date, specify interval value of 1 day from the current timestamp: now() + interval ‘1 day’
  • For past date, specify interval value one year from the current timestamp: Now() – interval ‘1 year’
  • Calculate one-year past the date from the value stored in the [Orderdate] column: orderdate – interval ‘1 year’
SELECT (NOW() + interval '2 hour') AS twohourslater,
(NOW() + interval '1 day') AS Onedaylater,
(NOW() - interval '1 year') AS OneYearBefore,
(Orderdate- interval '1 year') as Orderdatedifference
from salesorders;

SELECT NOW data output

AGE() function

The AGE() function returns the date difference in years, months and days. You can use this function to calculate a person’s age.

This function accepts two date parameters and subtracts the first date value from the second one.

SELECT age

If you reverse the values in the above function script, it returns the value in the negative.

SELECT age

In another example, let’s say someone has a birthdate of 1990-07-01. Therefore, a person’s age can be calculated as below.

SELECT current_date,
AGE(timestamp '1990-07-01') as EmpAge;

Query editor Postgres date

EXTRACT() function

The Extract() function returns the day, week, month, year, and quarter from the specified date value.

Extract a year

SELECT EXTRACT(YEAR FROM TIMESTAMP '2021-06-28 10:30:15') as year;

SELECT extract

Extract a month

SELECT EXTRACT(Month FROM TIMESTAMP '2021-06-28 10:30:15') as Month;

SELECT extract in Postgres

Extract a Quarter

SELECT EXTRACT(QUARTER FROM TIMESTAMP '2021-06-28 10:30:15') as QUARTER;

SELECT extract in Postgres

Extracting the day of the week

SELECT EXTRACT(DOW FROM TIMESTAMP '2021-06-28 10:30:15') as DOW ;

SELECT extract double precision

Extracting the day of the year

SELECT EXTRACT(DOY FROM TIMESTAMP '2021-06-28 10:30:15') as DOY ;

SELECT extract double precision

You can also use the EXTRACT() function in combination with an INTERVAL. For example, below we specify the interval as 7 years 9 month 20 days 09 hours 12 minutes and 13 seconds. The extract function returns the individual values.

SELECT EXTRACT(YEAR
FROM INTERVAL '7 years 9 months 20 days 09 hours 12 minutes 13 second' ),
EXTRACT(Month
FROM INTERVAL '7 years 9 months 20 days 09 hours 12 minutes 13 second' ),
EXTRACT(Day
FROM INTERVAL '7 years 9 months 20 days 09 hours 12 minutes 13 second' ),
EXTRACT(hour
FROM INTERVAL '7 years 9 months 20 days 09 hours 12 minutes 13 second' ),
EXTRACT(Minute
FROM INTERVAL '7 years 9 months 20 days 09 hours 12 minutes 13 second' );
;

Query editor history

AT TIMEZONE

Sometimes, you need to convert the timestamp into a different time zone. For example, you can store date values in UTC (Universal time coordinator) and convert the time zone as required.

SELECT * FROM pg_timezone_names;

Query editor history

To check the current time zone, use SHOW TIMEZONE as shown below.

Time zone date format in Postgres

You can choose the required time zone value from the pg_timezone_names and use AT TIME ZONE for getting the output per the specified time zone.

SELECT '2021-06-28 07:09:19'::timestamp AT TIME ZONE 'America/Chicago';

Time stamp date format in Postgres

Similarly, we can get different time zone outputs using AT TIME ZONE.

SELECT '2021-06-28 07:09:19'::timestamp AT TIME ZONE 'America/New_York';

Query editor history

SELECT '2021-06-28 07:09:19'::timestamp AT TIME ZONE 'Asia/Qatar';

Query editor history

SELECT '2021-06-28 07:09:19'::timestamp AT TIME ZONE 'Europe/Istanbul';

Query editor history timezone

Summary

Postgres date types are essential and valuable when storing date and timestamps in almost every table of a relational database. You need them for various purposes such as order insertion or when updating timestamp, purchases and sales orders, event details, customer and employee information and more. You can use multiple Postgres functions to convert a date type to the required time zone, format and specific information to simplify the extraction and analysis of your data.

About the Author

Rajendra Gupta

Rajendra Gupta is a MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" in 2020 and 2021 at SQLShack.

Related Articles