oracle - Time subtraction format result

Question

I'm having trouble formatting the result of my time calculation as well as searching for a forum solution. I do not wish to view the "hours from UTC" leading the result (the +09).

select
(localtimestamp - to_timestamp(us.STARTDATETIME,'hh24:mi:ss')) as HoursPassed
from random us

Where us.STARTDATETIME is a varchar2 with something like 08:00

My result:

+09 07:30:17.160826

Desired result:

07:30:17

Answer 1

When you subtract one timestamp from another the result is an internal interval data type, but you can treat it as 'interval day to second':

select
(localtimestamp - to_timestamp(us.STARTDATETIME,'hh24:mi:ss')) as HoursPassed
from random us;

HOURSPASSED        
-------------------
+08 15:26:54.293892

The '+08' (in my session time zone) is the number of days, not a UTC offset; that is the value it is because when you convert a string to a date or timestamp and only provide the time part, the date part defaults to the first day of the current month:

The default date values are determined as follows:

• The year is the current year, as returned by SYSDATE.
• The month is the current month, as returned by SYSDATE.
• The day is 01 (the first day of the month).
• The hour, minute, and second are all 0.

These default values are used in a query that requests date values where the date itself is not specified ...

So I'm really comparing:

select localtimestamp, to_timestamp(us.STARTDATETIME,'hh24:mi:ss')
from random us;

LOCALTIMESTAMP             TO_TIMESTAMP(US.STARTDATET
-------------------------- --------------------------
2017-08-09 23:26:54.293892 2017-08-01 08:00:00.000000

You can't directly format an interval, but you can extract the elements of the time and format those separately, and concatenate them.

select to_char(extract(hour from (localtimestamp
    - to_timestamp(us.STARTDATETIME, 'hh24:mi:ss'))), 'FM00')
  ||':'|| to_char(extract(minute from (localtimestamp
    - to_timestamp(us.STARTDATETIME, 'hh24:mi:ss'))), 'FM00')
  ||':'|| to_char(extract(second from (localtimestamp
    - to_timestamp(us.STARTDATETIME, 'hh24:mi:ss'))), 'FM00')
  as hourspassed
from random us;

HOURSPASSED
-----------
15:26:54

Repeatedly calculating the same interval looks a bit wasteful and hard to manage, so you can do that in an inline view or a CTE:

with cte (diff) as (
  select localtimestamp - to_timestamp(us.STARTDATETIME, 'hh24:mi:ss')
  from random us
)
select to_char(extract(hour from diff), 'FM00')
  ||':'|| to_char(extract(minute from diff), 'FM00')
  ||':'|| to_char(extract(second from diff), 'FM00')
  as hourspassed
from cte;

HOURSPASSED
-----------
15:26:54

You could also use dates instead of timestamps; subtraction then gives you the difference as a number, with whole and fractional days:

select current_date - to_date(us.STARTDATETIME, 'hh24:mi') as hourspassed
from random us;

HOURSPASSED
-----------
 8.64368056

The simplest way to format that is to add it to a known midnight time and then use to_char():

select to_char(date '1970-01-01'
  + (current_date - to_date(us.STARTDATETIME, 'hh24:mi')),
  'HH24:MI:SS') as hourspassed
from random us;

HOURSPAS
--------
15:26:54

I've stuck with current_date as the closest match to localtimestamp; you may actually want systimestamp and/or sysdate. (More on the difference here.)