CODEWITHSUNDEEP
sqloracle-database
homer
homer

Reputation: 433

SQL subtract hours, minutes, seconds from date

DB: oracle

Is there a way or function to deduct below two sample data and get historic datetime in format (yyyymmdd hr24:mi:ss)?

1. Date: 20150324 13:05:22  (Date time)
2. Time: 125:36.25  (hr:mi.ss)

Sample expected result 

20150319 06:30:10

Upvotes: 0

Views: 3432

Answers (2)

David Faber
David Faber

Reputation: 12486

Your sample expected result is incorrect given your same data. It should be 2015-03-19 07:28:57. Something like the below might work for you:

WITH d1 AS (
    SELECT timestamp'2015-03-24 13:05:22' AS mydate
         , '125:36.25' AS mytime FROM dual
)
SELECT mydate - NUMTODSINTERVAL( TO_NUMBER(SUBSTR(mytime, 1, INSTR(mytime, ':') - 1))*3600
                               + TO_NUMBER(SUBSTR(mytime, INSTR(mytime, ':') + 1, 2))*60
                               + TO_NUMBER(SUBSTR(mytime, INSTR(mytime, '.') + 1, 2)), 'SECOND')
  FROM d1;

What I am doing in the above query is converting the "time" element to seconds and then to an INTERVAL value to subtract from the date/time value.

As an aside it would be better if the time column were an INTERVAL DAY TO SECOND instead of a VARCHAR2. Then it could be used directly in date/timestamp arithmetic without needing to jump through any hoops.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

I would suggest converting the second to fractional days and just subtracting from the first:

select (datecol - 
        (to_number(substr(hhmmss, -2)) / (60*24*24) +
         to_number(substr(hhmmss, -5, 2))/(60*24) +
         to_number(substr(hhmmss, instr(hhmmss, ':') - 1)/24
        )
       )

Upvotes: 0

Related Questions