Converting SQL number into a time, to perform date comparisons.

Question

I am struggling with an SQL time comparison on two fields that have been set up as integers, rather than time stamps.

I have some performance metrics in a database that the developer originally set up as int(8). The database contains the start and end times of a transaction. For example

Some sample data may be

id | start_time | end_time
---------------------------
1  |      85958 | 90001 

If I simply subtracted the two values, I would get 4043 seconds, when the transaction time is only 3. I am struggling however to convert the values into a time format that allows me to perform date comparisons.

I am unable to do this calculation within the application, because there are 100's of rows in the database per day, and I am trying to calculate the average and max times for the transactions.

EDIT:

To clarify

The times are in seconds 85958 represent 8:59:58 90001 represents 9:00:01

To make things worse, 1 minute past midnight 0:01:00 would be represented as 100.

Answer 1

Are you sure you need to convert? Perhaps the number represents milliseconds, so the difference would be ~4 seconds.

Answer 2

I would suggest the values are actually meant to be 08:59:58 and 09:00:01 in hours:minutes:seconds by guessing the end difference is 3

but these are all guesses you need to ask others in your company what they are - as someone else must have used these even if the original coder had left

Answer 3

I tested this in MySQL but I'm sure the technique can be adapted to work in DB2:

SELECT
    (end_time DIV 10000) * 3600 +
    ((end_time DIV 100) % 100) * 60 +
    end_time % 100 -
    (start_time DIV 10000) * 3600 -
    ((start_time DIV 100) % 100) * 60 -
    start_time % 100
FROM table1

Result:

3

The way it works is to use integer division and the modulo operation to extract the HH MM and SS parts of each timestamp and convert each part into seconds. The seconds are then added together to form a total number of seconds since midnight for each timestamp. The difference between these two gives the transaction time.

Note that this won't work if the transaction starts before midnight and finishes after midnight. You may need to consider if the day has changed and correct for this. If you don't have the day stored in your database then you can look for negative transation times and add 24 hours to make them positive and this should give the correct result (as long as transactions don't exceed one day in length, but I guess that this is unlikely in practice).

My attempt at writing this for DB2 (not tested):

SELECT
    (end_time / 10000) * 3600 +
    MOD(end_time / 100, 100) * 60 +
    MOD(end_time, 100) -
    (start_time / 10000) * 3600 -
    MOD(start_time / 100, 100) * 60 -
    MOD(start_time, 100)
FROM table1

Answer 4

Assuming you're using DB2 for LUW, you can do this using a few functions:

• DIGITS() – gives you a zero-padded character string of your integer
• TRANSLATE() - reformat the character string
• MIDNIGHT_SECONDS - return the number of seconds since midnight for a time.

This will work in cases were the value 100 = '00:01:00'.

Example:

select
   id,
   MIDNIGHT_SECONDS(TRANSLATE('EF:GH:IJ',DIGITS(end_time),'ABCDEFGHIJ')) -
      MIDNIGHT_SECONDS(TRANSLATE('EF:GH:IJ',DIGITS(start_time),'ABCDEFGHIJ')) as runtime
from
   your_table;

The above expression will not work if start_time > end_time (i.e., start_time is before midnight, but end_time is after midnight).

Of course, the real problem here is using an INT to store a TIME. It would be better to just fix your data model so it uses TIME (or better, a TIMESTAMP).

Answer 5

Most of the answers already described are all valid, but unfortunately the iSeries seems to quibble over different functions and so I had to tailor the answers given to get it to work.

The final solution I got was

select TIME(SUBSTR(DIGITS(END_TIME),1,2) CONCAT ':' CONCAT SUBSTR(DIGITS(END_TIME),3,2) CONCAT ':' CONCAT SUBSTR(DIGITS(END_TIME),5,2)) - TIME(SUBSTR(DIGITS(START_TIME),1,2) CONCAT ':' CONCAT SUBSTR(DIGITS(START_TIME),3,2) CONCAT ':' CONCAT SUBSTR(DIGITS(START_TIME),5,2)) from table1;

Thanks for all the quick and detailed responses