SQL NOW and playing with timestamps

This post started off as one thing and quickly morphed into something more. I was going through all of the additions and changes in the new Technical Refreshes for IBM i 7.3 and started playing with the Db2 for i (SQL) NOW built in function.

NOW returns the current timestamp, and it has been around since at least 7.1. The latest TR allows me to give the decimal precision (fraction) of the seconds, for an example see below.

2018-10-17-18.26.32.206964

As I said now I can give NOW the decimal precision I want from zero to twelve.

01 SELECT NOW() AS NONE, 02 NOW(0) AS ZERO, 03 NOW(1) AS ONE, 04 NOW(6) AS SIX, 05 NOW(12) AS TWELEVE 06 FROM SYSIBM.SYSDUMMY1

In this example I will have returned five columns with different decimal precisions. The first column is the same as was returned before this TR.

NONE 2018-10-17-18.26.32.206964 ZERO 2018-10-17-18.23.50 ONE 2018-10-17-18.23.50.2 SIX 2018-10-17-18.23.50.206964 TWELVE 2018-10-17-18.23.50.206964701171

The default for this built in function is the same as passing the parameter six.

The advantage of playtime is I can try things that do not work just to see what happens. In this case what would happen if I wanted a decimal precision of thirteen, one more than the allowed maximum.

01 SELECT NOW(13) 02 FROM SYSIBM.SYSDUMMY1 Argument 01 of function NOW not valid.

I am sure some of you know that this functionality was available before with SQL's CURRENT_TIMESTAMP.

01 SELECT CURRENT_TIMESTAMP AS NONE, 02 CURRENT_TIMESTAMP(12) AS TWELVE, 03 CURRENT_TIMESTAMP(0) AS ZERO 04 FROM SYSIBM.SYSDUMMY1

The results are the same as if I had used NOW.

NONE 2018-10-17-18.33.04.277570 TWELVE 2018-10-17-18.33.04.277570835937 ZERO 2018-10-17-18.33.04

The advantage of playtime is I can go off at tangent from my original purpose. In this case I wondered if I could do the same with the timestamp built in function in RPG. Looking in the IBM's KnowledgeCenter for IBM i releases 7.3 and 7.2 I find that it is possible to pass the decimal precision to the BiF.

01 **free 02 dcl-s Count packed(2) ; 03 dcl-s Retrieved char(36) ; 04 dsply '*** RPG ***' ; 05 dsply ('%TIMESTAMP() = ' + %char(%timestamp()) ) ; 06 dsply ('%TIMESTAMP(0) = ' + %char(%timestamp(*sys:0)) ) ; 07 for Count = 1 to 10 ; 08 dsply ('%TIMESTAMP(12) = ' + %char(%timestamp(*sys:12)) ) ; 09 endfor ; 10 dsply '*** SQL ***' ; 11 exec sql SET :Retrieved = NOW() ; 12 dsply ('NOW() = ' + Retrieved) ; 13 exec sql SET :Retrieved = NOW(12) ; 14 dsply ('NOW(12) = ' + Retrieved) ; 15 exec sql SET :Retrieved = CURRENT_TIMESTAMP(12) ; 16 dsply ('CURRENT_T(12) = ' + Retrieved) ; 17 *inlr = *on ;

Line 1: Everything is now all free RPG.

Lines 2 and 3: Definition of the variables I will be using in this program.

Line 4: This is the section code where I am using RPG's timestamp BiF.

Line 5: I am sure this is how most use the timestamp.

Line 6: Here I want no decimal precision, no fraction of seconds. Notice that the %TIMESTAMP has two parameters:

1. *SYS use the system generated timestamp
2. 0 the decimal precision

Lines 7 – 9: I am using a For group to display the timestamp with twelve places of decimal precision. This is so I can see how RPG's timestamp use the whole timestamp.

Line 10: In this section I will be using SQL to get the timestamp.

Line 11: I am moving the value from SQL's NOW into a character variable.

Line 12: Using the display operation code to display what was retrieved.

Lines 13 and 14: I am retrieving the full length of the timestamp using NOW(12), and then displaying it.

Lines 15 and 16: Just to show the same type of value is retrieved using CURRENT_TIMESTAMP.

What does the output from this program look like?

DSPLY *** RPG *** DSPLY %TIMESTAMP() = 2018-10-17-19.18.48.031000 DSPLY %TIMESTAMP(0) = 2018-10-17-19.18.48 DSPLY %TIMESTAMP(12) = 2018-10-17-19.18.48.876000000000 DSPLY %TIMESTAMP(12) = 2018-10-17-19.18.49.336000000000 DSPLY %TIMESTAMP(12) = 2018-10-17-19.18.50.147000000000 DSPLY %TIMESTAMP(12) = 2018-10-17-19.18.50.853000000000 DSPLY %TIMESTAMP(12) = 2018-10-17-19.18.52.731000000000 DSPLY %TIMESTAMP(12) = 2018-10-17-19.18.54.948000000000 DSPLY %TIMESTAMP(12) = 2018-10-17-19.18.55.977000000000 DSPLY %TIMESTAMP(12) = 2018-10-17-19.18.56.766000000000 DSPLY %TIMESTAMP(12) = 2018-10-17-19.18.57.663000000000 DSPLY %TIMESTAMP(12) = 2018-10-17-19.18.58.510000000000

What I find annoying using RPG's generated timestamp is that only the first three decimal places contain any value, the rest are always zero.

The same is not true with SQL, I can have a full timestamp.

DSPLY *** SQL *** DSPLY NOW() = 2018-10-17-19.19.01.586369 DSPLY NOW(12) = 2018-10-17-19.19.02.784708544921 DSPLY CURRENT_T(12) = 2018-10-17-19.19.03.977838224609

Much better than RPG's attempt at providing me with a timestamp.

Which would I use: CURRENT_TIMESTAMP or NOW()?

IBM recommendation is to use CURRENT_TIMESTAMP for "maximum portability" to other flavors of SQL. If I am going to embed SQL statements where I want a timestamp in RPG I don't care about portability, as it will be running on IBM i. Therefore, I can use NOW as it quicker type and takes up less room.

I think this post shows how valuable time for play is for experimenting with new things. Every developer, no matter what platform or language they work with, need to make themselves playtime to give themselves a chance to learn and experiment with new things.

 

You can learn more about this from the IBM website:

• SQL's NOW built in function
• SQL CURRENT_TIMESTAMP
• RPG's %TIMESTAMP built in function

 

This article was written for IBM i 7.3 TR5 and 7.2 TR9.