Searching for pairs of date/time values in a single row

Question

I have a legacy IBM DB2 database table that contains lots of records of phone calls. It has columns for ID, customer number, employee who took the call, etc. The date/time for each call is kept in a pair of columns, ROSDAT and ROSTIM. ROSDAT is an 8 byte numeric column, and ROSTIM is a 6 byte numeric. Example:

ROSDAT    ROSTIM  
20111006  163243  
20111007  012335

So for the first row the date is 2011-10-06, and the time is 16:32:43. There are millions of records in this table, and I'm trying to make a query that will search it by time and date. The only way I've found to do that so far is to concatenate the time/date columns into one big number like so:

select * from PHONELOGTABLE
where ROSDAT * power(10, 6) + ROSTIM >= 20111015124500
and ROSDAT * power(10, 6) + ROSTIM <= 20111116013000

This searches for everything from 2011-10-15 12:45:00 to 2012-11-16 01:30:00. It works, but the concatenation means (as far as I know) that the indexes on the ROSDAT and ROSTIM columns cannot be used, so the query is slower than it should be. It seems to me that there should be a way to do this with a join or something.

I tried this but couldn't get it to work, and I know I'm missing something:

select a.* from PHONELOGTABLE a join PHONELOGTABLE b on a.ID = b.ID where a.ROSDAT >= 20111015 and b.ROSTIM >= 124500
and a.ROSDAT <= 20111116 and b.ROSTIM <= 013000

Anybody have an idea?

Answer 1

I'm not sure of db2 syntax, but how about this?

-- declare your 4 variables
@START_ROSDAT
@STOP_ROSDAT
@START_ROSTIM
@STOP_ROSTIM

select  *
from    PHONELOGTABLE
where   (   @start_rosdat <> @stop_rosdat                                    -- search covers more than 1 day
           and  (       (ROSDAT > @START_ROSDAT AND ROSDAT < @STOP_ROSDAT)   -- catches all full days between start and stop
                   or   (ROSDAT = @START_ROSDAT AND ROSTIM >= @START_ROSTIM) -- catches everything on the "start" day
                   or   (ROSDAT = @STOP_ROSDAT AND ROSTIM <= @STOP_ROSTIM)   -- catches everything on the "stop" day
                )
        )
  or    (   @start_rosdat = @stop_rosdat                                     -- only search a single "day"
           and  (       (ROSDAT = @START_ROSDAT AND ROSTIM >= @START_ROSTIM)
                   and  (ROSDAT = @STOP_ROSDAT AND ROSTIM <= @STOP_ROSTIM)
                )
        )

---

WHERE condition simplified a bit (the parentheses are there only for clarity, you can safely remove them):

where   ( ROSDAT > @start_rosdat   and  ROSDAT < @stop_rosdat )

   or   (  @start_rosdat < @stop_rosdat 
      and  ROSDAT = @start_rosdat  and  ROSTIM >= @start_rostim )

   or   (  @start_rosdat < @stop_rosdat 
      and  ROSDAT = @stop_rosdat   and  ROSTIM <= @stop_rostim  )

   or   (  @start_rosdat = @stop_rosdat 
      and  ROSDAT = @start_rosdat  and  ROSTIM >= @start_rostim
                                   and  ROSTIM <= @stop_rostim  )

---

and another version (parentheses are needed here):

where   (  ROSDAT > @start_rosdat  
       or  ROSDAT = @start_rosdat  and  ROSTIM >= @start_rostim )

   and  (  ROSDAT < @stop_rosdat
       or  ROSDAT = @stop_rosdat   and  ROSTIM <= @stop_rostim  )

Answer 2

I see you already have an answer, and I see that you can't change the table definition because of other programs that use the table, but can you add in new columns to the table? Are you also on DB2 for Linux/Unix/Windows (tested on 9.7)?

If you can, and you are, then I might suggest you create a GENERATED COLUMN to create a TIMESTAMP column, which you could then index for searching.

SET INTEGRITY FOR PHONELOGTABLE OFF;

ALTER TABLE PHONELOGTABLE ADD COLUMN
    tstamp TIMESTAMP GENERATED ALWAYS AS (
        TIMESTAMP(
            LPAD(dt, 8, '0') ||
            LPAD(tm, 6, '0')
        )
    );

SET INTEGRITY FOR PHONELOGTABLE IMMEDIATE CHECKED FORCE GENERATED;

CREATE INDEX PHONELOGTABLE_TSTAMP ON PHONELOGTABLE  (
    TSTAMP
);

The LPAD function will ensure that you'll always have at least 14 characters (it implicitly casts the first argument to VARCHAR, and then pads on the left with the last argument to at least the number of characters specified in the second arg), which the TIMESTAMP function can then cast to a TIMESTAMP.

This won't work if you're on the Mainframe DB2 (the only type of generated columns DB2 for z/OS supports is identity columns, row change timestamps, or rowids). I'm not sure about DB2 for iSeries.