René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Rounding dates to 15 seconds

Creating a table and filling with dates: 
create table r_15s (
  dt date,
  i  integer
);

declare
  v_dt date := sysdate;
begin
  for vi in 1..15 loop

    insert into r_15s values(v_dt, vi);

    v_dt := v_dt + trunc(dbms_random.value(5,35))/24/60/60;
  end loop;
end;
/

commit;

A solution with floor

dt - to_date('01010001','ddmmyyyy') (= diff_days) is the difference in days between the 1st of January of the year 1 and dt. This difference is fractional.
24*60*60*diff_days (= diff_secs) is the difference in seconds since January 1st of year 1. (24*60*60 equals the number of seconds per day).
floor(diff_secs/15) (= quart_min) is the number of quarters of minutes since January 1st of year1.
Now, quart_min must be multiplied again with 15 and then be divided by the number of seconds in a day. The result must be converted back to a date. 
select 
  dt,
  to_char(
    to_date('01010001','ddmmyyyy') + 
    floor (
      24*60*60*(dt - to_date('01010001','ddmmyyyy')) 
      /15) *15 / 24/60/60
  , 'dd.mm.yyyy hh24:mi:ss'),
  i
from
  r_15s;

Another solution

select
  dt,
  to_char(dt,'dd.mm.yyyy hh24:mi:') ||
  to_char(trunc(to_number(to_char(dt,'ss'))/15)*15,'FM00')
from 
  r_15s;