CODEWITHSUNDEEP
mysqlsql
Flak
Flak

Reputation: 2670

Get row between time

I have rows every 3 hours, like: 

796 4   0.9852  2015-04-12 13:00:00
797 4   0.1139  2015-04-12 16:00:00
798 4   0.5736  2015-04-12 19:00:00
799 4   0.0486  2015-04-12 22:00:00
800 4   0.3138  2015-04-13 01:00:00
801 4   0.9298  2015-04-13 04:00:00
802 4   0.9713  2015-04-13 07:00:00

...

1100    5   0.5677  2015-04-12 13:00:00
1101    5   0.8268  2015-04-12 16:00:00
1102    5   0.5761  2015-04-12 19:00:00
1103    5   0.2263  2015-04-12 22:00:00
1104    5   0.8859  2015-04-12 01:00:00

How to get 16:00:00 and 19:00:00 if Current time is between those.

Upvotes: 0

Views: 29

Answers (2)

Brian DeMilia
Brian DeMilia

Reputation: 13248

select *
  from time_table x
  join (select user_id, min(time_column) as time_column
          from time_table
         where time_column >= current_timestamp
         group by user_id) y
    on x.user_id = y.user_id
   and x.time_column = y.time_column
union all
select *
  from time_table x
  join (select user_id, max(time_column) as time_column
          from time_table
         where time_column < current_timestamp
         group by user_id) y
    on x.user_id = y.user_id
   and x.time_column = y.time_column

Upvotes: 1

Jordi Llull
Jordi Llull

Reputation: 810

Assuming there's always a row after and row a before the current time you can do this:

 (
   SELECT *
     FROM mytable
    WHERE `date` <= NOW()
      AND user_id = 4
 ORDER BY `date` DESC 
    LIMIT 1   
 )
UNION
 (
   SELECT *
     FROM mytable
    WHERE `date` > NOW()
      AND user_id = 4
 ORDER BY `date` ASC 
    LIMIT 1   
 )

Upvotes: 0

Related Questions