Oracle: how to add minutes to a timestamp?

Question

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?

Trihedron

Answer 1

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.

Floydflss

dont forget to see the answer with 40+ ratings below :) – Riojas

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

@KalpeshSoni Thank you for pointing out that a much better solution exists :) I suppose Justin Cave's answer is the one you're refering to? – Jonme

Answer 2

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

Wisdom

+1 Wow, I never knew this...I can finally drop the ridiculous fractions of a day stuff! – False

This is really helpful and probably should be the accepted answer given the upvotes it has received. – Dugaid

the answer I was looking for. Thanks a lot for this(#257824) comment which pointed to this answer! – Fluting

This is a good answer, but I personally think jtomaszk's answer is more clear and to the point. – Jonme

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

I like this, but it doesn't work when the interval is coming from a parameter or query result. – Platitudinous

@Platitudinous - 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. – Wisdom

@JustinCave - I didn't know about this function, thanks! – Platitudinous

Answer 3

UPDATE "TABLE" 
SET DATE_FIELD = CURRENT_TIMESTAMP + interval '48' minute 
WHERE (...)

Where interval is one of

• YEAR
• MONTH
• DAY
• HOUR
• MINUTE
• SECOND

Each

This works :) and your answer is much easier to read than the Oracle docs on Interval Literals for 11g – Jonme

Answer 4

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

Elfreda

Answer 5

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

Epigram

perfect solution – Georgeannageorgeanne

Answer 6

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;

Commodity

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

Answer 7

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.

Uninspired

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

Answer 8

Can we not use this

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

I am new to this domain.

Hammertoe

Answer 9

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'

Variable

Answer 10

Oracle now has new built in functions to do this:

select systimestamp START_TIME, systimestamp + NUMTODSINTERVAL(30, 'minute') end_time from dual

Behistun

Answer 11

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

Lidia

Answer 12

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>

Dynamite

5 / 24 for 5 hours and 30 / (24 * 60) for 30 minutes. – Dynamite

Answer 13

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

Curitiba

Why would you use the anti-meridian with HH24 format? – Mcgowan