Geek asks and answers

Subtract one hour from date and time, not one day

I have a column datetimein Oracle (MM / DD / YYYY HH: MM: SS AM / PM), but when I do this:

SELECT MAX(D_DTM)-1 FROM tbl1

... he returns daily. How to remove one hour from a column, not one day?

I also noticed that the entries datetimefor 12AM look like MM / DD / YYYY, not MM / DD / YYYY 00:00:00; I'm not sure if this is important.

+5
source share
6 answers

Randy's answer is good, but you can also use intervals:

SELECT MAX(D_DTM)- interval '1' hour FROM tbl1
+13
source

Or use a function INTERVAL. It has the same result, but I think it reads more clearly - this, of course, is just an opinion :)

SELECT MAX(D_DTM) - INTERVAL '1' HOUR FROM tbl1

INTERVAL , , , , , DATE, .

, 1 .

Oracle NumToDSInterval, , , :

SELECT MAX(D_DTM) - NUMTODSINTERVAL(1, 'HOUR') FROM tbl1
+5

- .

, - -(1/24)

+4
select sysdate - numtodsinterval(1,'hour') from dual
+4

NUMTODSINTERVAL( number, expression )


NUMTODSINTERVAL(150, 'DAY')
NUMTODSINTERVAL(1500, 'HOUR')
NUMTODSINTERVAL(15000, 'MINUTE')
NUMTODSINTERVAL(150000, 'SECOND')

, , INTERVAL .

+2

.

sysdate - 5/(24 * 60 * 60) → 5 systime

sysdate - 5/(24 * 60) → 5 systime

sysdate - 5/(24) → 5 systime

select (sysdate - (1/24)) from the double

+1
source

More articles:


All Articles