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

Question

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?

What do you mean by next 3 events? Are you using MySQL in your programming stack? — Tim Biegeleisen, Dec 03 '15 at 01:45
yes, I am using MySQL, with the 3 next events I mean how to get the next rows according the current datetime — Saymon, Dec 03 '15 at 01:49
I updated the post @TimBiegeleisen I hope it helps, yes I know this is hard how it is, but the table was designed putting the dates in this way so I only have to find out how to extract this info properly :( — Saymon, Dec 03 '15 at 01:57
you can use sub query, first you concat your datetime fields into one field datetime, and then you can select these subquery.. — check, Dec 03 '15 at 02:04
Possible duplicate of [What is the SQL for 'next' and 'previous' in a table?](http://stackoverflow.com/questions/203302/what-is-the-sql-for-next-and-previous-in-a-table) — Nathan Tuggy, Dec 03 '15 at 04:00

score 1 · Answer 1 · answered Dec 03 '15 at 01:52

1

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.

answered Dec 03 '15 at 01:52

Shadow

33,525
10
51
64

month, day, hour don't use the cero in front, only one digit when they can – Saymon Dec 03 '15 at 02:09
Then you may have to use str_to_date() function http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_str-to-date – Shadow Dec 03 '15 at 07:25
Can you apply the str_to_date() in my query? I am trying on this following way but it is not working (I did not put the order by yet because I can't even make this part :s ) : SELECT * FROM of2ab_jcalpro_events where STR_TO_DATE(concat(year, '-', month, '-', day, ' ', hour, ':', minute,':00'), '%Y-%n-%j %G:%i:%s') >= now(); – Saymon Dec 03 '15 at 14:29
I cannot, since I do not know what data are in the fields. The linked documentation describes the format strings, which are not the same as in php. – Shadow Dec 03 '15 at 17:41

score 1 · Answer 2 · answered Dec 03 '15 at 01:57

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

score 0 · Answer 3 · answered Dec 04 '15 at 01:05

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

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

3 Answers3