CODEWITHSUNDEEP
oracle-databasedatetimeplsqldate-arithmetic
lightweight
lightweight

Reputation: 3327

Subtract one hour from datetime rather than one day

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

SELECT MAX(D_DTM)-1 FROM tbl1

...it goes back a day. How do I remove one hour from the column rather than one day?

I've also noticed that the datetime records for 12AM look like MM/DD/YYYY and not MM/DD/YYYY 00:00:00; I'm not sure if that matters.

Upvotes: 11

Views: 46614

Answers (6)

Ankit Kachchhi
Ankit Kachchhi

Reputation: 51

Its simple.

sysdate - 5/(24*60*60) --> Subtracts 5 seconds from systime

sysdate - 5/(24*60) --> Subtracts 5 minutes from systime

sysdate - 5/(24) --> Subtracts 5 hours from systime

Hence

select (sysdate - (1/24)) from dual

Upvotes: 4

sakumar
sakumar

Reputation: 499

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

Upvotes: 5

Joe W
Joe W

Reputation: 1872

Another method of using intervals is

NUMTODSINTERVAL( number, expression )

examples

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

I bring this up because it is useful for situations where using INTERVAL wont work.

Upvotes: 2

Ed Gibbs
Ed Gibbs

Reputation: 26333

Or use the INTERVAL function. It has the same result but I think it reads more clearly - that's of course just an opinion :)

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

The nice thing about the INTERVAL function is that you can make the interval be years, months, days, hours, minutes or seconds when dealing with a DATE value, though the month interval can be tricky when dealing with end-of-month dates.

And yes, the quote around the 1 in the example is required.

You can also use the Oracle-specific NumToDSInterval function, which is less standard but more flexible because it accepts variables instead of constants:

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

Upvotes: 7

A.B.Cade
A.B.Cade

Reputation: 16905

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

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

Upvotes: 19

Randy
Randy

Reputation: 16677

yes - dates go by integer days.

if you want hours you need to do some math - like -(1/24)

Upvotes: 6

Related Questions