CODEWITHSUNDEEP
sqloracle-database
user13046968
user13046968

Reputation:

How can I add minutes and seconds to interval

I have the following: trunc(sysdate - 1) + interval '8' hour

How can I add minutes and seconds to that interval?

Upvotes: 2

Views: 1867

Answers (3)

MT0
MT0

Reputation: 167822

Use HOUR TO SECOND instead of just HOUR:

SELECT TRUNC( SYSDATE - 1 ) + INTERVAL '8:12:34' HOUR TO SECOND
FROM   DUAL;

Outputs:

| TRUNC(SYSDATE-1)+INTERVAL'8:12:34'HOURTOSECOND |
| :--------------------------------------------- |
| 2020-06-08 08:12:34                            |

db<>fiddle here

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Sometimes, it is easier to resort to the old-fashioned method:

select trunc(sysdate - 1) + (8 / 24 + 30 / (24 * 60) + 5 / (24 * 60 * 60))

Or to express it using one interval and arithmetic:

trunc(sysdate - 1) + (8 * 60 * 60 + 30 * 60 + 17) * interval '1' second

There is a syntax for composite intervals; I find Oracle's version hard to remember.

Upvotes: -1

zedfoxus
zedfoxus

Reputation: 37039

You can try something like this:

select cast(trunc(sysdate - 1) as timestamp) + interval '8' hour + interval '40' minute + interval '20' second from dual;

You are currently using sysdate. In the example above, I'm switching that to timestamp. Then, add 8 hours. Then, add minutes and seconds.

Example: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=76a640ecfb4b59cfbac628511d2accd3

Upvotes: 1

Related Questions