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 format

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.

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;

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

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

Now() function

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

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

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');

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;

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;

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.

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

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;

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;

Extract a month

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

Extract a Quarter

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

Extracting the day of the week

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

Extracting the day of the year

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

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' );
;

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.

Visibility into it all.

Monitor your databases and resolve performance issues quickly.

Try Foglight

SELECT * FROM pg_timezone_names;

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

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';

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';

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

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

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.