Date range query in a given time

Asked

Viewed 88 times

0

I have a query that takes all the data between days.

However, I would like him to take this data only in an interval of hours (from 08:00 to 17 for example).

My query currently looks like this: 

select TO_CHAR(FROM_TZ(TO_TIMESTAMP(TO_CHAR(DT, 'yyyy/MM/dd 
               hh24:Mi:ss'),'yyyy/mm/dd hh24:mi:ss'), 'UTC') AT TIME ZONE 
               'AMERICA/SAO_PAULO', 'dd/mm/yyyy') as Data, 
    AREA, VALOR 
    from TABELA where AREA = 'MINHAAREA' and 
    CODE = 'MEUCODE' and 
    DT >= to_date('01/05/2019 00:00:00', 'dd/mm/yyyy hh24:mi:ss') and 
    DT <= to_date('01/06/2019 00:00:00', 'dd/mm/yyyy hh24:mi:ss') 
    order by DT
sql database oracle

  • Not only: DT >= to_date('01/05/2019 08:00:00', 'dd/mm/yyyy hh24:mi:ss') and DT <= to_date('01/06/2019 17:00:00', 'dd/mm/yyyy hh24:mi:ss') ?

  • No, because then he would get all the data of the days interval from '01/05/2019 08:00:00' to 01/06/2019 17:00:00. What I want is for him to take the data of each day in that interval of hours.

  • Then put two more conditions specifying the time, regardless of the date between 08:00 and 17:00.

  • OR AN EXTRACT(HOUR FROM DT) BETWEEN 8 AND 17.

1 answer

0


Use to_char and extract the time

select TO_CHAR(FROM_TZ(TO_TIMESTAMP(TO_CHAR(DT, 'yyyy/MM/dd 
               hh24:Mi:ss'),'yyyy/mm/dd hh24:mi:ss'), 'UTC') AT TIME ZONE 
               'AMERICA/SAO_PAULO', 'dd/mm/yyyy') as Data, 
    AREA, VALOR 
    from TABELA where AREA = 'MINHAAREA' and 
    CODE = 'MEUCODE' and 
    TO_CHAR(DT,'HH24MI') BETWEEN '0800' AND '1759'
    order by DT

Browser other questions tagged sql database oracle

You are not signed in. Login or sign up in order to post.