Wednesday, February 17, 2021

SQL function to truncate timestamps

truncate timestamp

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.

2 comments:

  1. Thanks Simon for your pedagogic work, although the output in the statement number 4 'Day: Day name' I don't believe is accurate, shouldn't be Wednesday instead?

    ReplyDelete
    Replies
    1. If you break apart the statement on line 4 you will see why the answer is Sunday.

      TRUNC_TIMESTAMP('2021-02-17-17.07.59.420115','DAY') gives the date of the start of the week. Which is the same result as the previous result column, 2021-02-14 00:00:00.000000

      DATE(...) converts the timestamp to a date, 2021-02-21

      DAYNAME(...) returns the day of the week for that date, Sunday

      If you run this statement you see it step by step:
      SELECT TRUNC_TIMESTAMP('2021-02-17-17.07.59.420115','DAY') AS "Start of week",
      DATE(TRUNC_TIMESTAMP('2021-02-17-17.07.59.420115','DAY')) AS "Date",
      DAYNAME(DATE(TRUNC_TIMESTAMP('2021-02-17-17.07.59.420115','DAY'))) AS "Day"
      FROM SYSIBM.SYSDUMMY1 ;

      Delete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.