Formatting of dates subtraction 1

[ddrillich]

Good Day,

We are trying to format the max - min value (is it a date?) into minutes and seconds. How can we do it? The following fails...

select to_char(to_date(max - min), 'DD:MM') from (select min(timestamp) min, max(timestamp) max from wfeventssummary where workflowid = '12232' and transitionname ^= 'null')

Regards,
Dan

 

[carp]

Here's a blunt object approach that should work. I'm sure others will have something more elegant:

SELECT floor(numseconds/60)||':'||to_char((numseconds - floor(numseconds/60) * 60),'09')
  FROM (SELECT (max(timestamp) - min(timestamp)) * 86400 AS numSeconds 
          FROM wfeventssummary 
         WHERE workflowid = '12232' AND transitionname ^= 'null')

 

[ddrillich]

Thank you carp.

We did get an error for this query -

ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND

Regards,
Dan

 

[ddrillich]

The following does work -

select x * 84600 from (
 SELECT ( max(cast (timestamp as DATE)) - min(cast (timestamp as DATE)  )  )  as x from wfeventssummary WHERE workflowid = '12232' AND transitionname ^= 'null'
 )

Regards,
Dan

 

[ddrillich]

A bit better -

 SELECT floor(numseconds/60) || ':' || to_char( (numseconds - floor(numseconds/60)*60),'09') from (
 select x * 84600 as numseconds from (
 SELECT ( max(cast (timestamp as DATE)) - min(cast (timestamp as DATE)  )  )  as x from wfeventssummary WHERE workflowid = '12232' AND transitionname ^= 'null'
 )
 )

Regards,
Dan

 

[carp]

Sorry - you are using timestamps and I am using dates. I missed that. Try this:

SELECT (extract(day from delta)*1440 + extract(hour from delta)*60 + extract(minute from delta))||':'||to_char(round(extract(second from delta)),'09') 
  FROM (SELECT (max(timestamp) - min(timestamp)) AS delta 
          FROM wfeventssummary 
         WHERE workflowid = '12232' 
           AND transitionname ^= 'null')

You may want to use FLOOR or CEIL instead of ROUND to handle the seconds fraction.

 

[ddrillich]

Thank you Carp - this one worked!!

Regards,
Dan

 

[carp]

Glad to hear it. The first one will work on DATE datatypes (in case you ever want to do the same thing with a different type).