How to select rows in TIMESTAMP column in Oracle SQL?-CodePudding

I have table in Oracle SQL like below:

col1
-------------
22/01/15 16:45:05,657432556
21/12/22 11:01:33,234543456
21/10/13 10:15:45,437483674
21/11/12 11:22:01,315432223

Above column "col1" is as TIMESTAMP and I would like to

1. select only these dates between 21/10/01 and 21/12/01
1. create new column "col2" with converted TIMESTAMP to date

So as a result I would like to have something like below:

col1                        | col2
----------------------------------------
21/10/13 10:15:45,437483674 | 21/10/13
21/11/12 11:22:01,315432223 | 21/11/12

How can I do that in Oracle SQL ?

CodePudding user response：

For the second part, you can add a virtual column - so you don't have to maintain both values - which converts the timestamp to a date, either preserving the time part:

alter table your_table add (col2 date generated always as (cast(col1 as date)));

COL1                        COL2
--------------------------  -------------------
2022-01-15 16:45:05.657432  2022-01-15 16:45:05
2021-12-22 11:01:33.234543  2021-12-22 11:01:33
2021-10-13 10:15:45.437483  2021-10-13 10:15:45
2021-11-12 11:22:01.315432  2021-11-12 11:22:01

or truncating to midnight:

alter table your_table add (col2 date generated always as (trunc(cast(col1 as date))));

COL1                        COL2
--------------------------  -------------------
2022-01-15 16:45:05.657432  2022-01-15 00:00:00
2021-12-22 11:01:33.234543  2021-12-22 00:00:00
2021-10-13 10:15:45.437483  2021-10-13 00:00:00
2021-11-12 11:22:01.315432  2021-11-12 00:00:00

To restrict the dates, you need to determine what 'between' means to you. As you're using the first day of the month I would assume you really want all data in October and November, with nothing actually from December 1st; in which case:

select * from your_table
where col1 >= timestamp '2021-10-01 00:00:00'
and col2 < timestamp '2021-12-01 00:00:00'

COL1                        COL2
--------------------------  -------------------
2021-10-13 10:15:45.437483  2021-10-13 00:00:00
2021-11-12 11:22:01.315432  2021-11-12 00:00:00

If you want to include exactly midnight on the first then make that <=. To include the whole of that day make is < midnight on the 2nd.

If you want the results in a specific output format then change your client/session settings, or explicitly convert to strings (for display only):

select to_char(col1, 'YY/MM/DD HH24:MI:SS,FF9') as col1,
  to_char(col2, 'YY/MM/DD') as col2
from your_table
where col1 >= timestamp '2021-10-01 00:00:00'
and col2 < timestamp '2021-12-01 00:00:00'

COL1                         COL2
---------------------------  --------
21/10/13 10:15:45,437483674  21/10/13
21/11/12 11:22:01,315432223  21/11/12

db<>fiddle

CodePudding user response：

Table contents:

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               TIMESTAMP(6)
 COL2                                               DATE

SQL> select * From test order by col1;

COL1                           COL2
------------------------------ -------------------
13.10.21 10:15:45,437484
22.12.21 11:01:33,234543
15.01.22 16:45:05,657433
15.01.22 16:45:05,657433

SQL>

For 1, one option is to use a timestamp literal:

SQL> select * From test where col1 between timestamp '2021-10-01 00:00:00.000'
  2                                    and timestamp '2021-12-01 00:00:00.000';

COL1                           COL2
------------------------------ -------------------
13.10.21 10:15:45,437484

SQL>

For 2, use CAST:

SQL> update test set col2 = cast (col1 as date);

4 rows updated.

SQL> select * from test order by col1;

COL1                           COL2
------------------------------ -------------------
13.10.21 10:15:45,437484       13.10.2021 10:15:45
22.12.21 11:01:33,234543       22.12.2021 11:01:33
15.01.22 16:45:05,657433       15.01.2022 16:45:05
15.01.22 16:45:05,657433       15.01.2022 16:45:05

SQL>