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.

Moreau

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

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

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. – Lauraine

Answer 1

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

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

Catawba

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

Answer 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

Lauraine

Answer 3

yes - dates go by integer days.

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

Dense

Answer 4

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

Erigeron

Answer 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

Joslin

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

@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. – Kimura

@Kimura The idea is that information like that should be included in the answer. – Teatime

Answer 6

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.

Toinette