Convert SQL number in time to perform date mappings

Question

I am struggling with comparing SQL time on two fields that were configured as integers, not on timestamps.

I have some performance indicators in the database that the developer initially configured as int (8). The database contains the start and end times of the transaction. for instance

Some example data may be

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

If I just read the two values, I would get 4043 seconds when the transaction time was only 3. I struggle, however, to convert the values ​​to a time format that allows me to perform date comparisons.

I cannot do this calculation inside the application because there are 100 rows per day in the database and I am trying to calculate the average and maximum time for transactions.

EDIT:

To clarify

Time in seconds 85958 represents 8:59:58 90001 represents 9:00:01

To make things worse, 100 minutes will be submitted 1 minute before midnight 0:01:00.

Answer 1

Most of the answers already described are all valid, but unfortunately the iSeries seems to discuss different functions, so I had to adapt the answers to make it work.

The final decision I received 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 answers.

Answer 2

I tested this in MySQL, but I'm sure this method 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 modulo operations to extract the HH MM and SS parts of each timestamp and convert each part into seconds. Then the seconds are added together to form the total number of seconds since midnight for each timestamp. The difference between the two gives transaction time.

Please note that this will not work if the transaction starts before midnight and ends after midnight. You may need to consider whether the day has changed and whether it will fix it. If you do not have a day stored in your database, you can look for the time of the negative transmission and add 24 hours to make them positive, and this should give the correct result (if the transactions do not exceed one day in length, but it is probably unlikely to practice).

My attempt to write this for DB2 (not verified):

 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 3

Assuming you are using DB2 for LUW, you can do this using several functions:

• DIGITS () - gives you a zero character string of your integer
• TRANSLATE () - reformat a character string
• MIDNIGHT_SECONDS - Returns the number of seconds from midnight to time.

This will work in cases where the value is 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 before midnight, but end_time after midnight).

Of course, the real problem is using INT to store TIME. It is best to fix your data model so that it uses TIME (or better, TIMESTAMP).

Answer 4

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

Answer 5

I would suggest that the values ​​are actually for 08:59:58 and 09:00:01 in hours: minutes: seconds, assuming the difference at the end is 3

but all these are guesses that you need to ask others in your company what they are - how someone else should have used them, even if the original encoder left