Home » SQL & PL/SQL » SQL & PL/SQL » Creating rows every 5 minutes without overlap (19c)
Creating rows every 5 minutes without overlap [message #684758] Mon, 16 August 2021 11:12 Go to next message
Unclefool
Messages: 82
Registered: August 2021
Member
I have some SQL that generates rows for every 5 minutes. How can this be modified to get rid of overlapping times.

Note: I don't want the times to cross midnight.

Ideally I would like to create a function where I can pass in a start time and end time in and loop until going past the end time


ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';

with
   row_every_5_mins as
   ( select trunc(sysdate) + (rownum-1)*5/1440 t_from,
            trunc(sysdate) + rownum*5/1440 t_to
     from dual
      connect by level <= 1440/5
   ) SELECT * from row_every_5_mins;


Current output:

T_FROM    T_TO
08162021 00:00:00    08162021 00:05:00
08162021 00:05:00    08162021 00:10:00
08162021 00:10:00    08162021 00:15:00
08162021 00:15:00    08162021 00:20:00
…
….

Desired output 

T_FROM    T_TO
08162021 00:00:00    08162021 00:05:00    08162021 00:10:00
08162021 00:15:00    08162021 00:20:00    08162021 00:25:00
…
….

[Updated on: Mon, 16 August 2021 12:23]

Report message to a moderator

Re: Creating rows every 5 minutes without overlap [message #684759 is a reply to message #684758] Mon, 16 August 2021 19:11 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
with row_every_15_mins
  as (
      select  trunc(sysdate) + (level - 1) * 15 / 1440 t_from
        from  dual
        connect by level <= 96
     )
select  t_from,
        t_from + 5 / 1440 t_from_plus_5,
        t_from + 10 / 1440 t_to_plus_10
  from  row_every_15_mins
/

T_FROM            T_FROM_PLUS_5     T_TO_PLUS_10
----------------- ----------------- -----------------
08162021 00:00:00 08162021 00:05:00 08162021 00:10:00
08162021 00:15:00 08162021 00:20:00 08162021 00:25:00
08162021 00:30:00 08162021 00:35:00 08162021 00:40:00
08162021 00:45:00 08162021 00:50:00 08162021 00:55:00
08162021 01:00:00 08162021 01:05:00 08162021 01:10:00
08162021 01:15:00 08162021 01:20:00 08162021 01:25:00
08162021 01:30:00 08162021 01:35:00 08162021 01:40:00
08162021 01:45:00 08162021 01:50:00 08162021 01:55:00
08162021 02:00:00 08162021 02:05:00 08162021 02:10:00
08162021 02:15:00 08162021 02:20:00 08162021 02:25:00
08162021 02:30:00 08162021 02:35:00 08162021 02:40:00
08162021 02:45:00 08162021 02:50:00 08162021 02:55:00
08162021 03:00:00 08162021 03:05:00 08162021 03:10:00
08162021 03:15:00 08162021 03:20:00 08162021 03:25:00
08162021 03:30:00 08162021 03:35:00 08162021 03:40:00
08162021 03:45:00 08162021 03:50:00 08162021 03:55:00
08162021 04:00:00 08162021 04:05:00 08162021 04:10:00
08162021 04:15:00 08162021 04:20:00 08162021 04:25:00
08162021 04:30:00 08162021 04:35:00 08162021 04:40:00
08162021 04:45:00 08162021 04:50:00 08162021 04:55:00
08162021 05:00:00 08162021 05:05:00 08162021 05:10:00
08162021 05:15:00 08162021 05:20:00 08162021 05:25:00
08162021 05:30:00 08162021 05:35:00 08162021 05:40:00
08162021 05:45:00 08162021 05:50:00 08162021 05:55:00
08162021 06:00:00 08162021 06:05:00 08162021 06:10:00
08162021 06:15:00 08162021 06:20:00 08162021 06:25:00
08162021 06:30:00 08162021 06:35:00 08162021 06:40:00
08162021 06:45:00 08162021 06:50:00 08162021 06:55:00
08162021 07:00:00 08162021 07:05:00 08162021 07:10:00
08162021 07:15:00 08162021 07:20:00 08162021 07:25:00
08162021 07:30:00 08162021 07:35:00 08162021 07:40:00
08162021 07:45:00 08162021 07:50:00 08162021 07:55:00
08162021 08:00:00 08162021 08:05:00 08162021 08:10:00
08162021 08:15:00 08162021 08:20:00 08162021 08:25:00
08162021 08:30:00 08162021 08:35:00 08162021 08:40:00
08162021 08:45:00 08162021 08:50:00 08162021 08:55:00
08162021 09:00:00 08162021 09:05:00 08162021 09:10:00
08162021 09:15:00 08162021 09:20:00 08162021 09:25:00
08162021 09:30:00 08162021 09:35:00 08162021 09:40:00
08162021 09:45:00 08162021 09:50:00 08162021 09:55:00
08162021 10:00:00 08162021 10:05:00 08162021 10:10:00
08162021 10:15:00 08162021 10:20:00 08162021 10:25:00
08162021 10:30:00 08162021 10:35:00 08162021 10:40:00
08162021 10:45:00 08162021 10:50:00 08162021 10:55:00
08162021 11:00:00 08162021 11:05:00 08162021 11:10:00
08162021 11:15:00 08162021 11:20:00 08162021 11:25:00
08162021 11:30:00 08162021 11:35:00 08162021 11:40:00
08162021 11:45:00 08162021 11:50:00 08162021 11:55:00
08162021 12:00:00 08162021 12:05:00 08162021 12:10:00
08162021 12:15:00 08162021 12:20:00 08162021 12:25:00
08162021 12:30:00 08162021 12:35:00 08162021 12:40:00
08162021 12:45:00 08162021 12:50:00 08162021 12:55:00
08162021 13:00:00 08162021 13:05:00 08162021 13:10:00
08162021 13:15:00 08162021 13:20:00 08162021 13:25:00
08162021 13:30:00 08162021 13:35:00 08162021 13:40:00
08162021 13:45:00 08162021 13:50:00 08162021 13:55:00
08162021 14:00:00 08162021 14:05:00 08162021 14:10:00
08162021 14:15:00 08162021 14:20:00 08162021 14:25:00
08162021 14:30:00 08162021 14:35:00 08162021 14:40:00
08162021 14:45:00 08162021 14:50:00 08162021 14:55:00
08162021 15:00:00 08162021 15:05:00 08162021 15:10:00
08162021 15:15:00 08162021 15:20:00 08162021 15:25:00
08162021 15:30:00 08162021 15:35:00 08162021 15:40:00
08162021 15:45:00 08162021 15:50:00 08162021 15:55:00
08162021 16:00:00 08162021 16:05:00 08162021 16:10:00
08162021 16:15:00 08162021 16:20:00 08162021 16:25:00
08162021 16:30:00 08162021 16:35:00 08162021 16:40:00
08162021 16:45:00 08162021 16:50:00 08162021 16:55:00
08162021 17:00:00 08162021 17:05:00 08162021 17:10:00
08162021 17:15:00 08162021 17:20:00 08162021 17:25:00
08162021 17:30:00 08162021 17:35:00 08162021 17:40:00
08162021 17:45:00 08162021 17:50:00 08162021 17:55:00
08162021 18:00:00 08162021 18:05:00 08162021 18:10:00
08162021 18:15:00 08162021 18:20:00 08162021 18:25:00
08162021 18:30:00 08162021 18:35:00 08162021 18:40:00
08162021 18:45:00 08162021 18:50:00 08162021 18:55:00
08162021 19:00:00 08162021 19:05:00 08162021 19:10:00
08162021 19:15:00 08162021 19:20:00 08162021 19:25:00
08162021 19:30:00 08162021 19:35:00 08162021 19:40:00
08162021 19:45:00 08162021 19:50:00 08162021 19:55:00
08162021 20:00:00 08162021 20:05:00 08162021 20:10:00
08162021 20:15:00 08162021 20:20:00 08162021 20:25:00
08162021 20:30:00 08162021 20:35:00 08162021 20:40:00
08162021 20:45:00 08162021 20:50:00 08162021 20:55:00
08162021 21:00:00 08162021 21:05:00 08162021 21:10:00
08162021 21:15:00 08162021 21:20:00 08162021 21:25:00
08162021 21:30:00 08162021 21:35:00 08162021 21:40:00
08162021 21:45:00 08162021 21:50:00 08162021 21:55:00
08162021 22:00:00 08162021 22:05:00 08162021 22:10:00
08162021 22:15:00 08162021 22:20:00 08162021 22:25:00
08162021 22:30:00 08162021 22:35:00 08162021 22:40:00
08162021 22:45:00 08162021 22:50:00 08162021 22:55:00
08162021 23:00:00 08162021 23:05:00 08162021 23:10:00
08162021 23:15:00 08162021 23:20:00 08162021 23:25:00
08162021 23:30:00 08162021 23:35:00 08162021 23:40:00
08162021 23:45:00 08162021 23:50:00 08162021 23:55:00

96 rows selected.

SQL>
SY.
Re: Creating rows every 5 minutes without overlap [message #684760 is a reply to message #684758] Mon, 16 August 2021 19:53 Go to previous message
Unclefool
Messages: 82
Registered: August 2021
Member
Thank you for your expertise

[Updated on: Mon, 16 August 2021 20:01]

Report message to a moderator

Previous Topic: Table type collection in SELECT
Next Topic: SYSAUX IMPACT ON PERFORMANCE
Goto Forum:
  


Current Time: Fri Mar 29 08:00:15 CDT 2024