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
– Tim Biegeleisen, Commented Dec 3, 2015 at 1:45
yes, I am using MySQL, with the 3 next events I mean how to get the next rows according the current datetime — Saymon
– Saymon, Commented Dec 3, 2015 at 1: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
– Saymon, Commented Dec 3, 2015 at 1:57

Shadow · Accepted Answer · 2015-12-03 01:52:35Z

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 3, 2015 at 1:52

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Saymon Over a year ago

month, day, hour don't use the cero in front, only one digit when they can

Shadow Over a year ago

Then you may have to use str_to_date() function dev.mysql.com/doc/refman/5.7/en/…

Saymon Over a year ago

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();

Shadow Over a year ago

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.

Tim Biegeleisen · Accepted Answer · 2015-12-03 01:57:21Z

1

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

answered Dec 3, 2015 at 1:57

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Comments

Saymon · Accepted Answer · 2015-12-04 01:05:37Z

0

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

answered Dec 4, 2015 at 1:05

Saymon

5209 silver badges20 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related