46

I need to add 30 minutes to values in a Oracle date column. I do this in my SELECT statement by specifying

to_char(date_and_time + (.000694 * 31)

which works fine most of the time. But not when the time is on the AM/PM border. For example, adding 30 minutes to 12:30 [which is PM] returns 1:00 which is AM. The answer I expect is 13:00. What's the correct way to do this?

Vitaly Olegovitch
  • 3,509
  • 6
  • 33
  • 49
Sajee
  • 4,317
  • 14
  • 46
  • 54

13 Answers13

144

In addition to being able to add a number of days to a date, you can use interval data types assuming you are on Oracle 9i or later, which can be somewhat easier to read,

SQL> ed
Wrote file afiedt.buf
SELECT sysdate, sysdate + interval '30' minute FROM dual
SQL> /

SYSDATE              SYSDATE+INTERVAL'30'
-------------------- --------------------
02-NOV-2008 16:21:40 02-NOV-2008 16:51:40
erhun
  • 3,549
  • 2
  • 35
  • 44
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • 13
    +1 Wow, I never knew this...I can finally drop the ridiculous fractions of a day stuff! – wheelibin Aug 25 '10 at 08:32
  • 5
    This is really helpful and probably should be the accepted answer given the upvotes it has received. – Scott Bennett-McLeish Feb 08 '12 at 06:57
  • the answer I was looking for. Thanks a lot for this(http://stackoverflow.com/questions/257324/oracle-how-to-add-minutes-to-a-timestamp#comment28355714_258555) comment which pointed to this answer! – MohamedSanaulla Jan 14 '14 at 09:08
  • This is a good answer, but I personally think [jtomaszk's answer](https://stackoverflow.com/a/17039496/715077) is more clear and to the point. – Superole Sep 20 '17 at 13:44
  • This method also maintains precision if your source value is a `TIMESTAMP` such as `CURRENT_TIMESTAMP`. Adding 30 / 24 / 60 to a TIMESTAMP will convert it to a DATETIME. – aaaantoine Mar 20 '19 at 21:09
  • I like this, but it doesn't work when the interval is coming from a parameter or query result. – Homer Jan 24 '20 at 23:22
  • 1
    @Homer - I'm not sure I understand. If your parameter is an `interval`, it should behave the same way. If you are passing in a number and/or a unit, you probably want to use the `numToDSInterval` function to construct the interval rather than using the interval literal syntax. – Justin Cave Jan 25 '20 at 03:38
  • @JustinCave - I didn't know about this function, thanks! – Homer Feb 04 '20 at 17:43
30

All of the other answers are basically right but I don't think anyone's directly answered your original question.

Assuming that "date_and_time" in your example is a column with type DATE or TIMESTAMP, I think you just need to change this:

to_char(date_and_time + (.000694 * 31))

to this:

to_char(date_and_time + (.000694 * 31), 'DD-MON-YYYY HH24:MI')

It sounds like your default date format uses the "HH" code for the hour, not "HH24".

Also, I think your constant term is both confusing and imprecise. I guess what you did is calculate that (.000694) is about the value of a minute, and you are multiplying it by the number of minutes you want to add (31 in the example, although you said 30 in the text).

I would also start with a day and divide it into the units you want within your code. In this case, (1/48) would be 30 minutes; or if you wanted to break it up for clarity, you could write ( (1/24) * (1/2) ).

This would avoid rounding errors (except for those inherent in floating point which should be meaningless here) and is clearer, at least to me.

Dave Costa
  • 47,262
  • 8
  • 56
  • 72
  • 4
    dont forget to see the answer with 40+ ratings below :) – Kalpesh Soni Oct 03 '13 at 19:29
  • I'm downvoting this. While practical in general, there are always chances of: 1) rounding errors, 2) errors with daylight savings time and other date/time caveats, 3) it's less maintainable than actual interval calculations. – Lukas Eder Oct 17 '14 at 07:41
  • 1
    @KalpeshSoni Thank you for pointing out that a much better solution exists :) I suppose [Justin Cave's answer](https://stackoverflow.com/a/257399/715077) is the one you're refering to? – Superole Sep 20 '17 at 13:29
20
UPDATE "TABLE" 
SET DATE_FIELD = CURRENT_TIMESTAMP + interval '48' minute 
WHERE (...)

Where interval is one of

  • YEAR
  • MONTH
  • DAY
  • HOUR
  • MINUTE
  • SECOND
jtomaszk
  • 9,223
  • 2
  • 28
  • 40
  • 1
    This works :) and your answer is much easier to read than the Oracle docs on [Interval Literals for 11g](http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements003.htm#SQLRF00221) – Superole Sep 20 '17 at 14:24
13

from http://www.orafaq.com/faq/how_does_one_add_a_day_hour_minute_second_to_a_date_value

The SYSDATE pseudo-column shows the current system date and time. Adding 1 to SYSDATE will advance the date by 1 day. Use fractions to add hours, minutes or seconds to the date

SQL> select sysdate, sysdate+1/24, sysdate +1/1440, sysdate + 1/86400 from dual;

SYSDATE              SYSDATE+1/24         SYSDATE+1/1440       SYSDATE+1/86400
-------------------- -------------------- -------------------- --------------------
03-Jul-2002 08:32:12 03-Jul-2002 09:32:12 03-Jul-2002 08:33:12 03-Jul-2002 08:32:13
crazy
  • 131
  • 1
  • 2
13

I prefer using an interval literal for this, because interval '30' minute or interval '5' second is a lot easier to read then 30 / (24 * 60) or 5 / (24 * 60 * 69)

e.g.

  • some_date + interval '2' hour
  • some_date + interval '30' minute
  • some_date + interval '5' second
  • some_date + interval '2' day

You can also combine several units into one expression:

  • some_date + interval '2 3:06' day to minute

Adds 2 days, 3 hours and 6 minutes to the date value

The above is also standard SQL and also works in several other DBMS.

More details in the manual: https://docs.oracle.com/database/121/SQLRF/sql_elements003.htm#SQLRF00221

3

If the data type of the field is date or timestamp, Oracle should always give the correct result if you add the correct number given in number of days (or a the correct fraction of a day in your case). So if you are trying to bump the value in 30 minutes, you should use : 

select field + 0.5/24 from table;

Based on the information you provided, I believe this is what you tried to do and I am quite sure it works.

Sidnei Andersson
  • 39
  • 2
  • The above answer w/o using to_char on it provides just the default format, eg: 04-NOV-08. Which is not what I'm looking for. – Sajee Nov 04 '08 at 21:42
3

Can we not use this

SELECT date_and_time + INTERVAL '20:00' MINUTE TO SECOND FROM dual;

I am new to this domain.

saidevakumar
  • 111
  • 6
3

like that very easily

i added 10 minutes to system date and always in preference use the Db server functions not custom one .

select to_char(sysdate + NUMTODSINTERVAL(10,'MINUTE'),'DD/MM/YYYY HH24:MI:SS') from dual;
Bilal
  • 1,254
  • 13
  • 14
  • I could not get the "interval '2' second" syntax to work with JDBC parameter replacement but this worked for me as I could make the number in the function call a replaceable parameter. – Victor Apr 27 '23 at 14:11
1

Be sure that Oracle understands that the starting time is PM, and to specify the HH24 format mask for the final output.

SELECT to_char((to_date('12:40 PM', 'HH:MI AM') + (1/24/60) * 30), 'HH24:MI') as time
  FROM dual

TIME
---------
13:10

Note: the 'AM' in the HH:MI is just the placeholder for the AM/PM meridian indicator. Could be also 'PM'

Camilo Díaz Repka
  • 4,805
  • 5
  • 43
  • 68
1

Oracle now has new built in functions to do this:

select systimestamp START_TIME, systimestamp + NUMTODSINTERVAL(30, 'minute') end_time from dual
Julius Simon
  • 46
  • 4
0

Based on what you're asking for, you want the HH24:MI format for to_char.

Nathan Neulinger
  • 261
  • 1
  • 12
0

To edit Date in oracle you can try 

  select to_char(<columnName> + 5 / 24 + 30 / (24 * 60),
           'DD/MM/RRRR hh:mi AM') AS <logicalName> from <tableName>
Sam
  • 513
  • 1
  • 11
  • 27
-1
SELECT to_char(sysdate + (1/24/60) * 30, 'dd/mm/yy HH24:MI am') from dual;

simply you can use this with various date format....

Mark Hall
  • 53,938
  • 9
  • 94
  • 111
Chirag Mehta
  • 11
  • 1