Subtract one hour from datetime rather than one day

Question

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.

Solution 1

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

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

Solution 2

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

Solution 3

yes - dates go by integer days.

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

Solution 4

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

Solution 5

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

View more solutions

Comments

[Ed Gibbs]

How are you viewing the 12AM values? Is it through SQLPlus or a front-end language (C#, PHP, etc., etc.)?

[lightweight]

using Toad, is the front end the issue? I'm new the the Oracle env

[Ed Gibbs]

It could be a TOAD thing, but I'm not familiar with TOAD. If all the other dates in the column show the time component and the "midnight" dates don't, I think it's safe to assume that the "midnight" is really there and TOAD is just "helpfully" hiding it. There may be a setting where you can turn this feature on or off, but that's just a guess. I do know that the .NET languages and PHP will recognize the time portion - even if it's zero - and probably just about every other language will too.

[lightweight]

better answer IMO, more clear for future coders to know whats going on

[Cody Guldner]

Can you expand a little more on your answer, such as why you are using division to subtract?

[jpmc26]

@CodyGuldner The division is computing the value as a fraction of days. (1 hour is 1/24 of a day, so 5/24 days is 5 hours.) The subtraction then subtracts day. Personally, though, I'd be worried about floating point errors.

[Cody Guldner]

@jpmc26 The idea is that information like that should be included in the answer.