Wednesday, October 17, 2018

SQL NOW and playing with timestamps

using sql to get the timestamp

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:

 

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

5 comments:

  1. I was curious to see if the RPG %TIMESTAMP was much more efficient thatn the SQL NOW. So I ran a test of looping through each 100 times. I was surprised to find that the SQL NOW was faster. It took about half the time to generate the SQL timestamp than the RPG timestamp. So there is no reason not to use the SQL timestamp.

    ReplyDelete
    Replies
    1. Thank you for sharing your results, you can learn some interesting stuff when you get to play.

      I have to admit I am not surprised. SQL is getting faster and faster with every TR and release.

      Delete
  2. How can I strip off the time if I just want YYYY-MM-DD ?

    ReplyDelete
  3. DATE(CURRENT_TIMESTAMP) should do it.

    ReplyDelete

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.