Calculating the number of seconds for a time

I have to admit this was a strange request. Interfacing data to another application is no big deal, but the way it wanted its date and time data formatted was. It wanted a character date, in *MDY format with the date separator characters, character time, with dots as the separator character, and an integer of the number of seconds that the time represented.

I could calculate the seconds as:

TotalSeconds = (Hours x 3600) + (Minutes x 60) + Seconds

Fortunately I stumbled across a SQL scalar function that would do this for me: MIDNIGHT_SECONDS. This returns the number of seconds that the time represents. Which is exactly what I am looking for.

I can demonstrate this with the following SQL statement:

01 VALUES (TO_CHAR(CURRENT_TIMESTAMP,'MM/DD/YY'), 02 TO_CHAR(CURRENT_TIMESTAMP,'HH24.MI.SS'), 03 (EXTRACT(HOURS FROM CURRENT_TIMESTAMP) * 3600) + 04 (EXTRACT(MINUTES FROM CURRENT_TIMESTAMP) * 60) + 05 EXTRACT(SECONDS FROM CURRENT_TIMESTAMP)), 06 MIDNIGHT_SECONDS(CURRENT_TIMESTAMP))

Line 1: I am extracting the date from the current timestamp and formatting to character in *MDY format with a date separator.

Line 2: I am extracting the time from the current timestamp and using a dot as the separator. The "HH24" returns the hour in the 24-hour clock format.

Line 3 – 5: Here I am calculating the number of seconds as I described earlier. I am using the EXTRACT scalar function to extract the hours, minutes, and seconds from the timestamp.

Line 6: MIDNIGHT_SECONDS returns the number of seconds since midnight for the current timestamp.

The results are:

00001 00002 00003 00004 -------- -------- ------------ ----- 06/08/26 21.07.16 76036.122611 76036

The third column has decimals as the timestamp has microseconds.

The fourth column is the integer value I need.

In my final example the file I will be creating the interface data. The "input" file has two columns:

1. TRANS:  Transaction id
2. DTETME:  The timestamp of when the transaction was created

In this example I want four columns in my results:

1. Transaction id
2. Date as character, in *MDY format and including the date separator
3. Time as character, with a dot as the separator character
4. Seconds as integer

My SQL statement would be:

01 SELECT TRANS AS "Trans id", 02 TO_CHAR(DTETME,'MM/DD/YY') AS "Date", 03 TO_CHAR(DTETME,'HH24.MI.SS') AS "Time", 04 MIDNIGHT_SECONDS(DTETME) AS "Seconds" 05 FROM TESTFILE 06 ORDER BY TRANS

Line 1: The transaction id.

Line 2: The formatted date.

Line 3: The formatted time.

Line 4: The number of seconds.

The results of this statement are:

Trans id Date Time Seconds -------- -------- -------- ------- 1 06/18/26 02.36.18 9378 2 06/18/26 11.45.22 42322 3 06/18/26 14.00.33 50433 4 06/18/26 18.08.06 65286

By all means the above is an over simplification of what the statement would be in the production environment. But it is enough for you to understand the function of the MIDNIGHT_SECONDS scalar function.

 

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

 

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