Page 1 of 1

[SOLVED] Get MySQL to parse dates

Posted: Fri Feb 11, 2005 3:06 pm
by voltrader
I had previously ordered by timestamp, which worked until I realized that updating a record also changed the timestamp. :oops:

So I have to fall back to ordering by date of insertion, which is stored as:

Thursday 10th of February 2005 10:52:54 AM

However, these are stored as VARCHAR, which I did very early on -- a mistake, I know!

I'd like to order by them on retrieval.

Is there any way to get mysql to do the parsing instead of PHP?

Posted: Fri Feb 11, 2005 3:11 pm
by feyd
not easily.. have fun.. I'd convert all those to datetime's and tell the selection to make that format..

Posted: Fri Feb 11, 2005 3:17 pm
by voltrader
Ugh... that's what I thought. Thanks

Posted: Fri Feb 11, 2005 3:17 pm
by feyd
actually.. if you have 4.1.1+ str_to_date() should be able to do it..

Posted: Fri Feb 11, 2005 4:46 pm
by voltrader
Thanks... I'm running 3.23, so can't use str_to_date.

I think I ought to do this right, at least from here on out.

msql datetime default format is 0000-00-00 00:00:00 which corresponds to

Code: Select all

$date=date("Y:m:d H:i:s");
in PHP.

If I wanted to format as

Thursday 10th of February 2005 10:52:54 AM

on retrieval, I should I use mktime nested in date() to parse dates into days?

Posted: Fri Feb 11, 2005 4:57 pm
by feyd
nah.. you can ask MySQL to return the datetime as unix-timestamp.. then just tell date() the proper format.. ;)

Posted: Sun Feb 13, 2005 12:26 am
by voltrader
great thanks... SOLVED