Oracle: how to add minutes to a timestamp?

问题:

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?

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

回答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

回答3:

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

回答4:

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

Where interval is one of

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

回答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

回答6:

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.

回答7:

Can we not use this

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

I am new to this domain.

回答8:

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'

回答9:

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

回答10:

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>

回答11:

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;

回答12:

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