Date, time, and timestamp data types are often overlooked and underused in AS/400 information systems. This article, illustrated with practical examples, gives you a glimpse of the power available to you by using these data types (instead of numeric and character fields) to store time. You’ll learn how to do date math, how to handle durations of time, and how to circumvent some limitations of SQL/400.

The date, time, and timestamp data types and scalar functions are probably the most overlooked and underused functions in DB2/400. That’s too bad. There’s so much you can do with them that you can’t do with numeric and character date fields. You can add them, subtract them, and generally abuse them to your heart’s content and the benefit of your organization. In this article, I’ll show you some neat tricks that you can put to work in your environment.

The Nitty-gritty

The AS/400 has scalar functions that accept dates or date expressions as arguments. A scalar function is an SQL function that can be used in an SQL query anywhere that an expression can be used. Month is an example of a scalar function. Month returns the integer month portion of a date or timestamp column or expression. (Complete documentation of both the AS/400 and ODBC scalar functions can be found on my Web site, www.sqlthing.com/ODBCInfo.htm.

Your First Date

For the purposes of the following example queries, assume that you have a table (file) called WEBHITS that has the columns (fields) WEBPAGE and TMSTP. Each time a page is hit on your Web server, an entry recording the page and current timestamp is made. On your first date, management has decided that they want to know the number of page hits by day of the week. To get the data will necessitate using the DayofWeek scalar function.

The DayofWeek function takes a date or timestamp expression and returns a number between 1 and 7 in which 1 represents Sunday and 7 represents Saturday. This can be a useful method for indicating the day of week to a report user, but then you have to train the users that 1 represents Sunday, 2 represents Monday, and so on. By combining the DayofWeek function with the Substring function, you can return a string representing

the day of the week. The query illustrated in Figure 1 would return the page name and day of week from the WEBHITS table.

Now, I’ll dissect the function. The statement reads a record that has a date of 01/01/1980, which was a Tuesday, so the DayofWeek function returns a 3. Since 3*3 = 9, the Substring function starts at the ninth character and returns the next three characters. This results in the string TUE, and (magic!) you can now read the table, summarize the results, and create management’s report.

The above example is satisfying in and of itself, but I am extremely lazy. I don’t want to read all of those records and make my own summarization. To get SQL to summarize this for me, I need the Group By clause.

Unfortunately, the AS/400 will not allow you to use Group By with expressions or derived columns. It will allow you to group by scalar functions if you hide them in a view. (DB/2 Universal allows grouping by derived columns. Perhaps someday this ability will exist in DB2/400. To group by a scalar, I will create a view over the WEBHITS table, as shown in Figure 2.

The select statement uses the Month scalar (which returns the integer month number from a date or timestamp expression) and the trusty DayofWeek function to make a view called WEBTEMP. WEBTEMP contains one record for each record in the WEBHITS table and has the columns PAGE, MN, and DOW. I can now fool the AS/400 with the statement shown in Figure 3, which produces the information shown in Figure 4.

Of course, this shows a problem with the day-of-week dodge: It doesn’t sort correctly! Humans want to see MON, TUE, WED, etc. To correct this, I can change the day-of-week formula to the following:

SUBSTRING (‘XXXX1-SUN2-MON3-TUE4-WED5-THR6-FRI7-SAT’, DAYOFWEEK(TMSTP) *5, 5)

The days of the week will be returned as 1-SUN, 2-MON, 3-TUE, and so on. Even though this may not be as pleasing as MON, TUE, WED, at least it’s in order.

Temporal Increments (Dr. Who?)

Once you open Pandora’s date box, the powers that be will come up with an inexhaustible set of reports that are temporally challenged. Suppose, for example, that you’re asked to determine the number of page hits per quarter hour. First, you need to create a view over the WEBHITS table, as shown in Figure 5. The view should include time of day by the quarter hour.

Converting a time of day to an exact quarter hour requires the Substring, Digits, Integer, Hour, and Minute scalar functions. Hour returns the hour part of a timestamp, and Minute returns the minute portion. Integer forces a decimal result to be truncated. If the value of TMSTP is 01/01/1980 08:17:30.000099, the formula performs the steps shown in Figure 6.

I can now group by the HRINC column, as the query in Figure 7 does, to report on activity by 15-minute increments. I get a result like that shown in Figure 8.

Date Math!

Did you know that SQL has built-in support for date math? You can use the Days, Weeks, Months, Years, Hours, Minutes, Seconds, or Microseconds keywords to add or subtract periods of time from date, time, and timestamp type columns. The keywords can be used anywhere in an SQL statement that it is legal to use an expression.

Here is a practical use of the Day keyword. Using the WEBHITS example table, the query in Figure 9 would find pages hit within 60 days of the current date (when the query executes).

What’s a Duration?

Dates, times, and timestamps can also be added and subtracted from each other. The result of such an operation is something truly awful called duration. Duration represents the temporal difference in years, months, days, hours, minutes, seconds, and

microseconds. IMHO, the operations should result in a floating-point number in which the left-hand portion is the number of days’ difference and the right-hand portion is the number of microseconds’ difference. In this manner, you are open to all kinds of neat date and time tricks. However, one takes what one gets, and durations still allow for a lot of stupid date tricks. Before I play any more tricks with durations, however, let me show you how they are returned.

If the operands are both dates, the duration returned is a date duration of decimal (8,0). The first 4 bytes represent the number of years between the dates, the next 2 bytes represent the number of months between dates, and the last 2 bytes represent the number of days between the operands.

Time durations occur only when both operands are time type columns. A time duration is a decimal (6,0) number in which the first 2 bytes represent hours, the next 2 bytes represent minuets, and the last 2 bytes represent seconds.

A timestamp duration is a decimal (20,6) number, which is the combination of a date duration concatenated with a time duration. The six digits to the right of the decimal place represent the number of microseconds between the durations.

OK, so now you know all about the formatting of durations: What does this knowledge buy you? Not much, but knowledge for its own sake is a good thing. If you absolutely must add or subtract dates and times from each other, at least you now know what the AS/400 is doing with the information.

Here is a trick I apply to calculate the computing minutes that an employee used yesterday. Our employees enter records, and each record has a timestamp column called DTEIN; the employee’s initials are in the column OPERNO. The query (see Figure 10) uses the max and min functions to get the first and last timestamp seen for each employee. When min(dtein) is subtracted from max(dtein), the result is a timestamp duration. I use the Hour, Minute, and Second scalar functions to retrieve the relevant parts of the duration for manipulation. The query returns a table like the one shown in Figure 11. Here is a vivisection of the formula:

At the outset, Second( max(dtein) - Min(dtein)) returns the number of the seconds part of the timestamp duration. Seconds are then divided by (60*1.00), which is a dodge to get a decimal number. The Seconds function returns an integer, and 60 is an integer. Therefore, you must force the number 60 to be cast as a floating-point number, hence the 60*1.00, or the AS/400 will perform integer arithmetic and truncate any remainder. If the remainder is truncated, you lose your decimal seconds.

Next, the Minute function is used to return the minute duration between the minimum and maximum DTEIN values.

Finally, the Hour function is used to return the hours duration and multiplies the duration by 60 to achieve number of minutes. Adding the minutes together with the seconds yields the minutes and seconds of computer time that our operator used.

That’s Not All, Folks...

I hope that you have gained a new appreciation for the DB2/400 date, time, and timestamp data types from this little exploration. Be sure to check out DB2 for AS/400 SQL Programming or my Web page for more information on the other date and timestamp scalar functions. Remember that dates can be fun!

References

DB2 for AS/400 SQL Programming (SC41-5611, CD-ROM QB3AQ801) DB2 for AS/400 SQL Reference (SC41-5612, CD-ROM QB3AQ901)

Select WEBPAGE,

substring('XXSUNMONTUEWEDTHRFRISAT', dayofweek(TMSTP)*3, 3)

from SQLEXAMP.WEBHITS

Figure 1: Converting a number to the abbreviation of a day of week

CREATE VIEW SQLEXAMP.WEBTEMP (PAGE, MN, DOW) as

Select WEBPAGE, Month(TMSTP),

SUBSTRING('XXSUNMONTUEWEDTHRFRISAT', DAYOFWEEK(TMSTP) * 3, 3)

From SQLEXAMP.WEBHITS

Figure 2: Creating a view allows you to group by scalar functions

Select PAGE, MN, DOW, COUNT(*)

from SQLEXAMP.WEBTEMP

GROUP BY PAGE, MN, DOW

Figure 3: Grouping by a view

WEBPAGE Month Day of Week Hits

INDEX 1 FRI 111

INDEX 1 TUE 563

INDEX 1 WED 654

INDEX 2 THR 421

INDEX 2 WED 221

Figure 4: Query Results using the DayofWeek and Month functions

CREATE VIEW SQLEXAMP.WEBTEMPA (PAGE, MN, DOW, HRINC) as

Select WEBPAGE, Month(TMSTP),

substring('XXSUNMONTUEWEDTHRFRISAT', DAYOFWEEK(TMSTP) * 3, 3) ,

substring(digits((hour(TMSTP)*100) + (integer(minute(TMSTP)/15)*15)),7,4)
from SQLEXAMP.WEBHITS

Figure 5: Creating a view is sometimes necessary for building complex queries

hour(TMSTP)*100 = 800
integer(minute(TMSTP)/15)*15 = 15
800+15 = 815

Digits(815) = '0000000815'
substring('0000000815',7,4) = '0815'

Figure 6: Converting a time of day to an exact quarter hour

SELECT PAGE, HRINC, COUNT(*)

from SQLEXAMP.WEBTEMPA

group by PAGE,HRINC

order by PAGE,HRINC

Figure 7: Reporting Web hits in 15-minute intervals

PAGE HRINC COUNT(*)

INDEX 0815 637 INDEX 0830 921 INDEX 0845 910 INDEX 0900 54

Figure 8: Breaking a timestamp column into day parts

Select WEBPAGE, COUNT(*)

from EXAMPSQL.WEBHITS

where TMSTP BETWEEN CURRENT_TIMESTAMP - 60 DAYS

AND CURRENT_TIMESTAMP

GROUP BY WEBPAGE

Figure 9: SQL makes easy work of date and time arithmetic

SELECT OPERNO,
( (second ( max(dtein)- min(dtein) ) ) / (60*1.00 ) )
+ Minute( max(dtein)- min(dtein) )
+ (hour( max(dtein) - min(dtein) ) * 60) as T from SQLEXAMP.EMPRECS
where DATE(DTEIN)=CURRENT_DATE - 1 DAY
Group by OPERNO

Figure 10: Making sense of timestamp durations

OPERNO Minutes

ANN 169.45 BILL 142.21 TIM 131.55

Figure 11: Timestamp durations have been converted to minutes

Also Read

SQL 101: DDL Recap—From Physical Files to SQL Tables, Starting the Journey

TechTip: Generate and Email a Spreadsheet with SQL

Practical SQL: Journal Forensics Using SQL