blocks|key|1700002|text|确保Oracle知道开始时间是PM，并为最终输出指定HH24格式掩码。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1700003|SELECT+to_char((to_date('12:40+PM',+'HH:MI+AM')+%2B+(1/24/60)+*+30),+'HH24:MI')+as+time
++FROM+dual

TIME
---------
13:10|code-block|syntax|javascript|1700004|注意:+HH:MI中的'AM'只是AM/PM子午线指示器的占位符。也可以是'PM'|offset|length|style|CODE|1700005|entityMap^0|0|0|B|4|11|4|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]|$I|T|J|U|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|N|$]]

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

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

TIME
---------
13:10
</code></pre>

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

blocks|key|1700096|text|如果字段的数据类型为date或timestamp，并且您添加了以天数表示的正确数字(在本例中为正确的一天分数)，Oracle应始终提供正确的结果。因此，如果您尝试在30分钟内增加该值，则应使用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1700097|select+field+%2B+0.5/24+from+table;|code-block|syntax|javascript|1700098|根据您提供的信息，我相信这就是您试图做的事情，并且我非常确定它是有效的。|1700099|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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 : 

<pre><code>select field + 0.5/24 from table;
</code></pre>

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

blocks|key|1485057|text|除了能够将天数添加到日期之外，您还可以使用间隔数据类型，假设您使用的是Oracle+9i或更高版本，这样会更容易阅读，|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1485058|SQL>+ed
Wrote+file+afiedt.buf
SELECT+sysdate,+sysdate+%2B+interval+'30'+minute+FROM+dual
SQL>+/

SYSDATE++++++++++++++SYSDATE%2BINTERVAL'30'
--------------------+--------------------
02-NOV-2008+16:21:40+02-NOV-2008+16:51:40|code-block|syntax|javascript|1485059|entityMap^0|Z|9|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

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

<pre><code>SQL&gt; ed
Wrote file afiedt.buf
SELECT sysdate, sysdate + interval '30' minute FROM dual
SQL&gt; /

SYSDATE SYSDATE+INTERVAL'30'
-------------------- --------------------
02-NOV-2008 16:21:40 02-NOV-2008 16:51:40
</code></pre>

blocks|key|1700211|text|根据您的要求，您需要用于to_char的HH24:MI格式。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1700212|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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

blocks|key|1485202|text|所有其他答案基本上都是正确的，但我认为没有人直接回答你最初的问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1485203|假设您示例中的"date_and_time“是一个类型为DATE或TIMESTAMP的列，我认为您只需要更改以下内容：|1485204|to_char(date_and_time+%2B+(.000694+*+31))|code-block|syntax|javascript|1485205|要这样做：|1485206|to_char(date_and_time+%2B+(.000694+*+31),+'DD-MON-YYYY+HH24:MI')|1485207|听起来您的默认日期格式使用"HH“代码表示小时，而不是"HH24”。|1485208|此外，我认为你的常量术语既令人困惑又不准确。我猜您所做的是计算(.000694)大约等于一分钟的值，然后将它乘以您想要添加的分钟数(在示例中是31，尽管您在文本中说的是30+)。|1485209|我也会从一天开始，并在代码中将其划分为您想要的单位。在这种情况下，(1/48)将是30分钟；或者，如果为了清楚起见，您可以将其分解，可以写成(+(1/24)+*+(1/2)+)。|1485210|这将避免舍入误差(除了那些浮点中固有的，在这里应该是没有意义的)，而且更清楚，至少对我来说是这样。|1485211|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Y|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|Z|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|10|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|11|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|12|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|13|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|14|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|V|$]]

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:

<pre><code>to_char(date_and_time + (.000694 * 31))
</code></pre>

to this:

<pre><code>to_char(date_and_time + (.000694 * 31), 'DD-MON-YYYY HH24:MI')
</code></pre>

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.

blocks|key|1700358|text|来自http://www.orafaq.com/faq/how_does_one_add_a_day_hour_minute_second_to_a_date_value|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1700359|SYSDATE伪列显示当前系统日期和时间。将1添加到SYSDATE将使日期提前1天。使用分数将小时、分钟或秒添加到日期|1700360|SQL>+select+sysdate,+sysdate%2B1/24,+sysdate+%2B1/1440,+sysdate+%2B+1/86400+from+dual;

