CODEWITHSUNDEEP
sqloracle-databaseoracle10g
RLearner
RLearner

Reputation: 65

Generate a table with a range of timestamps - Oracle SQL

I am trying to create a table with 2 columns in the below format with all the dates of 2019:-

      START_TIME          END_TIME
2010-01-01 17:00:00|2019-01-02 17:00:00
2019-01-02 17:00:00|2019-01-03 17:00:00
2019-01-03 17:00:00|2019-01-04 17:00:00
...
...
2019-12-31 17:00:00|2020-01-01 17:00:00

Could you please help troubleshoot the error in this? Please suggest any optimized way of achieving this.

CREATE TABLE s.dates_2019
(
ts_range_begin timestamp(6),
ts_range_end timestamp(6),

);


 insert into s.dates_2019 (ts_range_begin)
 select 
     to_timestamp('12/31/2018 05:00 PM', 'YYYY-MM-DD HH24:MI:SS') + n.n
 from
  (select rownum n
   from   ( select 1 just_a_column
         from   dual
         connect by level <=
                to_timestamp('12/31/2019 05:00 PM', 'YYYY-MM-DD HH24:MI:SS') 
               -  to_timestamp('12/31/2018 05:00 PM', 'YYYY-MM-DD HH24:MI:SS')  
               + 1
           ) t
  ) n
  where
       to_timestamp('12/31/2018 05:00 PM','YYYY-MM-DD HH24:MI:SS')  + n.n <=  to_timestamp('12/31/2019 05:00 PM','YYYY-MM-DD HH24:MI:SS') 

 insert into s.dates_2019 (ts_range_end)
 select 
     to_timestamp('2019-01-01 05:00 PM', 'YYYY-MM-DD HH24:MI:SS') + n.n
 from
  (select rownum n
   from   ( select 1 just_a_column
         from   dual
         connect by level <=
                to_timestamp('2020-01-01 05:00 PM', 'YYYY-MM-DD HH24:MI:SS') 
               -  to_timestamp('2019-01-01 05:00 PM', 'YYYY-MM-DD HH24:MI:SS')  
               + 1
           ) t
  ) n
  where
       to_timestamp('2019-01-01 05:00 PM','YYYY-MM-DD HH24:MI:SS')  + n.n <=  to_timestamp('2020-01-01 05:00 PM','YYYY-MM-DD HH24:MI:SS')

Error is :- [Error Code: 30081, SQL State: 99999] ORA-30081: invalid data type for datetime/interval arithmetic

Upvotes: 0

Views: 235

Answers (2)

Littlefoot
Littlefoot

Reputation: 143023

How about this?

SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi';

Session altered.

SQL> with dates as
  2    (select date '2019-01-01' + 17/24 + level - 1 datum
  3     from dual
  4     connect by level <= date '2020-01-01' - date '2019-01-01' + 1
  5    ),
  6  staend as
  7    (select datum as start_time,
  8            lead(datum) over (order by datum) as end_time
  9     from dates
 10    )
 11  select start_time,
 12         end_time
 13  from staend
 14  where end_time is not null
 15  order by start_time;

START_TIME       END_TIME
---------------- ----------------
2019-01-01 17:00 2019-01-02 17:00
2019-01-02 17:00 2019-01-03 17:00
2019-01-03 17:00 2019-01-04 17:00
2019-01-04 17:00 2019-01-05 17:00
<snip>
2019-12-30 17:00 2019-12-31 17:00
2019-12-31 17:00 2020-01-01 17:00

365 rows selected.

SQL>

If you want to insert dates into a table, you don't really need a timestamp - date will do.

SQL> create table dates_2019
  2    (ts_range_begin date,
  3     ts_range_end   date
  4    );

Table created.

SQL> insert into dates_2019 (ts_range_begin, ts_range_end)
  2  with dates as
  3    (select date '2019-01-01' + 17/24 + level - 1 datum
  4     from dual
  5     connect by level <= date '2020-01-01' - date '2019-01-01' + 1
  6    ),
  7  staend as
  8    (select datum as start_time,
  9            lead(datum) over (order by datum) as end_time
 10     from dates
 11    )
 12  select start_time,
 13         end_time
 14  from staend
 15  where end_time is not null
 16  order by start_time;

365 rows created.

SQL>

If you want to aggregate weekends, consider using offset in the lead analytic function. That offset depends on day name (Friday). Also, remove weekend days from the result set (line #21, where day not in ('sat', 'sun')).

SQL> insert into dates_2019 (ts_range_begin, ts_range_end)
  2  with dates as
  3    (select date '2019-01-01' + 17/24 + level - 1 datum,
  4            --
  5            to_char(date '2019-01-01' + 17/24 + level - 1,
  6                    'fmdy', 'nls_date_language = english') day
  7     from dual
  8     connect by level <= date '2020-01-01' - date '2019-01-01' + 1
  9    ),
 10  staend as
 11    (select datum as start_time,
 12            day,
 13            lead(datum, case when day = 'fri' then 3
 14                             else 1
 15                        end) over (order by datum) as end_time
 16     from dates
 17    )
 18  select start_time,
 19         end_time
 20  from staend
 21  where day not in ('sat', 'sun')
 22    and end_time is not null;

261 rows created.

SQL> select * from dates_2019 order by ts_range_begin;

TS_RANGE_BEGIN   TS_RANGE_END
---------------- ----------------
2019-01-01 17:00 2019-01-02 17:00
2019-01-02 17:00 2019-01-03 17:00
2019-01-03 17:00 2019-01-04 17:00
2019-01-04 17:00 2019-01-07 17:00     --> aggregated
2019-01-07 17:00 2019-01-08 17:00
2019-01-08 17:00 2019-01-09 17:00
2019-01-09 17:00 2019-01-10 17:00
2019-01-10 17:00 2019-01-11 17:00
2019-01-11 17:00 2019-01-14 17:00     --> aggregated
2019-01-14 17:00 2019-01-15 17:00
2019-01-15 17:00 2019-01-16 17:00
<snip>

Upvotes: 1

EdmCoff
EdmCoff

Reputation: 3586

I think your actual error is because subtracting timestamps returns an interval, whereas you're using the result as a number in CONNECT BY LEVEL. You could cast the timestamps as dates (you might find the answers here useful) or use an interval expression to get the day component between the timestamps.

But if this is your actual SQL and not a simplification, I suggest just using dates in the CONNECT BY (you can still keep timestamps in your table if that's what you want) and doing something like...

CREATE TABLE dates_2019
(
ts_range_begin timestamp(6),
ts_range_end timestamp(6)
);

insert into dates_2019 (ts_range_begin)
 select 
     to_timestamp('2018-12-31 17', 'YYYY-MM-DD HH24') + rownum
 from
     dual
 connect by level <= to_date('2019-12-31 17', 'YYYY-MM-DD HH24') - to_date('2018-12-31 17', 'YYYY-MM-DD HH24')
 ;

update dates_2019 SET ts_range_end = ts_range_begin + 1;

... which I tested in Oracle 18c, but probably works 10g.

Upvotes: 1

Related Questions