I have a table, which contains a timestamp. I want each row an INTERVAL apart. In my example below I am using a 15 minutes interval
My first solution appears to work perfectly except that the fractional part of the timestamp is always .000000, which I expect but don't want. I'd like it to contain some other numbers.
In my second attempt I'm trying to INCREMENT the fractional part of the timestamp by .100000 the problem with this solution is if I'm creating many rows (1344) in my secomd example, the seconds part of the timestamp gets incremented by 1 second after 10 rows are inserted. See the second solution below. I don't want that either.
My third solution works
and I get the desired results but my QA team is finding this solution cumbersome as they need to adjust the fraction and the MOD number in order not to INCREMENT the seconds. For example, let's say they want to INCREMENT by .25 or .150, etc for the fractional part of the timestamp that means readjusting VALUES in two places, which they find cumbersome.
Lastly, I thought perhaps a regexp_replace solution would work where I could chop off the integer part of the second solution if the number of rows being generated would change the value of the seconds. For example, if 1.100000 is being generated I would like the regex to only keep the fractional part ie .100000 and add that part only. That attempt failed with an error. See attempt below.
Is there a method I can use like regex to remove the whole number so the seconds will not change but keep the fractional part of the timestamp. I've been struggling to get this to work but have been unsuccessful.
Can you add some insight on why this is failing and provide a working example for attempt number 4.
Below is my code and attempts. Thanks in advance for your time and expertise.
Attempt #1 fractional part always .000000
CREATE TABLE t3 (
seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
dt TIMESTAMP );
/
INSERT into t3 (dt)
with dt (dt, interv) as (
select timestamp '2022-01-01 00:00:00',
numtodsinterval(15,'MINUTE') from dual
union all
select dt.dt + interv, interv from dt
where dt.dt + interv < date '2022-01-15')
select dt from dt;
/
SELECT * FROM T3 ORDER BY SEQ_NUM
SEQ_NUM DT
1 01-JAN-22 12.00.00.000000 AM
2 01-JAN-22 12.15.00.000000 AM
3 01-JAN-22 12.30.00.000000 AM
4 01-JAN-22 12.45.00.000000 AM
5 01-JAN-22 01.00.00.000000 AM
6 01-JAN-22 01.15.00.000000 AM
…
...
1342 14-JAN-22 11.15.00.000000 PM
1343 14-JAN-22 11.30.00.000000 PM
1344 14-JAN-22 11.45.00.000000 PM
Attempt #2 notice the seconds change at seq_num 1355. It went from :00 to :01. I don't want the seconds to change.
TRUNCATE TABLE T3;
/
INSERT into t3 (dt)
with dt (dt, interv) as (
select timestamp '2022-01-01 00:00:00',
numtodsinterval(15,'MINUTE') +
numtodsinterval( (rownum * .100000), 'SECOND') from dual
union all
select dt.dt + interv, interv from dt
where dt.dt + interv < date '2022-01-15')
select dt from dt;
/
SELECT * FROM T3 ORDER BY SEQ_NUM
SEQ_NUM DT
1345 01-JAN-22 12.00.00.000000 AM
1346 01-JAN-22 12.15.00.100000 AM
1347 01-JAN-22 12.30.00.200000 AM
1348 01-JAN-22 12.45.00.300000 AM
1349 01-JAN-22 01.00.00.400000 AM
1350 01-JAN-22 01.15.00.500000 AM
1351 01-JAN-22 01.30.00.600000 AM
1352 01-JAN-22 01.45.00.700000 AM
1353 01-JAN-22 02.00.00.800000 AM
1354 01-JAN-22 02.15.00.900000 AM
1355 01-JAN-22 02.30.01.000000 AM
1356 01-JAN-22 02.45.01.100000 AM
…
…
Attempt #3 successful but awkward for testing
TRUNCATE TABLE T3;
/
INSERT /*+ APPEND */ into t3 (dt)
SELECT TIMESTAMP '2022-01-01 00:00:00'
+ (LEVEL - 1) * INTERVAL '5' MINUTE
+ MOD(LEVEL - 1, 10) * INTERVAL '0.1' SECOND
FROM DUAL
CONNECT BY
TIMESTAMP '2022-01-01 00:00:00'
+ (LEVEL - 1) * INTERVAL '5' MINUTE
+ MOD(LEVEL - 1, 10) * INTERVAL '0.1' SECOND < DATE '2022-01-15';
/
Looking to get something like this to work.
TRUNCATE TABLE T3;
/
INSERT into t3 (dt)
with dt (dt, interv) as (
select timestamp '2022-01-01 00:00:00',
numtodsinterval(15,'MINUTE') +
regexp_replace(
numtodsinterval( (rownum * .100000), 'SECOND'), '[^.]+\.(.*)$', '0.\1') from dual
union all
select dt.dt + interv, interv from dt
where dt.dt + interv < date '2022-01-15')
select dt from dt;
/
I'm a little unclear exactly what you're trying to achieve here - you want to increase the minutes/seconds by a fixed offset, but have a "random" value for the fractional seconds?
Anyway one way to avoid duplicating the values is to select them all first in a subquery. Then reference these:
with vals as (
select timestamp '2022-01-01 00:00:00' start_date,
timestamp '2022-01-01 03:00:00' end_date,
interval '15' minute time_increment,
interval '0.1' second fraction,
10 second_mod
from dual
)
select start_date
+ (level - 1) * time_increment
+ mod(level - 1, second_mod) * fraction ts
from vals
connect by start_date
+ (level - 1) * time_increment
+ mod(level - 1, second_mod) * fraction < end_date;
TS
------------------------------
01-JAN-2022 00.00.00.000000000
01-JAN-2022 00.15.00.100000000
01-JAN-2022 00.30.00.200000000
01-JAN-2022 00.45.00.300000000
01-JAN-2022 01.00.00.400000000
01-JAN-2022 01.15.00.500000000
01-JAN-2022 01.30.00.600000000
01-JAN-2022 01.45.00.700000000
01-JAN-2022 02.00.00.800000000
01-JAN-2022 02.15.00.900000000
01-JAN-2022 02.30.00.000000000
01-JAN-2022 02.45.00.100000000Or you could make all the values bind variables:
select to_timestamp ( :start_date, 'yyyy-mm-dd hh24:mi:ss' )
+ (level - 1) * numtodsinterval ( :time_increment, 'minute' )
+ mod(level - 1, :second_mod ) * numtodsinterval ( :fraction, 'second' ) ts
from dual
connect by to_timestamp ( :start_date, 'yyyy-mm-dd hh24:mi:ss' )
+ (level - 1) * numtodsinterval ( :time_increment, 'minute' )
+ mod(level - 1, :second_mod ) * numtodsinterval ( :fraction, 'second' ) <
to_timestamp ( :end_date, 'yyyy-mm-dd hh24:mi:ss' );