Page 1 of 1

Sort varchar Times Correctly

Posted: Sat May 16, 2009 9:47 pm
by Skara
:banghead: So I maybe designed this a little weird, but I really like the way it works, so I'm not going to change it. Here's the deal, though...
I have:
(int) date [timestamp]
(varchar) start
(varchar) end

where 'start' and 'end' are something like "1:30 pm" "10:00 pm" "08:00 am"
Sometimes it may say something different like "TBA" or "N/A" or "blahdeblah." (So like I said, varchar is what I really want)

NOW.... how might I sort these within the query? Because ORDER BY `date` ASC, `start` ASC doesn't work... It thinks 8 is > 2, so 2pm comes before 8am.

I know I can run a custom sort that uses strtotime(), but I'd rather do this in the query if possible. Is there a way to either sort times or cast the time into a timestamp?
really bad example of what I mean:
ORDER BY MAKETIME(`start`) ASC; [or MAKETIME(`date`+`start`)]??

Re: Sort varchar Times Correctly

Posted: Sat May 16, 2009 11:38 pm
by John Cartwright
so I'm not going to change it.
That attitude will not get you very far. You already figured out is a bad design, so I can only assume your on a deadline or are lazy. The problem how you are storing the fields is how strings are converted to integers when they contain non-numeric data. For the love of me, I can't remember if the remaining string is removed after the first non-numeric character, or if it simply strips all non-numeric data. Regardless, both cases make this very undesirable and make your calculation very difficult and/or impossible.

By the time you figure it out, surely you could have just improved the design.

Re: Sort varchar Times Correctly

Posted: Sun May 17, 2009 2:37 am
by Benjamin
Skara wrote::banghead: So I maybe designed this a little weird, but I really like the way it works, so I'm not going to change it.
You have designed it wrong, and your efforts to "make it work" will be your downfall. So you may want to :banghead: until it hurts, then learn the correct methodology.

Re: Sort varchar Times Correctly

Posted: Sun May 17, 2009 6:02 pm
by Skara
Ok, ok, point taken. :oops: Sry.
Too much :drunk: when I wrote it, maybe.

Anyway, given what I need to do, what might be a better way of going about this?
I really do want the user side of things to look like this, though, however I do the backend:
[__date_dropdowns_w/_fancy_calendar__] [__start_time__] [__end_time__]
Where start and end can be varchar... Problem is, sometimes there is no start or end time except something vague, or possibly a variable time.

And I didn't really mean I designed it bad (though in hindsight maybe I did). I just meant weird, as what I need to store is kinda weird...

Can someone give me pointer in the right direction? I'm at a loss as to how to do this really well.

Re: Sort varchar Times Correctly

Posted: Sun May 17, 2009 8:46 pm
by John Cartwright
Use datetime fields.

Re: Sort varchar Times Correctly

Posted: Tue May 19, 2009 10:11 pm
by Skara
...Ok, the time field may or may not actually have a time in it, though. I think having two fields for one item of data is a little redundant. What you're suggesting is either:

(datetime)start
(varchar)start
(datetime)end
(varchar)end
Then you have to check to see if the varchar fields are empty, and if so use the date, if not... (???)

OR

(datetime)start
(varchar)start
(int)start_custom
(datetime)end
(varchar)end
(int)end_custom
Then instead of checking if the varchar == '', you check to see if the int is true/false.

That seems like a far worse solution than what I have:
(varchar)start
(varchar)end

:|

Re: Sort varchar Times Correctly

Posted: Tue May 19, 2009 11:04 pm
by John Cartwright
You either need to accept your design with your datatypes, or normalize your data into the appropriate data types. I can't really offer you much advise because I don't understand the logic behind waht you are trying to do.

From what I've gotten, you are trying to order rows based on their start time, but some of the rows do not have a time set yet? Is it just the time that is not set, but the date is known? If thats the case you can still just have 2 fields, start_time and end_time and leave the time blank, i.e. 2009-05-10 00:00:00.

It might be better to seperate the datetime field into, date and time fields instead to make the missing times cleaner. It is very important you keep your data integrity otherwise you will run into your original problem.

Re: Sort varchar Times Correctly

Posted: Wed May 20, 2009 1:00 pm
by Skara
Yes, sorry, maybe my logic is a little unclear. You're right, though, in that sometimes the date is known and the time is not.

I'm coding a (very) simple app to track open shifts for more-or-less freelance employees. They can log on and look at what's available and call or email in what they would like. Very very simple premise.
In order to better organize/show/offer what's available, some shifts may be entered before a time is firmly set. For example, there may be a shift open on saturday, but it may start at either 7 or 8.
Therefore, rather than entering [1-2-09][8:00 am][5:00 pm], the manager may enter [1-2-09][7 or 8][TBA].

Ordinarily there will be a time listed, but not always. I need someway to sort the times easily. The exceptions will simply not be sorted correctly, and I'm ok with that.