Oracle PLSQL reduces time and time to specific hours

Question

I have Oracle PLSQL code generating a list of date and time stamps, and I would like to truncate them at certain hours of 7 am and 7 pm, and not at the beginning of the day.

For example:

• 03/01/2013 0700 becomes 01/03/2013 0700
• 03/01/2013 1235 becomes 01/03/2013 0700
• 03/01/2013 1932 becomes 01/03/2013 1900
• 03/02/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

Answer 1

No conventions :)

 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; 

See the violin.

Answer 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 ; 

Answer 3

You are looking for the following query:

 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;