Guru: DateTime Rules Of Thumb

May 18, 2026 Ted Holt

I am not a great programmer. I am a decent programmer who has found ways to stay out of trouble. I use many little rules of thumb to keep me and the people I serve from being unpleasantly surprised at inopportune moments. Today I share rules of thumb regarding datetime data that have served me in my work.

The first date field I used in an RPG program was a six-digit zoned-decimal value in MMDDYY format. This was part of a programming assignment for a class I was taking at the local vocational-technical center in days of yore. In my nascent understanding of Data Processing, this is how dates were stored in the file system.

After a few years on the job I tired of the problems this date format continually presented me, especially when using the #GSORT system utility to select and sort records from S/36 files. I wised up and started storing dates in YYMMDD format. (Y2K was still several years into the future.)

In the decades since, I have seen datetime data stored in many formats, but as far as I’m concerned, the only proper ways to store transactional datetime data these days is in DATE and TIMESTAMP columns of tables defined with SQL.

“What about the TIME data type?” I hear you asking. The only proper application for standalone TIME columns of which I’m aware is in a recurring schedule, such as a timetable for trains or flights. I’m sure there are other uses. I don’t think I’ve ever defined a TIME column in a table definition for production work.

If a time is associated with a date, I recommend a TIMESTAMP column, not separate DATE and TIME columns. It makes sense to me to define a single TIMESTAMP column because date and time together represent a single point in history, but it also facilitates row selection

For instance, suppose you want to select all rows from 6:00 PM of one day through 6:00 AM of the next day. If date and time are in separate columns, you have to use the TIMESTAMP function to combine them, like this:

select * from SomeTable as t
 where timestamp(t.DateCreated, t.TimeCreated) 
 between '2026-07-04-18.00.00' and '2026-07-05-06.00.00';

I admire TIMESTAMP’s ability to combine a date and time, an ability I wish RPG’s %TIMESTAMP function had. However, I avoid placing a function on the left side of a row selection expression, as it often degrades performance. If DateCreated and TimeCreated were one WhenCreated column, I wouldn’t need the TIMESTAMP function.

select * from SomeTable as t
 where t.WhenCreated
 between '2026-07-04-18.00.00' and '2026-07-05-06.00.00';

So we’re left with the DATE and TIMESTAMP data types for most applications. DATE usually suffices. Doctors, taxing authorities, banks and other usurious financial institutions, my kids, and cyber-terrorists the world over all know the date of my birth, but no one has ever asked me what time I was born. When time is irrelevant, I recommend using a DATE column.

That leaves TIMESTAMP. By default, I recommend TIMESTAMP(0), as fractions of a second are not usually required. TIMESTAMP without an argument defines a column with 6 fractional positions (microseconds). If you need them, you can define up to 12 fractional positions.

Fractions of a second are good if you need them, but they can mess up a query when they’re there only because someone didn’t have the foresight to leave them off. Assume this data set:

Key   WhenCreated
---   --------------------------
1     2026-07-04 08:00:00.123456
2     2026-07-04 08:00:35.222446
3     2026-07-04 08:01:10.306599
4     2026-07-04 08:23:59.000000
5     2026-07-04 08:23:59.227751
6     2026-07-04 08:23:59.999999

Which rows get returned by the following query?

select * from SomeTable
 where WhenCreated 
  between '2026-07-04-08.01.00' and '2026-07-04-08.23.59'

The answer, of course, is that the query returns rows 3 and 4. The microseconds eliminated rows 5 and 6. Chances of this type of query returning an inaccurate result set are slim, but I do my best not to leave anything to chance.

If you’re stuck querying a timestamp with fractions of a second, you have at least two workarounds. You can include fractions of seconds in the right side of the expression, like this:

select * from SomeTable
 where WhenCreated
  between '2026-07-04-08.01.00.000000'
      and '2026-07-04-08.23.59.999999';

Or you can use the Truncate timestamp (TRUNC_TIMESTAMP ) function on the left side of the expression.

select * from SomeTable
 where trunc_timestamp(WhenCreated, 'SS')
  between '2026-07-04-08.01.00'
      and '2026-07-04-08.23.59';

Yes, I put a function on the left side of the expression. I avoid it, but I do it when I have to.

TRUNC_TIMESTAMP can be used to select to any degree of precision. The previous example truncated everything past seconds (“SS”). Here’s one way to get all orders for a month, in this case July of 2026.

select * from qtemp.temp01
 where trunc_timestamp(WhenCreated, 'MM') = '2026-07-01';

TRUNC_TIMESTAMP sets everything past the degree of precision to low values, which means 01 for month and day, and zeros for all portions of the time, including fractions of a second. I did not have to specify the time portion of the datetime literal value because the default of the time portion of a timestamp is all zeros.

A similar function, ROUND_TIMESTAMP, rounds to the nearest units according to the precision required. For instance, rounding to the minute treats seconds values of 30 or more as part of the next minute.

If needed, you can use other functions on the left side of the row-selection expression.

select . . . where date(WhenCreated) = '2026-07-04';
select . . . where time(WhenCreated) = '08.00.00';
select . . . where year(WhenCreated) = 2026;
select . . . where month(WhenCreated) = 7;
select . . . where day(WhenCreated) = 5;
select . . . where hour(WhenCreated) = 12;
select . . . where minute(WhenCreated) = 23;
select . . . where second(WhenCreated) = 59;
select . . . where microsecond(WhenCreated) = 227751;

I might summarize these rules of thumb thus:

1: Use SQL, not DDS, to define physical files.

2: Use DATE if time is irrelevant.

3: Use one TIMESTAMP column, not separate DATE and TIME columns, for a single point in time.

4: Define timestamps as TIMESTAMP(0) by default. Use fractions of seconds only when required.

I still work with database files that store dates and times in alpha and numeric fields, but my preference by far is to use the datetime data types, mainly because the system rejects invalid date and time values. Strong data types promote database integrity.

Ted Holt is the original, the one and only, chief of the Four Hundred Gurus. We are glad he is back with us writing technical material that helps IBM i programmers. He is a self-employed, independent programmer living near Tupelo, Mississippi, who is old enough to retire but is not ready to do so. He still enjoys programming and is available to help others as needed. He welcomes your comments, questions, and suggestions.

This Issue Sponsored By

Table of Contents

Content archive

Recent Posts

Subscribe

Pages

Search