MySQL explode array of values to use to ORDER the result by

Question

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.

Check out this link... http://stackoverflow.com/questions/1096679/can-mysql-split-a-column — Ben Kirchner, Sep 23 '12 at 23:09
Couldn't you make a new table which stores the dates, one date per data row and join with your main table? It would be normalized so. — yunzen, Sep 23 '12 at 23:15
Well, normally I would do something like that and not have an array of dates or any information - I am highly against doing things that way - however in this circumstance it is just for a simple website that won't be used much, (It's really just a CMS for a single user to manage their small business). — Dylan Cross, Sep 23 '12 at 23:18

LSerni · Accepted Answer · 2012-09-23T23:21:59.410

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.

Well, I gave some thoughts about the rest. Not very promising, I'm afraid. — LSerni, Sep 23 '12 at 23:22
Ok, well thank you for the additional code, I may give that a go, but I also may decide just not to bother as well. I'll quote what I said a minute ago to someone's response, " Well, normally I would do something like that and not have an array of dates or any information - I am highly against doing things that way - however in this circumstance it is just for a simple website that won't be used much, (It's really just a CMS for a single user to manage their small business)." However, would there be a simple way to get the last date from the array, similarly to how you get the first row — Dylan Cross, Sep 23 '12 at 23:28
If not, I could always flip the array around when I insert it to the database so the "last" date is always the first result, I guess that may be the most plausible solution in this case. — Dylan Cross, Sep 23 '12 at 23:29
Yes, just use SUBSTRING_INDEX(date_of,',',-1) and it will get you the last element. Or the first if you have only one. — LSerni, Sep 23 '12 at 23:31

score 1 · Answer 2 · answered Sep 23 '12 at 23:12

1

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.

answered Sep 23 '12 at 23:12

Aatch

1,846
10
19

Ok thanks, I suppose this would be fine to do, but I'm just going to use the proper way just to be safe, thanks though! – Dylan Cross Sep 23 '12 at 23:16

MySQL explode array of values to use to ORDER the result by

2 Answers2