Extracting parts of date and time using a SQL function

Another of the enhancements with the latest round of Technical Refreshes, TR5 for IBM i 7.3 and TR9 for 7.2, is to the Db2 for i Extract function. This function will retrieve parts of dates and times from date, time, and timestamp variables. Prior to these TRs I could extract just basic information from the date (year, month, day) or time (hour, minute, second).

The enhancements allows me to retrieve a whole lot more information than I will ever need to know about a date or time.

Let me start with a SQL DDL table I built to contain the data for my examples:

01 CREATE TABLE QTEMP.TESTFILE ( 02 COLDATE DATE, 03 COLTIME TIME, 04 COLTIMESTAMP TIMESTAMP 05 ) ; 06 INSERT INTO QTEMP.TESTFILE VALUES('2013-06-10','09:27:55','2018-10-31-18.44.55.123456') ;

The table, TESTFILE, contains a date, time, timestamp columns. And I inserted one row with data for me to play with.

This what I can extract from dates and timestamps:

• EPOCH:  Number of seconds since 12:00 AM 1970.
• MILLENIUM or MILLENIUMS:  The number of the thousand year period the date is in. As I am writing this in 2018 the millennium will be "2".
• CENTURY or CENTURIES
• DECADE or DECADES:  The ten year period the date is in. The decade 2018 is in will be expressed as "201".
• YEAR* or YEARS
• QUARTER:  Quarter of the year.
• MONTH* or MONTHS
• WEEK:  Week of the year. The week starts on Monday.
• DAY* or DAYS
• DOW:  Day of week. 1=Sunday, etc.
• DOY:  Day of year, 1 – 366.

*  These are the types of information that was available before these TRs.

Let me put all of this into a single Select statement:

01 SELECT COLDATE, 02 EXTRACT(EPOCH FROM COLDATE) AS EPOCH, 03 EXTRACT(MILLENNIUM FROM COLDATE) AS MILLENNIUM, 04 EXTRACT(CENTURY FROM COLDATE) AS CENTURY, 05 EXTRACT(DECADE FROM COLDATE) AS DECADE, 06 EXTRACT(YEAR FROM COLDATE) AS YEAR, 07 EXTRACT(QUARTER FROM COLDATE) AS QUARTER, 07 EXTRACT(MONTH FROM COLDATE) AS MONTH, 08 EXTRACT(WEEK FROM COLDATE) AS WEEK, 09 EXTRACT(DAY FROM COLDATE) AS DAY, 10 EXTRACT(DOW FROM COLDATE) AS DAY_OF_WEEK, 11 EXTRACT(DOY FROM COLDATE) AS DAY_OF_YEAR 12 FROM QTEMP.TESTFILE

And this is what I get as a result:

COLDATE EPOCH MILLENNIUM CENTURY DECADE YEAR 06/10/13 1,370,822,400 2 20 201 2,013 QUARTER MONTH WEEK DAY DAY_OF_WEEK DAY_OF_YEAR 2 6 24 10 2 161

This is the date of the first post I published on this blog.

There are other ways to get the some of the same information from the date using other SQL functions. For example:

01 SELECT QUARTER(COLDATE), 02 DAYOFWEEK(COLDATE), 03 DAYOFYEAR(COLDATE) 04 FROM QTEMP.TESTFILE

I would not say that one is better than the other. It should be which method you think makes it easier for others to understand when looking at your code.

I can also extract information using the current system date:

01 SELECT EXTRACT(YEAR FROM CURRENT_DATE) AS CURRENT_YEAR 02 FROM SYSIBM.SYSDUMMY1 CURRENT_YEAR 2,018

From dates and timestamps I can extract the following types of information:

• HOUR* or HOURS
• MINUTE* or MINUTES
• SECOND* or SECONDS
• MILLISECOND or MILLISECONDS:  Number of seconds and to the thousandth part of the second, multiplied by a thousand.
• MICROSECOND or MICROSECONDS:  Number of seconds to the millionth part of the second, multiplied by a million.

*  These are the types of information that was available before these TRs.

I can put all of that into a Select statement:

01 SELECT COLTIME, 02 EXTRACT(HOURS FROM COLTIME) AS HOURS, 03 EXTRACT(MINUTES FROM COLTIME) AS MINUTES, 04 EXTRACT(SECONDS FROM COLTIME) AS SECONDS, 05 EXTRACT(MILLISECONDS FROM COLTIME) AS MILLISECONDS, 06 EXTRACT(MICROSECONDS FROM COLTIME) AS MICROSECONDS 07 FROM QTEMP.TESTFILE

