how to add dates with keep their formats?

Question

I have a start_time which is already formatted as date type and have duration as number like 449. It means 449 seconds. So i need end_time. Of course i can obviously convert duration to date format and add duration on start_time using below simply queries

select to_char(to_date(USE_SEC,'sssss'),'hh24miss') 
  from ABA_RM_INB_USAGE;

USE_SEC column is containing integer(number in oracle) like 1167 and above query is returning date formatted result like 001927 that is okay.

This is query that add duration on start_time

select to_char(USE_STRT_DTTM, 'hh24miss') + to_char(to_date(USE_SEC, 'sssss'), 'hh24miss') as duration
  from ABA_RM_INB_USAGE;

This is returning that result which is problem that convert to date format

95980.

It means 09:59:80 oops 80 seconds is absolutely wrong. Can i add dates with keep their formats. How can i ?

Answer 1

In Oracle DATE values do not have a format - you use the TO_CHAR function to format them when you need to output them.

In this case it looks like you need to use an interval. You have a field which contains a number of seconds that you want to convert to an interval - for this you can use the TO_DSINTERVAL function, although amusingly enough you have to convert the number to a string in order to use the function to convert it to an interval:

-- Version using TO_DSINTERVAL

WITH cteData AS (SELECT USE_STRT_DTTM + TO_DSINTERVAL('PT' || TO_CHAR(USE_SEC) || 'S') AS DT_TIME
                   FROM ABA_RM_INB_USAGE)
SELECT TO_CHAR(DT_TIME, 'YYYY-MM-DD HH24:MI:SS') FORMATTED_DATE_TIME
  FROM cteData;

Docs for TO_DSINTERVAL here

dbfiddle demonstrating this in use here

EDIT

As @AlexPoole points out, the better function to use here is NUMTODSINTERVAL:

-- Version using NUMTODSINTERVAL

WITH cteData AS (SELECT USE_STRT_DTTM + NUMTODSINTERVAL(USE_SEC, 'SECOND') AS DT_TIME
                   FROM ABA_RM_INB_USAGE)
SELECT TO_CHAR(DT_TIME, 'YYYY-MM-DD HH24:MI:SS') FORMATTED_DATE_TIME
  FROM cteData;

Docs for NUMTODSINTERVAL here

updated dbfiddle here

Answer 2

Your first query is converting your number-of-second value to a string. In your second query you are converting the start time to another string. Both represent HHMISS. Then you add them together, effectively:

'094053' + '001927'

For the addition operator to work they are implicitly converted to numbers, so it becomes:

94053 + 1927

which gives you your (numeric) result of 95980.

As soon as you convert to strings you are losing the ability to treat them as dates and honour the mod-60 behaviour for minutes and seconds, which is my you appear to end up with 80 seconds - but they aren't really seconds at all, it's just a number. You also lose the mod-24 behaviour for hours, so if your start time is just before midnight and the duration pushes you over midnight, your result wouldn't reflect that either.

As @GordonLinoff suggested, keep your date as a date, and add the number of seconds as a number, or a number converted to an interval:

USE_STRT_DTTM + USE_SEC / (24*60*60)

or:

USE_STRT_DTTM + USE_SEC * interval '1' second

Demo:

-- CTE for sample data
with ABA_RM_INB_USAGE (USE_STRT_DTTM, USE_SEC) as (
  select to_date('09:40:53', 'HH24:MI:SS'), 1167 from dual
  union all
  select to_date('23:54:55', 'HH24:MI:SS'), 449 from dual
)
-- query showing working
select USE_STRT_DTTM,
       USE_SEC,
       to_char(to_date(USE_SEC, 'sssss'), 'hh24:mi:ss') as use_sec_hhmiss,
       USE_SEC * interval '1' second as use_sec_interval,
       USE_STRT_DTTM + USE_SEC / (24*60*60) as result1,
       USE_STRT_DTTM + USE_SEC * interval '1' second as result2
  from ABA_RM_INB_USAGE;

USE_STRT_DTTM       USE_SEC USE_SEC_HHMISS USE_SEC_INTERVAL    RESULT1             RESULT2            
------------------- ------- -------------- ------------------- ------------------- -------------------
2019-08-01 09:40:53    1167 00:19:27       +00 00:19:27.000000 2019-08-01 10:00:20 2019-08-01 10:00:20
2019-08-01 23:54:55     449 00:07:29       +00 00:07:29.000000 2019-08-02 00:02:24 2019-08-02 00:02:24

Read more about Datetime/Interval Arithmetic.

---

I have a start_time which is already formatted as date type

Your column is (I hope, and seems to be the case from your query) a date. Dates do not have intrinsic human-readable formats. When you query your table your client will format the date to something readable, using either its own preferences or your session's NLS_DATE_FORMAT.

Of course i can obviously convert duration to date format and add duration on start_time

You originally converted your duration to a date data type (via to_date()), at 00:19:27 on the first day of the current month (which is what if defaults to if not day, month or year components are supplied; my CTE above is doing the same). You cannot add a date to another date. That even has its own error, "ORA-00975: date + date not allowed". So you then converted both your date values (start time and converted duration) to strings. You can't add strings together either, as that makes no sense; but if you try Oracle will implicitly try to convert both strings to numbers. In this case that implicit conversion works for both strings, but it usually won't; the superficially-similar '09:40:53' + '00:19:27' would get "ORA-01722: invalid number".

Answer 3

You can use +. This is the traditional method:

select start_time + duration / 24*60*60

You can write this now as:

select start_time + duration * interval '1' second