You Asked
Hello, I have SQL for PostgreSQL with following inside:
generate_series('2013-03-04T00:00:00.000+12:00', '2013-03-10T00:00:00.000+12:00', 'PT15M')
parameters are (date start, date end, interval in some ISO specific format PT15M (means 15 minutes));
and I need to write similar SQL for Oracle DB with support of this ISO format (interval can be changed, not always 15 minutes).
I searched here, there was one example but only with 1 day interval, not minutes and without dynamic ISO interval.
and Connor said...
Some simple examples to get you started
SQL> select timestamp '2016-01-10 14:00:00' + numtodsinterval(rownum*10,'MINUTE')
2 from dual
3 connect by level <= 20;
TIMESTAMP'2016-01-1014:00:00'+NUMTODSINTERVAL(ROWNUM*10,'MINUTE')
---------------------------------------------------------------------------
10-JAN-16 02.10.00.000000000 PM
10-JAN-16 02.20.00.000000000 PM
10-JAN-16 02.30.00.000000000 PM
10-JAN-16 02.40.00.000000000 PM
10-JAN-16 02.50.00.000000000 PM
10-JAN-16 03.00.00.000000000 PM
10-JAN-16 03.10.00.000000000 PM
10-JAN-16 03.20.00.000000000 PM
10-JAN-16 03.30.00.000000000 PM
10-JAN-16 03.40.00.000000000 PM
10-JAN-16 03.50.00.000000000 PM
10-JAN-16 04.00.00.000000000 PM
10-JAN-16 04.10.00.000000000 PM
10-JAN-16 04.20.00.000000000 PM
10-JAN-16 04.30.00.000000000 PM
10-JAN-16 04.40.00.000000000 PM
10-JAN-16 04.50.00.000000000 PM
10-JAN-16 05.00.00.000000000 PM
10-JAN-16 05.10.00.000000000 PM
10-JAN-16 05.20.00.000000000 PM
20 rows selected.
SQL>
SQL> select timestamp '2016-01-10 14:00:00' + numtodsinterval(rownum*10,'SECOND')
2 from dual
3 connect by level <= 20;
TIMESTAMP'2016-01-1014:00:00'+NUMTODSINTERVAL(ROWNUM*10,'SECOND')
---------------------------------------------------------------------------
10-JAN-16 02.00.10.000000000 PM
10-JAN-16 02.00.20.000000000 PM
10-JAN-16 02.00.30.000000000 PM
10-JAN-16 02.00.40.000000000 PM
10-JAN-16 02.00.50.000000000 PM
10-JAN-16 02.01.00.000000000 PM
10-JAN-16 02.01.10.000000000 PM
10-JAN-16 02.01.20.000000000 PM
10-JAN-16 02.01.30.000000000 PM
10-JAN-16 02.01.40.000000000 PM
10-JAN-16 02.01.50.000000000 PM
10-JAN-16 02.02.00.000000000 PM
10-JAN-16 02.02.10.000000000 PM
10-JAN-16 02.02.20.000000000 PM
10-JAN-16 02.02.30.000000000 PM
10-JAN-16 02.02.40.000000000 PM
10-JAN-16 02.02.50.000000000 PM
10-JAN-16 02.03.00.000000000 PM
10-JAN-16 02.03.10.000000000 PM
10-JAN-16 02.03.20.000000000 PM
20 rows selected.
SQL>
SQL>
SQL> select timestamp '2016-01-10 14:00:00' + numtodsinterval(rownum,'HOUR')
2 from dual
3 connect by level <= 20;
TIMESTAMP'2016-01-1014:00:00'+NUMTODSINTERVAL(ROWNUM,'HOUR')
---------------------------------------------------------------------------
10-JAN-16 03.00.00.000000000 PM
10-JAN-16 04.00.00.000000000 PM
10-JAN-16 05.00.00.000000000 PM
10-JAN-16 06.00.00.000000000 PM
10-JAN-16 07.00.00.000000000 PM
10-JAN-16 08.00.00.000000000 PM
10-JAN-16 09.00.00.000000000 PM
10-JAN-16 10.00.00.000000000 PM
10-JAN-16 11.00.00.000000000 PM
11-JAN-16 12.00.00.000000000 AM
11-JAN-16 01.00.00.000000000 AM
11-JAN-16 02.00.00.000000000 AM
11-JAN-16 03.00.00.000000000 AM
11-JAN-16 04.00.00.000000000 AM
11-JAN-16 05.00.00.000000000 AM
11-JAN-16 06.00.00.000000000 AM
11-JAN-16 07.00.00.000000000 AM
11-JAN-16 08.00.00.000000000 AM
11-JAN-16 09.00.00.000000000 AM
11-JAN-16 10.00.00.000000000 AM
20 rows selected.
SQL>