Oracle PLSQL 将日期时间截断为特定小时数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18886414/
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
Oracle PLSQL truncate datetime to specific hours
提问by user2793907
I have an Oracle PLSQL code generating a list of datetime stamps and I would like to truncate them to the specific hours of 7am and 7pm rather than the beginning of the day.
我有一个 Oracle PLSQL 代码生成日期时间戳列表，我想将它们截断到早上 7 点和晚上 7 点的特定时间，而不是一天的开始。
For example:
例如：
- 01/03/2013 0700 becomes 01/03/2013 0700
- 01/03/2013 1235 becomes 01/03/2013 0700
- 01/03/2013 1932 becomes 01/03/2013 1900
- 02/03/2013 0612 becomes 01/03/2013 1900
- 01/03/2013 0700 变成 01/03/2013 0700
- 01/03/2013 1235 变成 01/03/2013 0700
- 01/03/2013 1932 变成 01/03/2013 1900
- 02/03/2013 0612 变成 01/03/2013 1900
My code is currently:
我的代码目前是：
SELECT TRUNC(TRUNC(SYSDATE,'hh') + 1/24 - (ROWNUM) / 24, 'dd') as shift_date
FROM widsys.times
ORDER BY SYSDATE
Thanks
谢谢
回答by Florin Ghita
Without conditionals :)
没有条件:)
Select your_date,
trunc(your_date - 7/24) + --the date
trunc(to_char(your_date - 7/24,'hh24')/12)/2 + --wich half of day
7/24 --shift the hour
from
your_table;
回答by user272735
with data(time) as (
select to_date('2013-09-19 00:00:00', 'YYYY-MM-DD HH24:MI:SS') from dual union all
select to_date('2013-09-19 06:45:44', 'YYYY-MM-DD HH24:MI:SS') from dual union all
select to_date('2013-09-19 08:12:25', 'YYYY-MM-DD HH24:MI:SS') from dual union all
select to_date('2013-09-19 18:59:59', 'YYYY-MM-DD HH24:MI:SS') from dual union all
select to_date('2013-09-19 19:00:00', 'YYYY-MM-DD HH24:MI:SS') from dual union all
select to_date('2013-09-19 20:15:35', 'YYYY-MM-DD HH24:MI:SS') from dual union all
select to_date('2013-09-19 23:59:59', 'YYYY-MM-DD HH24:MI:SS') from dual
)
select d.time,
case
when to_number(to_char(d.time, 'HH24')) >= 19 then
trunc(d.time) + 19/24
when to_number(to_char(d.time, 'HH24')) >= 7 then
trunc(d.time) + 7/24
else
trunc(d.time - 1) + 19/24
end as shift_date
from data d
;
回答by ajmalmhd04
Are you looking for a query like this:
您是否正在寻找这样的查询：
SELECT CASE
WHEN TO_CHAR(your_date, 'hh24') > 12
THEN TRUNC(your_date)+ 19/24
ELSE TRUNC(your_date)+ 7/24
END
FROM your_table;
