oracle 在 SQL 中将日期字段舍入到下半小时
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9877641/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Round up date field to next half hour in SQL
提问by Paul
In SQL, I have a DATEvariable which I want to round to the next available half hour (i.e. xx:30). Note that this shouldn't just be to the nearesthalf hour.
在 SQL 中，我有一个DATE变量，我想四舍五入到下一个可用的半小时（即 xx:30）。请注意，这不应该只是最近的半小时。
Here are some examples:
这里有些例子：
Time Expected outcome
2012-03-26 11:25 AM 2012-03-26 11:30 AM
2012-03-26 11:45 AM 2012-03-26 12:30 PM
Is there a way to do this in SQL?
有没有办法在 SQL 中做到这一点？
采纳答案by Michael Broughton
Gave a +1 to Justin's answer for the originality.
对贾斯汀的原创性回答给予 +1 分。
If you want a more pedantic (or, perhaps more easily understood or maintained) aproach,
如果你想要一个更迂腐（或者，也许更容易理解或维护）的方法，
select case when to_number(to_char(sysdate,'mi')) > 30
then trunc(sysdate,'mi') + ((60 - to_number(to_char(sysdate,'mi')) +30)/1440)
else trunc(sysdate,'mi') + (30 - to_number(to_char(sysdate,'mi')))/1440 end
from dual
I'm only tossing this in there as this might be a more adaptable approach if you needed to do something similar against other numbers, like 45 minutes past the hour for example. You would just replace the three 30s with 45s and away you go.
我只是把它扔在那里，因为如果您需要对其他数字做类似的事情，例如一小时后的 45 分钟，这可能是一种更具适应性的方法。你只需用 45s 替换三个 30s 就可以了。
For performance, though, for your exact problem - Justin's is a real winner.
但是，对于性能，对于您的确切问题 - 贾斯汀是真正的赢家。
回答by Justin Cave
It's a bit convoluted, but something like
这有点令人费解，但类似于
SELECT round( (date_col - trunc(date_col)) * 48 )/ 48 +
trunc( date_col )
FROM dual
should work.
应该管用。
SQL> ed
Wrote file afiedt.buf
1 select round((sysdate - trunc(sysdate)) * 48)/48 + trunc(sysdate),
2 sysdate
3* from dual
SQL> /
ROUND((SYSDATE-TRUNC SYSDATE
-------------------- --------------------
26-mar-2012 14:30:00 26-mar-2012 14:35:30
TRUNC(date_col)truncates the time to midnight.date_col - trunc(date_col)returns the fraction of a day that has elapsed since midnight.- Multiplying by 48 gives you the number of half-hour increments
- Rounding that gives you the nearest half-hour
- Then dividing by 48 gives you a fraction of a day that can be added back to the
trunc(date_col)
TRUNC(date_col)将时间截断到午夜。date_col - trunc(date_col)返回自午夜以来经过的一天的分数。- 乘以 48 为您提供半小时增量的数量
- 为您提供最接近的半小时的四舍五入
- 然后除以 48 给你一天的一小部分，可以加回到
trunc(date_col)
回答by Dave Costa
Here's a little adjustment to Justin's answer that I think gives the results you want:
以下是对 Justin 回答的一些调整，我认为它可以提供您想要的结果：
WITH thedate AS (SELECT trunc(SYSDATE)+6.5/48 thedate FROM dual)
select round((thedate - trunc(thedate)) * 24 - 1/(24*3600))/24 + trunc(thedate)+1/48,
theDATE
from thedate;
Basically, this rounds to the nearest hour (after subtracting a second to prevent rounding up when you are exactly on the half-hour) then adds a half hour.
基本上，这四舍五入到最接近的小时（在减去一秒以防止在半小时时四舍五入）然后添加半小时。
回答by cyborg007
My code does this using ROUND function to round to the nearest hour and then adding half an hour
我的代码使用 ROUND 函数执行此操作以舍入到最近的小时，然后添加半小时
WITH thedate AS
(SELECT TO_DATE ('2012-03-26 11:20 AM', 'YYYY-MM-DD HH:MI AM') mydate
FROM DUAL)
SELECT mydate,
TO_CHAR (ROUND (mydate, 'HH') + 1 / 48,
'YYYY-MM-DD HH:MI AM'
) "Rounded Date"
FROM thedate;
Hope it helps
希望能帮助到你
回答by Craig McKay
To get the next half hour, modify Justin Cave's solution by changing the ROUND to CEIL:
要获得接下来的半小时，请通过将 ROUND 更改为 CEIL 来修改 Justin Cave 的解决方案：
SELECT CEIL( (date_col - trunc(date_col)) * 48 )/ 48 +
trunc( date_col )
FROM dual
ROUNDgoes to the nearest(up or down), CEILgoes up to the next(always up). FLOORwould go to the previous(always down).
ROUND移至最近的（向上或向下），CEIL移至下一个（始终向上）。 FLOOR会转到前一个（总是向下）。
To answer Michael Broughton's point about maintability:
回答 Michael Broughton 关于可维护性的观点：
48 is the number of "half hours" in a day. To change the period, replace both instances with:
48 是一天中“半小时”的数量。要更改期间，请将两个实例替换为：
60/x * 24
where xis the number of minutes in the period you want.
哪里x是您想要的时间段内的分钟数。
E.g. Every 10 mins = (60/10) * 24 = 144.
例如，每 10 分钟 = (60/10) * 24 = 144。
Hope this helps!
希望这可以帮助！
回答by mags
simplified (I hope)...
简化（我希望）...
select
case
when to_number(to_char(sysdate,'mi')) <= 30
then trunc(sysdate,'hh24') + 30/1440
else trunc(sysdate,'hh24') + 1/24
end
from dual;
