Oracle sql query with data type "date"

Question

i have a problem with conversion column with data type "Date".

I have table (I can't change the structure):

id           number(10,0)
time_stamp   date

When i used sql query:

  SQL * FROM t; 

I got:

 id   time_stamp
    1    01.07.19
    2    20.08.19

But i need date with time, so i used:

SELECT  
id
,TO_TIMESTAMP(lr.time_stamp,'DD.MM.YYYY HH24:MI:SS') as dt
FROM t;

I Got:

  id         dt
  1   01.07.19 00:00:00,000000000
  2   20.08.19 00:00:00,000000000

BUT when I use TO_CHAR, I see that there is time.

SELECT  
id
,(TO_CHAR(time_stamp, 'DD.MM.YYYY HH24:MI:SS')) as dt
FROM t

I Got:

  id         dt
  1   01.07.19 17:09:07
  2   20.08.19 21:45:03

And I just need time. I try to use it in the WHERE clause to get results in the given range. But when I use it like this:

SELECT id
FROM t
WHERE
 TO_TIMESTAMP(TO_CHAR(time_stamp,'DD.MM.YYYY HH24:MI:SS')) >= '01.11.2020 06:00:00' 
 AND TO_TIMESTAMP(TO_CHAR(time_stamp,'DD.MM.YYYY HH24:MI:SS')) <= '02.11.2020 06:00:00'

it's not exactly optimized and the query takes a long time. Do you have any advice? I always need a range from yesterday 06:00 to today 06:00

Answer 1

In Oracle, the Date data type is stored in an internal format and contains date and time till seconds precision. What you see in date while selecting data from the table totally depends on the NLS setting of the current session. (NLS_DATE_FORMAT)

So, You will just need to use it as it is in your query as follows:

SELECT ID
  FROM T
 WHERE TIME_STAMP BETWEEN TO_DATE('01.11.2020 06:00:00', 'DD.MM.YYYY HH24:MI:SS') 
                      AND TO_DATE('02.11.2020 06:00:00''DD.MM.YYYY HH24:MI:SS')