SQL function to truncate timestamps

I stumbled across this SQL function and thought it might be something I could use in the future, and that some of you might find it useful too. TRUNC_TIMESTAMP will truncate a timestamp to give another based on the type of truncation. For example if I wanted to truncate to the month it returns a new timestamp of the lowest value for the year and month.

It is not new, I can find it in the IBM KnowledgeCenter back to IBM i 7.1, it just surprises me that I have not discovered it before.

Rather than try to explain how this function works I think it is easier to show with examples. In these examples I am going to use the special register CURRENT_TIMESTAMP as the timestamp I will be truncating.

01 SELECT CURRENT_TIMESTAMP AS "Current", 02 TRUNC_TIMESTAMP(CURRENT_TIMESTAMP,'YY') AS "Year", 03 TRUNC_TIMESTAMP(CURRENT_TIMESTAMP,'MM') AS "Month", 04 TRUNC_TIMESTAMP(CURRENT_TIMESTAMP,'DD') AS "Day", 05 TRUNC_TIMESTAMP(CURRENT_TIMESTAMP,'HH') AS "Hour", 06 TRUNC_TIMESTAMP(CURRENT_TIMESTAMP,'MI') AS "Minute", 07 TRUNC_TIMESTAMP(CURRENT_TIMESTAMP,'SS') AS "Seconds", 08 FROM SYSIBM.SYSDUMMY1 ;

As you can see in this example is that the function has two parameters:

1. Timestamp
2. Format

The second parameter, format, is optional. If it is not passed the format is assumed to be 'DD', days.

There are synonyms for the format. I have given the shortest value as it keeps the length of part of the statement short so it will fit in the width of this post. This is not the full list of all the synonyms as there are some that are in my opinion unnecessary or just bizarre.

• Year: YY, YYYY, YEAR
• Month: MM, MON, MONTH

Let me show the results from the above statement so you can see what these all do to the current timestamp:

Current Year -------------------------- -------------------------- 2021-02-17 17:05:06.346986 2021-01-01 00:00:00.000000 Month Day -------------------------- -------------------------- 2021-02-01 00:00:00.000000 2021-02-17 00:00:00.000000 Hour Minute -------------------------- -------------------------- 2021-02-17 17:00:00.000000 2021-02-17 17:05:00.000000 Seconds -------------------------- 2021-02-17 17:05:06.000000

In the Year result only the year was returned, the rest of the timestamp is the lowest value for the year.

The Month result has only the year and month, and the rest of the timestamp is the lowest value at timestamp can be.

You can see the pattern when you look at the other results. The truncated part of the timestamp has the value of the current timestamp, while the rest of the timestamp is at its lowest value.

There are some "odd balls" so I included them in their own statement:

01 SELECT CURRENT_TIMESTAMP AS "Current", 02 TRUNC_TIMESTAMP(CURRENT_TIMESTAMP,'DD') AS "Day DD", 03 TRUNC_TIMESTAMP(CURRENT_TIMESTAMP,'DAY') AS "Start of week", 04 DAYNAME(DATE(TRUNC_TIMESTAMP('2021-02-17-17.07.59.420115', 'DAY'))) AS "DAY:Day name", 05 TRUNC_TIMESTAMP(CURRENT_TIMESTAMP,'WW') AS "WW:Date", 06 DAYNAME(DATE(TRUNC_TIMESTAMP('2021-02-17-17.07.59.420115', 'WW'))) AS "WW:Day name", 07 DAYNAME('2021-01-01') AS "FoY:Day name" 08 FROM SYSIBM.SYSDUMMY1 ;

I am going to show the results and the explain what these "odd balls" are doing.

Current Day DD -------------------------- -------------------------- 2021-02-17 17:07:59.420115 2021-02-17 00:00:00.000000 DAY:Start of week DAY:Day name -------------------------- ------------ 2021-02-14 00:00:00.000000 Sunday WW:Date WW:Day name FoY:Day name -------------------------- ----------- ------------ 2021-02-12 00:00:00.000000 Friday Friday

Line 1: Current timestamp. It is different from the previous statement because it was run later.

Line 2: This is the same truncation to the day as was shown before.

Line 3: The format 'DAY' does not do the same as 'DD'. It returns the date for the first day of the week for the date in the timestamp. In this example this is February 14, 2021.

Line 4: I am using the DAYNAME function to return the name of the day from the timestamp generated using 'DAY'.

Line 5: The 'WW' is the oddest of the "odd balls". TRUNC_TIMESTAMP determines the day of the week that the year started on, in 2021 that is Friday, and then returns the date for that last day of the week. In this example it returned the date of last Friday: February 12, 2021.

Line 6: This line uses the DATENAME to confirm that the timestamp returned using the 'WW' really is Friday.

Line 7: And then use the DATENAME to confirm that this year did start on Friday.

As I mentioned at the start of this post I might use this sometimes, I am not so sure about the "odd balls". Do you use TRUNC_TIMESTAMP? If you do please let me know how in the comments below.

 

You can learn more about the TRUNC_TIMESTAMP SQL function from the IBM website here.

 

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