I have returned to me the following results:

COLTIME HOURS MINUTES SECONDS MILLISECONDS MICROSECONDS 09:27:55 9 27 55.000000 55,000 55,000,000

I can use the extract with the current system time too.

01 SELECT EXTRACT(MICROSECONDS FROM CURRENT_TIME) 02 FROM SYSIBM.SYSDUMMY1 EXTRACT 23,000,000

I can use all of the above with a timestamp:

01 SELECT COLTIMESTAMP, 02 EXTRACT(EPOCH FROM COLTIMESTAMP) AS EPOCH, 03 EXTRACT(MILLENNIUM FROM COLTIMESTAMP) AS MILLENNIUM, 04 EXTRACT(CENTURY FROM COLTIMESTAMP) AS CENTURY, 05 EXTRACT(DECADE FROM COLTIMESTAMP) AS DECADE, 06 EXTRACT(YEAR FROM COLTIMESTAMP) AS YEAR, 07 EXTRACT(QUARTER FROM COLDATE) AS QUARTER, 08 EXTRACT(MONTH FROM COLTIMESTAMP) AS MONTH, 09 EXTRACT(WEEK FROM COLTIMESTAMP) AS WEEK, 10 EXTRACT(DAY FROM COLTIMESTAMP) AS DAY, 11 EXTRACT(DOW FROM COLTIMESTAMP) AS DAY_OF_WEEK, 12 EXTRACT(DOY FROM COLTIMESTAMP) AS DAY_OF_YEAR, 13 EXTRACT(HOURS FROM COLTIMESTAMP) AS HOURS, 14 EXTRACT(MINUTES FROM COLTIMESTAMP) AS MINUTES, 15 EXTRACT(SECONDS FROM COLTIMESTAMP) AS SECONDS, 16 EXTRACT(MILLISECONDS FROM COLTIMESTAMP) AS MILLISECONDS, 17 EXTRACT(MICROSECONDS FROM COLTIMESTAMP) AS MICROSECONDS 18 FROM QTEMP.TESTFILE

The results…

COLTIMESTAMP 2018-10-31-18.44.55.123456 EPOCH MILLENNIUM CENTURY 1,541,011,495 2 20 DECADE YEAR QUARTER MONTH WEEK DAY DAY_OF_WEEK 201 2,018 4 10 44 31 4 DAY_OF_YEAR 304 HOURS MINUTES SECONDS MILLISECONDS MICROSECONDS 18 44 55.123456 55,123 55,123,456

Again I can use the extract with the current system timestamp.

01 SELECT EXTRACT(HOUR FROM NOW()) AS HOUR 02 FROM SYSIBM.SYSDUMMY1 HOUR 22

The SQL Now built in function returns the current system timestamp. It was also enhanced by the latest TRs.

I can even use the extract in a RPG program.

01 **free 02 dcl-f TESTFILE extfile('QTEMP/TESTFILE') 03 rename(TESTFILE:INPUT) 04 alias ; 05 dcl-s Epoch uns(10) ; 06 read INPUT ; 07 exec sql SET :Epoch = EXTRACT(EPOCH FROM :COLDATE) ; 08 dsply ('Epoch = ' + %char(Epoch)) ; 09 *inlr = *on ;

I would not recommend reading a SQL table with native RPG I/O. With the current releases of IBM i, 7.3 and 7.2, using SQL I/O is more efficient and faster. But I just want to show what is possible.

Line 1: With the latest releases of IBM i there is no excuse not be writing all your code in totally free format RPG.

Lines 2 – 4: I define the SQL table just like any other file. As the table is in the library QTEMP I use the EXTFILE, line 2, to define that the table is in QTEMP. As I did not define a record format name in the table, the record format name will be the same as the table, therefore, I have to rename it. ALIAS in the file definition means that I will be using the long names for the columns in the file.

Line 5: I need to define a variable to contain the value of the epoch I retrieve from the table's column.

Line 6: Read the table. There is no need for me to have logic to check for end of file as there is only one row in the table, and this is an example program.

Line 7: Here I am using the EXTRACT function to get the epoch from the column COLDATE, and place it in the variable Epoch.

Line 8: I use the display operation code to show what was retrieved.

The result is:

DSPLY Epoch = 1370822400

I like what this does, and I can think of times when the extract SQL function would be every useful. But I cannot think of a reason why I would use the epoch.

 

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

 

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