CODEWITHSUNDEEP
mysqlsql
MightyPork
MightyPork

Reputation: 18861

Query to select intervals in day

I have table something like this (there's also "device_id" and "timestamp" columns)

day  | interval  |  value
----------------------------
1    | 14        |  63  // start of a day
1    | 14        |  83
1    | 14        |  73

1    | 15        |  23
1    | 15        |  33
1    | 15        |  50

2    | 16        |  23  // start of a day
2    | 16        |  33
2    | 16        |  50

I want to select all intervals in a day. That is simple.

However, an interval can start a bit before a day flips, or end a bit past:

day  | interval  |  value
----------------------------
7    | 14        |  63
7    | 14        |  83
8    | 14        |  73  // start of a day

8    | 15        |  23
8    | 15        |  33
8    | 15        |  50

8    | 16        |  23
8    | 16        |  33
9    | 16        |  50  // start of a day

Now I'd like to select all three intervals - or even better intervals that are mostly in that day.

SELECT ... WHERE day = 8

Gives me only parts of the start/end intervals (14, 16). That's useless, I need the complete intervals.

If there's no solution, I'll just do three queries, but there might be some SQL trick I'm not aware of?

It's MySQL, called from PHP.

More visually:

  day 7           | day 8             | day 9
------------------+-------------------+---------------
   ###13###  ###14###  ###15###  ###16###   ###17###
        ...  63 83 73  23 33 50  23 33 50   ...

I want all values in day 8 -> intervals 14, 15, 16

Upvotes: 0

Views: 282

Answers (2)

Strawberry
Strawberry

Reputation: 33945

SELECT DISTINCT y.* 
  FROM my_table x 
  JOIN my_table y 
    ON y.some_column = x.some_column 
 WHERE x.some_other_column = 8;

Upvotes: 0

Niels Keurentjes
Niels Keurentjes

Reputation: 41958

I think you are looking for this:

SELECT * FROM intervals 
WHERE interval IN (
    SELECT DISTINCT interval FROM intervals WHERE day = 8)

This selects all interval data where at least one of the entries for that interval occurs in day 8. The subquery determines which unique intervals happen in the day, which is then used by the outer query to select their specifics.

Upvotes: 1

Related Questions