create table solution1 (
ts timestamp(6) not null
)Table created.
create index idx_solution1 ON solution1 (ts)Index created.
create table solution2 (
ts timestamp(6) not null
)Table created.
create index idx_solution2 ON solution2 (to_number(to_char(ts,'YYYYMMDD')))Index created.
create table solution3 (
ts timestamp(6) not null
)Table created.
create index idx_solution3 ON solution3 (trunc(ts))Index created.
create table solution4 (
ts timestamp(6) not null,
ts_yyyymmdd number(8,0) not null
)Table created.
create index idx_solution4 ON solution4 (ts_yyyymmdd)Index created.
insert into solution1 (select timestamp '2019-01-01 00:00:00'+NUMTODSINTERVAL(rownum,'SECOND') from dual connect by level <= 10)10 row(s) inserted.
insert into solution2 (select ts from solution1)10 row(s) inserted.
insert into solution3 (select ts from solution1)10 row(s) inserted.
insert into solution4 (select ts,to_number(to_char(ts,'YYYYMMDD')) as ts_yyyymmdd from solution1)10 row(s) inserted.
commitStatement processed.
select * from solution1
where ts >= TIMESTAMP '2019-01-01 00:00:00' and ts < TIMESTAMP '2019-01-02 00:00:00'| TS | 01-JAN-19 12.00.01.000000 AM | 01-JAN-19 12.00.02.000000 AM | 01-JAN-19 12.00.03.000000 AM | 01-JAN-19 12.00.04.000000 AM | 01-JAN-19 12.00.05.000000 AM | 01-JAN-19 12.00.06.000000 AM | 01-JAN-19 12.00.07.000000 AM | 01-JAN-19 12.00.08.000000 AM | 01-JAN-19 12.00.09.000000 AM | 01-JAN-19 12.00.10.000000 AM |
|---|
select * from solution2
where to_number(to_char(ts,'YYYYMMDD'))
between to_number(to_char(DATE '2019-01-01','YYYYMMDD'))
and to_number(to_char(DATE '2019-01-02','YYYYMMDD'))| TS | 01-JAN-19 12.00.01.000000 AM | 01-JAN-19 12.00.02.000000 AM | 01-JAN-19 12.00.03.000000 AM | 01-JAN-19 12.00.04.000000 AM | 01-JAN-19 12.00.05.000000 AM | 01-JAN-19 12.00.06.000000 AM | 01-JAN-19 12.00.07.000000 AM | 01-JAN-19 12.00.08.000000 AM | 01-JAN-19 12.00.09.000000 AM | 01-JAN-19 12.00.10.000000 AM |
|---|
select * from solution3
where trunc(ts)
between trunc(TIMESTAMP '2019-01-01 00:00:00')
and trunc(TIMESTAMP '2019-01-02 00:00:00')| TS | 01-JAN-19 12.00.01.000000 AM | 01-JAN-19 12.00.02.000000 AM | 01-JAN-19 12.00.03.000000 AM | 01-JAN-19 12.00.04.000000 AM | 01-JAN-19 12.00.05.000000 AM | 01-JAN-19 12.00.06.000000 AM | 01-JAN-19 12.00.07.000000 AM | 01-JAN-19 12.00.08.000000 AM | 01-JAN-19 12.00.09.000000 AM | 01-JAN-19 12.00.10.000000 AM |
|---|
select * from solution4 where ts_yyyymmdd >= 20190101 and ts_yyyymmdd <= 20190101| TS | TS_YYYYMMDD | 01-JAN-19 12.00.01.000000 AM | 20190101 | 01-JAN-19 12.00.02.000000 AM | 20190101 | 01-JAN-19 12.00.03.000000 AM | 20190101 | 01-JAN-19 12.00.04.000000 AM | 20190101 | 01-JAN-19 12.00.05.000000 AM | 20190101 | 01-JAN-19 12.00.06.000000 AM | 20190101 | 01-JAN-19 12.00.07.000000 AM | 20190101 | 01-JAN-19 12.00.08.000000 AM | 20190101 | 01-JAN-19 12.00.09.000000 AM | 20190101 | 01-JAN-19 12.00.10.000000 AM | 20190101 |
|---|