CODEWITHSUNDEEP
phpmysql
Saymon
Saymon

Reputation: 520

MySQL query to select some rows when dates and time are in different fields

I have a table for events/parties that store the day, month, year, hour, and minute in different fields for each event, in this way:

+--------+-----+-------+--------+--------+----------+
| event  | day | month |  year  |  hour  |  minute  |
+--------+-----+-------+--------+--------+----------+
| event1 |  2  |   12  |  2015  |   11   |   25     |
| event2 |  3  |    1  |  2016  |   12   |   30     |
| event3 |  4  |    2  |  2016  |   13   |   45     |
+--------+-----+-------+--------+--------+----------+

Using this structure I can do a query for the exact current time in this way:

SELECT * FROM of2ab_jcalpro_events 
WHERE day = " . date("j") . "
AND month = " . date("n") . "
AND year = " . date("Y") . "
AND hour = " . date("G") . "
AND minute = " . date("i") . "
ORDER by minute ASC 
LIMIT 0,3

Now... My problem is how do I select the next three events? I mean from right now the next 3 events? It would be easy if I have a datetime, but I can not change the table structure and this is what I have, any idea?

Let put these values as example:

date("j") = 2
date("n") = 12
date("Y") = 2015
date("G") = 20
date("i") = 45

This mean: 2015-12-02 20:45

So, how to get the next rows after right now?

Upvotes: 0

Views: 50

Answers (3)

Saymon
Saymon

Reputation: 520

With the help of the guys above, specially @shadow I found out the perfect query that works for me under the condition explained above.

SELECT title, day, month, year, hour, minute FROM of2ab_jcalpro_events 
        WHERE 
        STR_TO_DATE(concat(day, ',', month, ',', year, ' ', hour, ':', minute,':00'),'%d,%m,%Y %H:%i:%s') > now()
        order by STR_TO_DATE(concat(day, ',', month, ',', year, ' ', hour, ':', minute,':00'),'%d,%m,%Y %H:%i:%s') asc
        limit 4

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520918

Since you don't have single timestamp column, you can't do a single ORDER BY to get what you want. But you can collectively ORDER BY the combination of the date fields. I added LIMIT 3 to get the 3 events closest to the current time.

ORDER BY year DESC, month DESC, day DESC, hour DESC, minute DESC
LIMIT 3

Upvotes: 1

Shadow
Shadow

Reputation: 34232

Create a mysql date and time string by concatenating the field values and you can compare this to the current time and order by this calculated value.

select * events
where concat(year, '-', month, '-', day, ' ', hour, ':', minute,':00')>=now()
order by concat(year, '-', month, '-', day, ' ', hour, ':', minute,':00') desc limit 3;

If month, day, hour, minute data are not stored in 2 digits, then you may have to convert them to a 2-digit format.

Upvotes: 1

Related Questions