Sorting by Date

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
okee
Forum Newbie
Posts: 5
Joined: Thu Jan 16, 2003 4:48 pm

Sorting by Date

Post by okee »

I have a few rows with the following dates

28/07/2003
25/07/2003
24/07/2003

now if i add the date 27/06/2003 and i do a query with sort by date it returns.

28/07/2003
27/06/2003
25/07/2003
24/07/2003

Is there any way to sort it correctly or do i need to use a different date format ?

okee
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

...

Post by kettle_drum »

Yeah i would just recomend storing all date values as the unix time value as it makes anything you want to do with dates easier.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Or use the DATE field type of your database - never store dates in VARCHAR fields.

Mac
SBukoski
Forum Contributor
Posts: 128
Joined: Wed May 21, 2003 10:39 pm
Location: Worcester, MA

Post by SBukoski »

twigletmac wrote:Or use the DATE field type of your database - never store dates in VARCHAR fields.

Mac
Why? If I'm going to be doing a lot of manipulation, then sure, I definitely don't want to use a Varchar format as it makes it slightly more difficult. But if my only purpose is to display a date, then I don't see a problem. it's actually one less step as you don't have to convert the timestamp to a string representation.

Also, I store any date I want to store in the format YYYY-MM-DD. this gauratees that they will sort properly.

Is there some major, underlying reason you think storing dates as VARCHAr is bad?
sitething
Forum Newbie
Posts: 6
Joined: Sun Jul 27, 2003 7:08 pm

Post by sitething »

Pardon me for butting in, eh! :D But as someone who's been bitten in the ass by this before, I just can't resist.

You've already shown that storing as a varchar does NOT sort properly... at least not without a bit of mojo.

For me, just as a matter of personal preference, if I'm storing a date then I store it as some kind of time-referenced data type. Like a timestamp. Because see you never know what you'll want in the future. Maybe you don't need to do anything other than list the dates now, but someday you may, and when that day comes and you have all these varchars to deal with... you'll regret it. :? I know I did.

Best thing is, make your data type correct and you can't go wrong. But it's up to you really :wink:
Post Reply