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.
Extracts the year part from the timestamp.
Extracts the month from the timestamp.
Extracts the day from the timestamp.
Extracts the hour from the timestamp.
Extracts the minute from the timestamp.
Extracts the seconds from the timestamp.
Extracts the milliseconds from the timestamp.
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.
Extracts the week from the date.
Returns the Day of Week from the date.
Returns the Day of Year from the date.
Returns the UNIX timestamp. Calculated from 01-01-1970.
Extracts the quarter from the date.
Extracts the time zone information.
Returns the time zone difference in hours.
Returns the time zone difference in minutes.
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)
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.
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.