Oracle PLSQL truncate datetime to specific hours
18,454
Solution 1
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;
Solution 2
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
;
Author by
user2793907
Updated on June 04, 2022Comments
-
user2793907 almost 2 years
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.
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
My code is currently:
SELECT TRUNC(TRUNC(SYSDATE,'hh') + 1/24 - (ROWNUM) / 24, 'dd') as shift_date FROM widsys.times ORDER BY SYSDATE
Thanks