Working with Date and Time calculations is an integral part of any Data Analytics solution. Be it financial data or any data related to sales, dates always play a pivotal role in analyzing the trends and hence draw a pattern out from them. Some of the most commonly used KPIs that involve date and time calculations are monthly sales, quarterly web traffic, year-over-year growth etc. These KPIs help businesses understand and gauge their performance and also aids in making decisions necessary for their development. In this article, we are going to explore some of the important Date and Time functions that can be used in PostgreSQL and how they can be implemented henceforth.

PostgreSQL is one of the leading open-source relational database systems that can be used to build operational databases as well as data warehouses. PostgreSQL provides a number of different date and time functions by default that can be used to calculate these kinds of KPIs. Also, we can calculate time intervals using these functions that will help us analyze what has changed between those two timeframes. In this article, we are going to focus on the following concepts.

• Extract date parts from existing timestamps
• Using the INTERVAL function in PostgreSQL
• Using the CAST function in PostgreSQL to convert timestamps to other formats

Extract date parts from existing timestamps

While working on a large database project, often we come across multiple columns that store data in timestamp format. These columns can be with or without time zone information. Sometimes it is required that we do not use the entire timestamp for analysis, instead just use a part of it, for example, a day or an hour. Let us first understand how a timestamp column looks like in PostgreSQL.

Figure 1 – Sample Timestamp example in PostgreSQL with time zone information

As you can see in the figure above, we have used the now() function in PostgreSQL that returns the current time of the system. Since I am in Dublin, my time zone is UTC+1 (summertime). Let us first understand the anatomy of a timestamp and break down the timestamp to extract each of the parts into different columns.

As explained in the above table, the timestamp can be broken down into these parts. Now in order to extract and print each of these parts separately, we can make use of the EXTRACT function in PostgreSQL. The syntax for the EXTRACT function is as follows.

EXTRACT(part FROM timestamp);

Let us now try to extract the date values from the timestamp. This can be done as follows.

Figure 2 – Extracting date parts from the time zone

As you can see in the figure above, we have extracted the year, month and date from the time zone. These values are now available as separate columns. You can use these columns to perform various calculations. Next, we can also extract the time values from the timestamp. This can be done by using the following.

Figure 3 – Extracting the time values from the timestamp in PostgreSQL

In the above figure, the hour, minute and second values have been extracted from the timestamp and these can be used as parts of calculation where date and time are used. Apart from these, the EXTRACT function also provides some other methods for extracting more date parts from the timestamps. These are as follows.

Figure 4 – Extract Function in PostgreSQL

Figure 5 – Extract Function in PostgreSQL

As you can see in the figures above, we have used most of the commonly used date parts that are used with the EXTRACT function in PostgreSQL.

Using the INTERVAL function in PostgreSQL

While working with Date and Time calculations in PostgreSQL, sometimes we need to calculate the duration between two given timestamps. This can be anything, for example, the difference between days in two timestamps or the difference between hours in the two timestamps. These kinds of calculations are important in logistics or food delivery chains where customers might need to calculate the time taken to deliver the goods or services. Let us now look at how can we calculate the difference between two timestamps and also add time to an existing timestamp. Let us consider that we have two timestamps as follows.

• Order_Date = 2021-02-28 20:15:21.298284+01
• Delivery_Date = 2021-03-10 10:39:11.943206+01

In the first scenario, we would like to calculate the time taken in days since the order was placed until it was delivered. This can be done in PostgreSQL using the AGE() function. This function takes in two timestamps as arguments and then returns the interval between them. Let us see it in action now.

Figure 6 – Using the AGE function to calculate an interval

This shows that the difference between both the timestamps is around 9.5 days.

Similarly, we can also use the INTERVAL function to add time to an existing timestamp and return the new timestamp. This is useful if we want to set a date after a fixed period of time. For example, when an order is placed, we might want to calculate the delivery date based on the shipping time interval. This can be done as follows.

Figure 7 – Adding INTERVAL to existing timestamps

As you can see in the figure above, we have calculated the delivery date by adding 2 days to the order date. This is an easy way to add days, hours, or minutes, etc. to an existing timestamp.

Using the CAST function in PostgreSQL

The CAST function in PostgreSQL is used to convert values from one format to another. This is usually done to return a fixed data type for a field. An important point to note here would be that in order to use the CAST function, the values returned must be compatible with the datatype specified in the CAST function. The syntax of the CAST function is as follows.

CAST(expression as datatype)
CAST expression::datatype

We can use either of the expressions to use the CAST function in PostgreSQL. Let us now see it working.

Figure 8 – Usaing the CAST function in PostgreSQL

As you can see in the above figure, the datatypes returned by the two columns are date and timestamp respectively. However, the date expression that we have provided to the CAST function is still the same. In this manner, you can use the CAST function to convert the datatype from one to another.

Conclusion

In this article, we have explored a few of the important Date and Time functions in PostgreSQL. While building any modern data model, it is extremely important to implement a proper date dimension that can be thoroughly used to analyze data. Using these Date and Time functions will make the process of analyzing the data much easier as you can easily drill down and across the date dimension and make the necessary changes as required. To learn more about Date and Time Functions in PostgreSQL, you can also refer to the official documentation found here.

• Author
• Recent Posts

Aveek Das

Aveek is an experienced Data and Analytics Engineer, currently working in Dublin, Ireland. His main areas of technical interest include SQL Server, SSIS/ETL, SSAS, Python, Big Data tools like Apache Spark, Kafka, and cloud technologies such as AWS/Amazon and Azure.

He is a prolific author, with over 100 articles published on various technical blogs, including his own blog, and a frequent contributor to different technical forums.

In his leisure time, he enjoys amateur photography mostly street imagery and still life. Some glimpses of his work can be found on Instagram. You can also find him on LinkedIn

View all posts by Aveek Das

Latest posts by Aveek Das (see all)

• Getting started with PostgreSQL on Docker - August 12, 2022
• Getting started with Spatial Data in PostgreSQL - January 13, 2022
• An overview of Power BI Incremental Refresh - December 6, 2021