Reputation: 5986
I have to store a different number of values in a column called, date_of, for the date of an event. There can be multiple dates that it takes place, for instance Oct 1st, 2nd, and 6th. And I need to store that in that column as three different dates. Now I do this like this:
1350619200, 1350705600, 1350792000
So what I want to be able to do is order the mysql result by the date(s). The first date should be the one that it gets ordered by, however if there are multiple dates as shown above, if the date is pass then it should go to the second date, then third date etc.
I'm not sure how to get started with this, my main priority is to just order the results by the first date in the array, the other part of my question isn't as important.
Upvotes: 0
Views: 883
Reputation: 57463
If you order by the first field, which is always set, then you can try extracting the field:
ORDER BY SUBSTRING_INDEX(date_of, ',', 1);
Actually, you could order by date_of
, since lexicographic order is maintained. You'd be in trouble if you wanted to order by the last subfield, but since you want the first...
Now "if the first element of the date is past" is a bit tricky.
ORDER BY CASE WHEN SUBSTRING_INDEX(date_of, ',', 1) > UNIX_TIMESTAMP()
THEN SUBSTRING_INDEX(date_of, ',', 1)
ELSE ... END
could be a start, but then if there is no second subfield? If we iterate SUBSTRING_INDEX, we get again the first field.
You would have to iterate the CASE
itself. Shudder
ORDER BY CASE WHEN SUBSTRING_INDEX(date_of, ',', 1) > UNIX_TIMESTAMP()
THEN SUBSTRING_INDEX(date_of, ',', 1)
ELSE
CASE WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(date_of, ',', 2),',',-1) > UNIX_TIMESTAMP()
THEN SUBSTRING_INDEX(SUBSTRING_INDEX(date_of, ',', 2),',',-1)
ELSE
CASE WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(date_of, ',', 3),',',-1) > UNIX_TIMESTAMP()
THEN SUBSTRING_INDEX(SUBSTRING_INDEX(date_of, ',', 3),',',-1)
ELSE 999999999
END
END
END
I can see there's no great performances to be had from this structure. I would strongly advise for having the three dates in three different columns, or the model reengineered to have dates in a separate table.
Upvotes: 3
Reputation: 1856
If you store it as a string (VARCHAR
) then you can just do a ORDER BY
on the query. MySQL will do a lexographic sort on the string. If you are storing unix timestamps (which you appear to be), then this will work until 20th November 2286 5:46:40 PM UTC.
It works because all the timestamps are likely to be the same length, 10 digits. You just have to make sure that keep the format of the list consistent. I'd go for <number>,<number,...
no spaces.
Upvotes: 1