0

I am stucking by calculating the timestamp,how can i calculate/sum on different timestamp, example as below

Time (Timestamp in one column)
***********************
0:00:01
0:00:08
0:00:12
0:00:38
0:01:04
2:04:49
15:48:38
23:30:59
7:05:52
8:17:29

I want to show the sum(timestamp) in hh:mi.Any help?

Roshan
  • 47
  • 2
  • 10
  • do you have date along with time stamp or just only time stamp?? – smn_onrocks Feb 21 '14 at 09:54
  • Hi smn, having only timestamps – Roshan Feb 21 '14 at 09:57
  • http://stackoverflow.com/questions/14874811/sum-hours-from-timestamp – Nagaraj S Feb 21 '14 at 09:59
  • thank you providing the details I tried what is given But the database is db2. – Roshan Feb 21 '14 at 10:12
  • Hi Muse, adding to your comment data type varchargraphic which can convert it to (SUM(RTRIM(hour(TK.STATUSTRACKING)))+ ( SUM(RTRIM((minute(TK.STATUSTRACKING)))))/60) but still i am facing some error, i will if it is solve – Roshan Feb 21 '14 at 13:03
  • 1
    Well, what have you tried? There are functions `HOURS(...)`, etc.... Given your sample data, what should the result be? Note that you can't use `TIME` as the output type, as that's strictly time-of-day (stops at 24 hours, essentially). – Clockwork-Muse Feb 21 '14 at 13:03

2 Answers2

0

i can tel you in postgresql you can do like

postgres=# select time '05:00' - time '03:00';
 ?column? 
----------
 02:00:00
(1 row)

dont know how much this can help you

smn_onrocks
  • 1,282
  • 1
  • 18
  • 33
0

This example works on DB2 when the values being added are TIME columns (as shown in original question). The trick is to convert the times to elapsed seconds, which are much easier to add up.

WITH origTbl (origTime) AS (VALUES
( TIME( '0:00:01' ) ),
( TIME( '0:00:08' ) ),
( TIME( '0:00:12' ) ),
( TIME( '0:00:38' ) ),
( TIME( '0:01:04' ) ),
( TIME( '2:04:49' ) ),
( TIME( '15:48:38' ) ),
( TIME( '23:30:59' ) ),
( TIME( '7:05:52' ) ),
( TIME( '8:17:29' ) )
)
,
totSeconds( secs ) AS (
SELECT SUM( MIDNIGHT_SECONDS( origTime ) ) 
FROM origTbl
)
SELECT RTRIM( CHAR( secs/3600 ) ) || ':' || 
LPAD( RTRIM( CHAR( MOD(secs, 3600)/60 ) ), 2, '0' ) || ':' ||
LPAD( RTRIM( CHAR( MOD(secs, 60) ) ), 2, '0' ) AS totHMMSS FROM totSeconds
;

TOTHMMSS
-----------------
56:49:50

  1 record(s) selected.
Fred Sobotka
  • 5,252
  • 22
  • 32
  • Thanks Fred for your response,but as above data which used as sample if Varchargraphic datatype column inserting/holds only HH:MI:SS format data, I need to calculate the total column data,this column holds status wise timestamp data, hope you get it – Roshan Feb 26 '14 at 05:04