Oracle INCREMENT fractional parts of a timestamp-CodePudding

I have a table, which contains a timestamp. I want each row an INTERVAL apart. In my example below I am using a15 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 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.

Thirdly, I thought perhaps a regexp_replace solution would work where I could chop off the integer part of the second solution (keep the fractional part) and then add that to my interval. That attempt failed with an error. See third attempt below.

Is there a way I can get this to work? Where I can change the fractional part of the timestamp without it affecting the MMDDYYYY HH24:MI:SS part of the date.

Below is my code and attempts along with an example of the sample output I'm looking to generate.

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


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 failed


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

ORA-30081: invalid data type for datetime/interval arith

Desired output


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.00.000000 AM
1356    01-JAN-22 02.45.00.100000 AM
1357    01-JAN-22 03.00.00.200000 AM
…
…

CodePudding user response：

You can use:

INSERT into t3 (dt)
SELECT TIMESTAMP '2022-01-01 00:00:00'
           (LEVEL - 1) * INTERVAL '15' MINUTE
           MOD(LEVEL - 1, 10) * INTERVAL '0.1' SECOND
FROM   DUAL
CONNECT BY
       TIMESTAMP '2022-01-01 00:00:00'
           (LEVEL - 1) * INTERVAL '15' MINUTE
           MOD(LEVEL - 1, 10) * INTERVAL '0.1' SECOND < DATE '2022-01-15';

or:

INSERT into t3 (dt)
SELECT TIMESTAMP '2022-01-01 00:00:00'
          NUMTODSINTERVAL((LEVEL-1)*15*60   MOD(LEVEL-1, 10)/10, 'SECOND')
FROM   DUAL
CONNECT BY
      TIMESTAMP '2022-01-01 00:00:00'
          NUMTODSINTERVAL((LEVEL-1)*15*60   MOD(LEVEL-1, 10)/10, 'SECOND')
        < DATE '2022-01-15';

Which both give the values:

SEQ_NUM DT

1 2022-01-01 00:00:00.000000

2 2022-01-01 00:15:00.100000

3 2022-01-01 00:30:00.200000

4 2022-01-01 00:45:00.300000

5 2022-01-01 01:00:00.400000

6 2022-01-01 01:15:00.500000

7 2022-01-01 01:30:00.600000

8 2022-01-01 01:45:00.700000

9 2022-01-01 02:00:00.800000

10 2022-01-01 02:15:00.900000

11 2022-01-01 02:30:00.000000

SEQ_NUM	DT
1	2022-01-01 00:00:00.000000
2	2022-01-01 00:15:00.100000
3	2022-01-01 00:30:00.200000
4	2022-01-01 00:45:00.300000
5	2022-01-01 01:00:00.400000
6	2022-01-01 01:15:00.500000
7	2022-01-01 01:30:00.600000
8	2022-01-01 01:45:00.700000
9	2022-01-01 02:00:00.800000
10	2022-01-01 02:15:00.900000
11	2022-01-01 02:30:00.000000

db<>fiddle here

CodePudding user response：

It looks like you just want to add an interval of 15 minutes and 0.1 seconds

select level seq_num,
       timestamp '2022-01-01 00:00:00'   
          (level-1) * interval '15' minute  
          (level-1) * interval '0.1' second dt
  from dual
connect by level <= 10

Here's a dbfiddle that shows it producing the output you want.