0

I need to convert a string value containing time in HH:MM:SS format to timestamp and then add those timestamps grouped by ticketid. I have tried TIME functions but they are not helping much.

Current results:

    TICKETID               WORKTIMETRACKING


       5141                  02:55:00
       5141                  00:00:00
       7856                  01:12:55
       7857                  00:07:00
       7857                  00:01:00

Desired results:

    TICKETID               WORKTIMETRACKING


       5141                  02:55:00
       7856                  01:12:55
       7857                  00:08:00

In current results we have same ticketids with different worktimetraking but I need to extract results by summing the time(in timestampformat) as shown in desired results (ticket:7857 worktime adds up to 00:08:00)

Sarshad
  • 29
  • 4
  • Even if you convert your character values to `TIME`, you cannot add one `TIME` value to another; you can only add a _duration_ (or interval) to `TIME`. Since what you're dealing with are duration values, that's what you need to work with. – mustaccio Aug 27 '19 at 16:10
  • is there a way to add time durations in db2? – Sarshad Aug 27 '19 at 16:12
  • Hi please see [sqlserver_dateadd](https://www.w3schools.com/sql/func_sqlserver_dateadd.asp) – Jephren Naicker Aug 27 '19 at 17:48
  • Possible duplicate of [SQL query - SUM duration values (hh:mm:ss) from ALN field](https://stackoverflow.com/questions/48746132/sql-query-sum-duration-values-hhmmss-from-aln-field) – Clockwork-Muse Aug 27 '19 at 20:50

1 Answers1

1

We cannot add two TIME values, i.e.

db2 "values time('00:01:59') + time ('00:00:02')"
SQL0402N  The data type of an operand of an arithmetic function or operation 
"+" is invalid.  SQLSTATE=42819

So the easiest approach would be to use MIDNIGHT_SECONDS scalar function.

For your input

db2 "create table tickets(ticketid int, worktimetracking varchar(10))"
db2 "insert into tickets values (5141, '02:55:00')"
db2 "insert into tickets values (5141, '00:00:00')"
db2 "insert into tickets values (7856, '01:12:55')"
db2 "insert into tickets values (7857, '00:07:00')"
db2 "insert into tickets values (7857, '00:01:00')"

you can try something like this:

db2 "select 
       ticketid, 
       time('00:00:00') + sum(midnight_seconds(time(worktimetracking))) seconds as total_time 
     from 
       tickets 
     group 
       by ticketid"

TICKETID    TOTAL_TIME
----------- ----------
       5141 02:55:00  
       7856 01:12:55  
       7857 00:08:00  

  3 record(s) selected.

This would to as long as it is never more then 24 hours, otherwise you need a different type.

kkuduk
  • 591
  • 2
  • 6
  • This is clever. – mustaccio Aug 28 '19 at 01:22
  • Hi @kkuduk. Thanks alot for the answer. I'll try this for sure. But worktimetracking will have more than 24 hours. What type would be used to cater that? – Sarshad Aug 28 '19 at 05:16
  • @Sarshad you can of course leave it it seconds. What you would like to see on the screen with e.g. 100 hours? "100:00:00"? Or something of a "4 days 4 hours" kind? – kkuduk Aug 28 '19 at 07:20
  • Also, your input data is in HH:MM:SS format. Does it ever exceed 24 hours for a single entry? Or only after aggregation? – kkuduk Aug 28 '19 at 07:48