Select two hour fields where one of them goes past midnight

Asked

Viewed 204 times

0

I have two tables with the following scheme:

data       | hora ini   | hora fim
-----------|------------|---------
16/08/2016 | 1115       | 1319
16/08/2016 | 1320       | 1419
16/08/2016 | 1420       | 90

Being that in the second table, it already has the records inserted every half hour, as in the example below:

data       | hora 
-----------|-------
16/08/2016 | 1380
16/08/2016 | 1410
17/08/2016 | 0       
17/08/2016 | 30       
17/08/2016 | 60  
17/08/2016 | 90
17/08/2016 | 120

What I need is that when I update a record from the second table, for example the midnight record, it does a search if there is no record in the first table that "passes between 00:00 and 00:30". That is, if there is a period pointed in the first table that is from 22:00 until 01:00, it should appear in this select.

I can assemble a BETWEEN to return the records, however, when it passes of the midnight he Buga therefore the final hour turns out to be less than the initial.

sql oracle

  • If date 2 is higher, BETWEEN( D1, D2 ) if date 2 is lower, NOT BETWEEN( D2, D1 )

  • It doesn’t work like this. I believe my solution looks like this: http://prntscr.com/c7gxs0

  • the solution is not to search for date & time ? Something like ... date between to_date('16/08/2016 23:00','dd/mm/yyyy hh24:mi') and to_date('1708/2016 00:30','dd/mm/yyyy hh24:mi:mi')

  • no, friend, because the date in the to_date parameter comes from the same field, that is, they are not different but equal.

1 answer

0


I managed to make this work with the following query:

SELECT * FROM
(
-- This inline view gets everything in your table, plus the start_datetime and end_datetime as calculated from your various fields.
SELECT your_table.*, start_date + ( start_time / 1440 ) start_datetime, start_date + case when end_time < start_time then end_time + 1440 else end_time end end_datetime FROM your_table
) 
WHERE start_datetime between ... whatever date range you want ...
AND end_datetime between ... whatever date range you want ...

This way I managed to make all the selects I needed.

Browser other questions tagged sql oracle

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