[SOLVED] Get MySQL to parse dates

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
voltrader
Forum Contributor
Posts: 223
Joined: Wed Jul 07, 2004 12:44 pm
Location: SF Bay Area

[SOLVED] Get MySQL to parse dates

Post 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?
Last edited by voltrader on Sun Feb 13, 2005 12:27 am, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

not easily.. have fun.. I'd convert all those to datetime's and tell the selection to make that format..
User avatar
voltrader
Forum Contributor
Posts: 223
Joined: Wed Jul 07, 2004 12:44 pm
Location: SF Bay Area

Post by voltrader »

Ugh... that's what I thought. Thanks
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

actually.. if you have 4.1.1+ str_to_date() should be able to do it..
User avatar
voltrader
Forum Contributor
Posts: 223
Joined: Wed Jul 07, 2004 12:44 pm
Location: SF Bay Area

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

nah.. you can ask MySQL to return the datetime as unix-timestamp.. then just tell date() the proper format.. ;)
User avatar
voltrader
Forum Contributor
Posts: 223
Joined: Wed Jul 07, 2004 12:44 pm
Location: SF Bay Area

Post by voltrader »

great thanks... SOLVED
Post Reply