CODEWITHSUNDEEP
oracle-databasesumtimestamporacle10gvarchar
suhas
suhas

Reputation: 173

How to get Sum of timestamp in oracle?

Unfortunately i have stored timestamp as varchar in the database,as following format as 07:53:39 now i want sum of that timestamp which i have stored as a varchar, i have written the following query to convert varchar into timestamp by removing the colons,now i want sum of this timestamp how i can get it ,

select TO_CHAR(to_timestamp(regexp_replace(ru.duration,'(*[[:punct:]])',''),'HH:MI:SS'),'HH:MI:SS') FROM RESOURCE_UTILIZATION RU WHERE RU.RESOURCE_NAME='AKSHAY ANPAT';

the following is output of the above query :

07:53:39
03:07:02
07:18:21
05:10:44
06:14:49
07:43:51

please help me to get sum of timestamp in above query.

Upvotes: 0

Views: 7489

Answers (1)

Justin Cave
Justin Cave

Reputation: 231781

My guess is that you want to sum the interval between the timestamp and midnight on the day in question. If you want to get an interval day to second as a result

with x as (
  select '01:23:45' val from dual union all
  select '02:15:30' from dual
)
select numtodsinterval( sum( to_date( val, 'hh24:mi:ss' ) - trunc( sysdate, 'MM' ) ), 'day' )
  from x

returns the interval +000000000 03:39:15.000000000. You could also get a difference in days just by removing the numtodsinterval call

with x as (
  select '01:23:45' val from dual union all
  select '02:15:30' from dual
)
select sum( to_date( val, 'hh24:mi:ss' ) - trunc( sysdate, 'MM' ) )
  from x

Upvotes: 2

Related Questions