CODEWITHSUNDEEP
mysqlsqldatetime
Kin
Kin

Reputation: 4596

How to get current and next datetime record depending on current datetime?

I'm working on a small project which shows tv programs schedule. The main problem is that I need to show 4 programs including current program. The problem is that I really don’t know if the first program from the result is current or may be just the next as previous is still in place...

For example:

+----+------------+----------+
| id | date       | time     |
+----+------------+----------+
| 38 | 2016-11-15 | 11:55:00 |
| 39 | 2016-11-15 | 13:00:00 |
| 40 | 2016-11-15 | 13:30:00 |
| 41 | 2016-11-15 | 14:00:00 |
| 42 | 2016-11-15 | 14:30:00 |
| 43 | 2016-11-15 | 15:00:00 |
| 44 | 2016-11-15 | 15:30:00 |
| 45 | 2016-11-15 | 16:30:00 |
| 46 | 2016-11-15 | 18:30:00 |
| 47 | 2016-11-15 | 19:20:00 |
+----+------------+----------+

So basically I need to get 4 rows, first should be currently showing if exists.

Upvotes: 1

Views: 71

Answers (2)

e4c5
e4c5

Reputation: 53734

You haven't been very clear about what time slot belongs to a program, so I am guessing that this is what you are looking for

SELECT * FROM shows where id >= 
  (SELECT id FROM shows 
   WHERE `time` <= '13:33' AND `date` = '2016-11-15' 
   ORDER BY `time` DESC LIMIT 1) 
LIMIT 4;

Upvotes: 2

Salman Arshad
Salman Arshad

Reputation: 272096

Fairly straight forward:

SELECT *
FROM shows
WHERE TIMESTAMP(date, time) >= (
    SELECT TIMESTAMP(date, time)
    FROM shows
    WHERE TIMESTAMP(date, time) <= CURRENT_TIMESTAMP
    ORDER BY TIMESTAMP(date, time) DESC
    LIMIT 1
)
ORDER BY TIMESTAMP(date, time)
LIMIT 4

Result when current time is 2016-11-15 12:59:59:

+------+------------+----------+
| id   | date       | time     |
+------+------------+----------+
|   38 | 2016-11-15 | 11:55:00 |
|   39 | 2016-11-15 | 13:00:00 |
|   40 | 2016-11-15 | 13:30:00 |
|   41 | 2016-11-15 | 14:00:00 |
+------+------------+----------+

Result when current time is 2016-11-15 13:00:00:

+------+------------+----------+
| id   | date       | time     |
+------+------------+----------+
|   39 | 2016-11-15 | 13:00:00 |
|   40 | 2016-11-15 | 13:30:00 |
|   41 | 2016-11-15 | 14:00:00 |
|   42 | 2016-11-15 | 14:30:00 |
+------+------------+----------+

TIMESTAMP function is used to combine date and time.

Upvotes: 1

Related Questions