CODEWITHSUNDEEP
sqloracle-databasedatetime
Ateev
Ateev

Reputation: 15

Add date to query

I want to add time series to my database table autogenerated. Suppose the column values should be

How do I do this in Oracle db?

Upvotes: 1

Views: 60

Answers (1)

ekochergin
ekochergin

Reputation: 4129

You can generate data like this recursively using hierarchical query

select to_date('23-07-2021 00:00:00', 'dd-mm-yyyy hh24:mi:ss') + level / 24 / 60 * 5 
  from dual 
connect by level <= 1000

Where

  • to_date('23-07-2021 00:00:00', 'dd-mm-yyyy hh24:mi:ss') - is the start date
  • level / 24 / 60 * 5 - 5 Minutes step.
  • connect by level <= 1000 - a dummy limit has no other reason other than to stop recursion at some point

UPD. Additional reuirement from OP: "This just creates a temporary table. I wanted to insert these values to a new table. And the value should just be till current_date + 1"

create table ek_test as 
select to_date('23-07-2021 00:00:00', 'dd-mm-yyyy hh24:mi:ss') + level / 24 / 60 * 5 date_col
from dual 
connect by to_date('23-07-2021 00:00:00', 'dd-mm-yyyy hh24:mi:ss') + level / 24 / 60 * 5 < sysdate + 1

Upvotes: 2

Related Questions