## Thursday, February 11, 2021

### Calculating difference between timestamps in SQL

Update: In 2023 an improved function to do the same was released. You can read about it here.

Having discussed how to make timestamp values from numbers, the next step in what I need to do was to determine the difference between the start and the end times. In this case there is a SQL function I can use: TIMESTAMPDIFF

The basic syntax of the statement is:

 ```SELECT TIMESTAMPDIFF(< number >,CHAR(< end timestamp > - < start timestamp >)) FROM < file > ```

The numbers you can use are for the following time intervals:

• 1 = Microseconds
• 2 = Seconds
• 4 = Minutes
• 8 = Hours
• 16 = Days
• 32 = Weeks
• 64 = Months
• 128 = Quarters
• 256 = Years

Let me jump straight to some examples. In this first I am only going to calculate the differences as time values, but not microseconds. Microseconds has such a small range they could not be calculated for the timestamps used in these statements.

 ```01 SELECT START_TIMESTAMP,END_TIMESTAMP, 02 TO_CHAR( TIMESTAMPDIFF(2,CHAR(END_TIMESTAMP - START_TIMESTAMP)), '999G999G999G999') AS "Seconds", 03 TO_CHAR( TIMESTAMPDIFF(4,CHAR(END_TIMESTAMP - START_TIMESTAMP)), '999G999G999G999') AS "Minutes", 04 TO_CHAR( TIMESTAMPDIFF(8,CHAR(END_TIMESTAMP - START_TIMESTAMP)), '999G999G999G999') AS "Hours" 05 FROM TESTFILE ; ```

With all of calculated columns I have used the TO_CHAR function to format the result with commas. It makes it so much easier to visualize the results.

The results look like:

 ```START_TIMESTAMP END_TIMESTAMP -------------------------- -------------------------- 1992-06-30 11:00:00.000000 2021-01-01 00:00:00.000000 2020-12-31 04:53:22.000000 2021-01-25 14:01:00.000000 SECONDS MINUTES HOURS ----------- ---------- ------- 898,606,800 14,976,780 249,613 2,192,858 36,547 609 ```

And now for the date calculations:

 ```01 SELECT START_TIMESTAMP,END_TIMESTAMP, 02 TO_CHAR( TIMESTAMPDIFF(16,CHAR(END_TIMESTAMP - START_TIMESTAMP)), '999G999G999G999'), 03 TO_CHAR( TIMESTAMPDIFF(32,CHAR(END_TIMESTAMP - START_TIMESTAMP)), '999G999G999G999'), 04 TO_CHAR( TIMESTAMPDIFF(64,CHAR(END_TIMESTAMP - START_TIMESTAMP)), '999G999G999G999'), 05 TO_CHAR( TIMESTAMPDIFF(128,CHAR(END_TIMESTAMP - START_TIMESTAMP)), '999G999G999G999'), 06 TO_CHAR( TIMESTAMPDIFF(256,CHAR(END_TIMESTAMP - START_TIMESTAMP)), '999G999G999G999') 07 FROM TESTFILE ; ```

And the results:

 ```START_TIMESTAMP END_TIMESTAMP -------------------------- -------------------------- 1992-06-30 11:00:00.000000 2021-01-01 00:00:00.000000 2020-12-31 04:53:22.000000 2021-01-25 14:01:00.000000 DAYS WEEKS MONTHS QUARTERS YEARS ------ ----- ------ -------- ----- 10,400 1,481 342 114 28 25 3 0 0 0 ```

When using the TIMESTAMPDIFF there are some assumptions for years and months that can produce inaccurate results:

1. Year = 365 days
2. Year = 52 weeks
3. Month = 30 days

The lack of leap years, 53 week years, and months of 31 and 28 days are something to be aware of.

I only use this function to calculate the difference of time, normally seconds, as most timestamps I handle on a daily basis cover a period of time that is rarely more than 24 hours.

This article was written for IBM i 7.4, and should work for some earlier releases too.

1. Gracias por compartir..

2. Another fantastic example of what's achievable

3. What are the pros and cons for storing timestamps in local or UTC time? When people are storing timestamps, is there a preferred number of decimals people are using?

4. How do I calculate the time difference between the current time and the time from a file?

1. Use CURRENT_TIMESTAMP to replace the END_TIMESTAMP in the examples I have given above.

To prevent "comment spam" all comments are moderated.
```