Select two time fields where one of them moves past midnight

Question

Select two time fields where one of them moves past midnight

Navigation

#1 by (0 votes)

0

I have two tables with the following schema:

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

In the second table, it already has the records entered 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 "runs between 00:00 and 00:30 ". That is, if there is a period indicated in the first table that is from 10:00 p.m. to 1:00 p.m., it should appear in this select.

I can mount a BETWEEN to return the records, however, when it passes midnight it buga because the final hour ends up being smaller than the initial one.

sql oracle

asked by anonymous 17.08.2016 / 22:26

1 answer

How can I make a oauth2 authentication of a native android application with a website developed with wordpress? could not execute statement

score 0 · Accepted Answer

I was able 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 ...

In this way, I was able to do all the selects I needed.