SYSDATE++++++++++++++SYSDATE%2B1/24+++++++++SYSDATE%2B1/1440+++++++SYSDATE%2B1/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|code-block|syntax|javascript|1700361|entityMap|0|LINK|mutability|MUTABLE|url|http://www.orafaq.com/faq/how_does_one_add_a_day_hour_minute_second_to_a_date_value^0|2|2B|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@]|C|$I|J]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

from <a href="http://www.orafaq.com/faq/how_does_one_add_a_day_hour_minute_second_to_a_date_value" rel="noreferrer">http://www.orafaq.com/faq/how_does_one_add_a_day_hour_minute_second_to_a_date_value</a>

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

<pre><code>SQL&gt; 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
</code></pre>

blocks|key|1485365|text|我们能不能不用这个|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1485366|SELECT+date_and_time+%2B+INTERVAL+'20:00'+MINUTE+TO+SECOND+FROM+dual;|code-block|syntax|javascript|1485367|我是这个领域的新手。|1485368|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Can we not use this

<pre><code>SELECT date_and_time + INTERVAL '20:00' MINUTE TO SECOND FROM dual;
</code></pre>

I am new to this domain.

blocks|key|1700539|text|SELECT+to_char(sysdate+%2B+(1/24/60)+*+30,+'dd/mm/yy+HH24:MI+am')+from+dual;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1700540|简单地说，您可以将其与各种日期格式一起使用……|unstyled|1700541|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>SELECT to_char(sysdate + (1/24/60) * 30, 'dd/mm/yy HH24:MI am') from dual;
</code></pre>

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

blocks|key|1700572|text|要在oracle中编辑日期，您可以尝试|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1700573|++select+to_char(<columnName>+%2B+5+/+24+%2B+30+/+(24+*+60),
+++++++++++'DD/MM/RRRR+hh:mi+AM')+AS+<logicalName>+from+<tableName>|code-block|syntax|javascript|1700574|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

To edit Date in oracle you can try 

<pre><code> select to_char(&lt;columnName&gt; + 5 / 24 + 30 / (24 * 60),
 'DD/MM/RRRR hh:mi AM') AS &lt;logicalName&gt; from &lt;tableName&gt;
</code></pre>

blocks|key|1485565|text|就像这样很容易|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1485566|我将10分钟添加到系统日期中，并且总是优先使用Db服务器函数，而不是自定义函数。|1485567|select+to_char(sysdate+%2B+NUMTODSINTERVAL(10,'MINUTE'),'DD/MM/YYYY+HH24:MI:SS')+from+dual;|code-block|syntax|javascript|1485568|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

like that very easily

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

<pre><code>select to_char(sysdate + NUMTODSINTERVAL(10,'MINUTE'),'DD/MM/YYYY HH24:MI:SS') from dual;
</code></pre>

blocks|key|1485631|text|Oracle现在有了新的内置函数来实现这一点：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1485632|select+systimestamp+START_TIME,+systimestamp+%2B+NUMTODSINTERVAL(30,+'minute')+end_time+from+dual|code-block|syntax|javascript|1485633|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Oracle now has new built in functions to do this:
<pre><code>select systimestamp START_TIME, systimestamp + NUMTODSINTERVAL(30, 'minute') end_time from dual
</code></pre>

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

<code>to_char(date_and_time + (.000694 * 31)</code>

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

Oracle: how to add minutes to a timestamp?

我需要为Oracle date列中的值添加30分钟。我在SELECT语句中通过指定to_char(date_and_time + (.000694 * 31)这在大多数情况下都工作得很好。但不是当时间在AM/PM边界时。例如，将30分钟添加到为PM的12:30将返回为AM的1:00我期望的答案是13:00。这样做的正确...

问Oracle:如何在时间戳中添加分钟？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Oracle:如何在时间戳中添加分钟？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Oracle:如何在时间戳中添加分钟？
EN