Search code examples
sqldatetimestampdb2mathematical-optimization

DB2 SQL Time Math decimal precision not visible

This question is mostly out of frustration over something that seems so simple and I can't get to work...

Our ERP system (Infor XA) stores date and time like this 1200203145125 which is simply CYYMMDDHHMMSS. The century byte is 0 = 19 and 1 = 20. So "120" (CYY) = the year 2020. The column is numeric. For workers that swipe onto a job, I need to measure the elapsed time between their swipe-on time and the time right now. So let's say a worker swiped-on today at 5:31 am, the timestamp might look like this: 1200203053116 (and 16 seconds). And I'm using CURRENT_TIMESTAMP for the "right now" time.

So far, trying to subtract to get the difference, I've been swimming in an ocean of VARCHAR_FORMAT(), CAST(), and data type mismatch errors for most of the day. The closest I've gotten so far, is to convert both times - the swipe time, and the CURRENT_TIMESTAMP with midnight_seconds() reducing both times to numerical seconds since midnight, and then subtracting the two. That works, returning the difference between the two times in seconds. (btw, I trimmed the lead-in century byte from the swipe date TRNDT)

--difference in hours (now)
     (midnight_seconds(CURRENT_TIMESTAMP)) - (midnight_seconds('20' || SUBSTR(ALLMOTRAN.TRNDT,2,2) || '-' || 
     SUBSTR(ALLMOTRAN.TRNDT,4,2) || '-' || SUBSTR(ALLMOTRAN.TRNDT,6,2) || ' ' || SUBSTR(ALLMOTRAN.TTIME,1,2) || ':' || 
     SUBSTR(ALLMOTRAN.TTIME,3,2) || ':' || SUBSTR(ALLMOTRAN.TTIME,5,2))) AS UNPOSTHRS

So... great seems easy enough. But I need my answer in decimal hours. When I try to divide the above by 3600 I only get an integer answer.

--difference in hours (now)
     (midnight_seconds(CURRENT_TIMESTAMP)) - (midnight_seconds('20' || SUBSTR(ALLMOTRAN.TRNDT,2,2) || '-' || 
     SUBSTR(ALLMOTRAN.TRNDT,4,2) || '-' || SUBSTR(ALLMOTRAN.TRNDT,6,2) || ' ' || SUBSTR(ALLMOTRAN.TTIME,1,2) || ':' || 
     SUBSTR(ALLMOTRAN.TTIME,3,2) || ':' || SUBSTR(ALLMOTRAN.TTIME,5,2)))/3600 AS UNPOSTHRS

I tried a few CAST(xxxxx AS DECIMAL(4,2)) type of things but that's not working either. Any suggestions would be great (even doing this a totally different way than I've been trying). Sadly, I wish this was a task on our SQL Server box instead of our ERP system. I could have been done with this in 5 minutes.

Thanks!

Solution

  • The problem that you are seeing is called integer division. A fair amount of databases, when given two integer number to divide, return an integer number that represents the integer part of the result.

    You can work around the issue by forcing decimal context with another operation:

    1.0 * (your big expression) / 3600

    Or better yet:

    (your big expression) / 3600.0