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

问题描述

我需要将 30 分钟添加到 Oracle 日期列中的值.我通过指定

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)

大部分时间都可以正常工作.但不是当时间在上午/下午边界时.例如，将 30 分钟添加到 12:30 [即 PM] 返回 1:00 即 AM.我期望的答案是13:00.这样做的正确方法是什么?

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.

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

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_char(date_and_time + (.000694 * 31), 'DD-MON-YYYY HH24:MI')

听起来您的默认日期格式使用HH"代码表示小时，而不是HH24".

It sounds like your default date format uses the "HH" code for the hour, not "HH24".

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

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

我也会从一天开始，并将其划分为您想要在代码中使用的单元.在这种情况下，(1/48) 将是 30 分钟；或者如果您想为了清楚起见将其分解，您可以写成 ( (1/24) * (1/2) ).

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屋！