Oracle：如何添加分钟到时间戳？ [英] Oracle: how to add minutes to a timestamp?

问题描述

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

to_char（date_and_time +（.000694 * 31）

大部分时间工作正常，但不是AM / PM边界上的时间，例如，添加30分钟到$ code> 12:30 [这是PM]返回 1:00 这是AM。我期望的答案是 13:00 。这样做的正确方法是什么？

解决方案

所有其他答案基本正确，但我不认为任何人直接回答了您的原始问题。

假设您的示例中的date_and_time是DATE或TIMESTAMP类型的列，我想您只需要更改： / p>

  to_char（date_and_time +（.000694 * 31））
  

：

  to_char（date_and_time +（.000694 * 31） ，'DD-MON-YYYY HH24：MI'）
  

这听起来像你的默认日期格式HH代码为小时，而不是HH24。

此外，我认为您的常数是令人困惑和不精确的。我想你所做的是计算（.000694）是关于一分钟的价值，你将它乘以你要添加的分钟数（在示例中为31，尽管你在文中说了30）。 / p>

我也将从一天开始，并将其划分为您所需代码中的单位。在这种情况下，（1/48）将是30分钟;或者如果你想为了清楚起见，你可以写（（1/24）*（1/2））。

这将避免四舍五入的错误对于那些固有的浮点，这在这里应该是无意义的），更清楚，至少对我来说。

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?

解决方案

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.

这篇关于Oracle：如何添加分钟到时间戳？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！