Oracle：如何将分钟添加到时间戳？

Oracle: how to add minutes to a timestamp?

提问by Sajee

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

我需要为 Oracle 日期列中的值添加 30 分钟。我通过指定在我的 SELECT 语句中执行此操作

to_char(date_and_time + (.000694 * 31)

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:00which is AM. The answer I expect is 13:00. What's the correct way to do this?

大多数情况下都可以正常工作。但不是当时间在 AM/PM 边界时。例如，将 30 分钟添加到12:30[which is PM] 返回1:00哪个是 AM。我期待的答案是13:00。这样做的正确方法是什么？

采纳答案by Dave Costa

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:

假设您的示例中的“date_and_time”是一个类型为 DATE 或 TIMESTAMP 的列，我认为您只需要更改它：

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

听起来您的默认日期格式使用“HH”代码表示小时，而不是“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).

另外，我认为您的常数项既令人困惑又不准确。我猜你所做的是计算出 (.000694) 大约是一分钟的值，然后将它乘以要添加的分钟数（示例中为 31，尽管您在文本中说的是 30）。

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

我也会从一天开始，并将其划分为代码中所需的单位。在这种情况下，(1/48) 将是 30 分钟；或者，如果您想将其分解为清楚起见，您可以写成 ( ((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.

这将避免舍入错误（除了浮点固有的那些在这里应该毫无意义的错误）并且更清晰，至少对我来说。

回答by Justin Cave

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 9ior later, which can be somewhat easier to read,

除了能够为日期添加天数之外，您还可以使用间隔数据类型，假设您在Oracle 9i或以后，这可能更容易阅读，

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

回答by jtomaszk

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

Where intervalis one of

哪里interval是其中之一

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

• 年
• 月
• 日
• 小时
• MINUTE
• SECOND

回答by crazy

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

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

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

回答by a_horse_with_no_name

I prefer using an intervalliteral for this, because interval '30' minuteor interval '5' secondis a lot easier to read then 30 / (24 * 60)or 5 / (24 * 60 * 69)

I prefer using an intervalliteral for this, because interval '30' minuteor interval '5' secondis a lot easier to read then 30 / (24 * 60)or 5 / (24 * 60 * 69)

e.g.

e.g.

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

• 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:

You can also combine several units into one expression:

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

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

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

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.

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

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

回答by Sidnei Andersson

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 :

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.

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

回答by saidevakumar

Can we not use this

Can we not use this

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

I am new to this domain.

I am new to this domain.

回答by Camilo Díaz Repka

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

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'

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

回答by Nathan Neulinger

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

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

回答by Bilal

like that very easily

like that very easily

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

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;