Select specific timestamp interval across multiple dates

I have tried searches on Google and various other sites with no luck, even making the search terms as vague as possible.

I have a query with data across multiple days but I only want to select data that has the time between 21:45 and 22:45.

The temp table built with the whole data has the data column that was converted from to_date to to_char so changing it back to to_date or to_timestamp is necessary, I think.

The problem is I have tried both of those and get invalid month errors. For example to_date(complete_date, 'hh24:mi:ss') gives me the error.

I'm not sure how to filter for a timestamp interval without giving a hard coded date.

Many thanks in advance. I am using Oracle Sql and unfortunately I don't have the query at the moment. It's on the computer at work. If a reply comes and I am at work I can reply back with more information.

With details as (
      select to_char(complete_date, 'yyyy-mm-dd hh24:mi:ss') complete_date, 
             to_char(complete_date, 'hh24:mi:ss') as ts from table 
      where complete_date between trunc(sysdate)-30 and trunc(sysdate) )

select * from details where ts between '21:45:00' and '22:45:00'

---

I was able to filter by timestamp by using:

Round(Extract(hour from to_timestamp(complete_date, 'yyyy-mm-dd hh24:mi:ss')) + (extract(minute from to_timestamp(complete_date, 'yyyy-mm-dd hh24:mi:ss'))/60),2) count_ts

Then filter for count_ts between 21.75 and 22.75. This will allow me to get any data between those times no matter the day.

Problem solved.

链接地址: http://www.djcxy.com/p/36942.html

上一篇: SYSDATE和来自列的日期之间的Oracle时差

下一篇: 跨多个日期选择特定时间戳记间隔