For example, when an order is placed, we might want to calculate the delivery date based on the shipping time interval. This is useful if we want to set a date after a fixed period of time. Similarly, we can also use the INTERVAL function to add time to an existing timestamp and return the new timestamp. This shows that the difference between both the timestamps is around 9.5 days. Let us see it in action now.įigure 6 – Using the AGE function to calculate an interval This function takes in two timestamps as arguments and then returns the interval between them. This can be done in PostgreSQL using the AGE() function. In the first scenario, we would like to calculate the time taken in days since the order was placed until it was delivered. Let us consider that we have two timestamps as follows. Let us now look at how can we calculate the difference between two timestamps and also add time to an existing timestamp. 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. This can be anything, for example, the difference between days in two timestamps or the difference between hours in the two timestamps. While working with Date and Time calculations in PostgreSQL, sometimes we need to calculate the duration between two given timestamps. Returns the time zone difference in minutes.įigure 4 – Extract Function in PostgreSQLįigure 5 – Extract Function in PostgreSQLĪs 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. Returns the time zone difference in hours. Apart from these, the EXTRACT function also provides some other methods for extracting more date parts from the timestamps. 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. This can be done by using the following.įigure 3 – Extracting the time values from the timestamp in PostgreSQL Next, we can also extract the time values from the timestamp. You can use these columns to perform various calculations. These values are now available as separate columns. This can be done as follows.įigure 2 – Extracting date parts from the time zoneĪs you can see in the figure above, we have extracted the year, month and date from the time zone. Let us now try to extract the date values from the timestamp. The syntax for the EXTRACT function is as follows. Now in order to extract and print each of these parts separately, we can make use of the EXTRACT function in PostgreSQL. Let us first understand the anatomy of a timestamp and break down the timestamp to extract each of the parts into different columns.Įxtracts the year part from the timestamp.Įxtracts the milliseconds from the timestamp.Īs explained in the above table, the timestamp can be broken down into these parts. Since I am in Dublin, my time zone is UTC+1 (summertime). Let us first understand how a timestamp column looks like in PostgreSQL.įigure 1 – Sample Timestamp example in PostgreSQL with time zone informationĪs you can see in the figure above, we have used the now() function in PostgreSQL that returns the current time of the system. 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. These columns can be with or without time zone information. While working on a large database project, often we come across multiple columns that store data in timestamp format. Using the CAST function in PostgreSQL to convert timestamps to other formats Using the INTERVAL function in PostgreSQL In this article, we are going to focus on the following concepts.Įxtract date parts from existing timestamps Also, we can calculate time intervals using these functions that will help us analyze what has changed between those two timeframes. PostgreSQL provides a number of different date and time functions by default that can be used to calculate these kinds of KPIs. PostgreSQL is one of the leading open-source relational database systems that can be used to build operational databases as well as data warehouses. 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. These KPIs help businesses understand and gauge their performance and also aids in making decisions necessary for their development. Some of the most commonly used KPIs that involve date and time calculations are monthly sales, quarterly web traffic, year-over-year growth etc. 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. Working with Date and Time calculations is an integral part of any Data Analytics solution.
0 Comments
Leave a Reply. |