PHP and MySQL date sorting problem?

[Anon]

Hello,

I've recently noticed a problem when calling a number of records from a database and having them sorted by date.

the SQL string: "SELECT * FROM postings ORDER BY post_date DESC"

gives the following result:

982178817
980959050
980958412
980958251
980958103
980957147
976306934
975437646
974731505
972568916
971212440
967127673
966454788
1000225431
1000124627

loading all the dates into a php array and using the rsort() function also gives this result. it seems to be treating the numbers that have gone above 1,000,000,000 as LESS than those that are under 1,000,000,000. Has anyone else noticed this, or am I just doing something wrong?

Thanks,
Casey

[gid__]

Dates are sorted fine for me, with mysql 3.23.42, I'm using signed bigint's. Make sure that your post_date field is an integer, not a varchar or other text field. Because it looks like your fields are sorted alphabetically not numberically.

If it is a text field, then you should be able to do an alter table and change it to bigint's without data loss. I'd back up the table just in case though.

[toma42]

Steve's right, but why aren't you storing date data in a datetime field? I think you need to take a close look at your reasoning for this then rewrite your code to use appropriate data types.

[Anon]

well, it was infact a text field and not an integer value, i hadn't noticed this previously, as i wasn't the original creator of the database.

Thanks,
Casey

[gid__]

gotta love fixing/maintaining other people's code 🙂

On a side note in reponse to Tom:

I always use bigint's for storing datetimestamps. PHP's functions for formating them are fantastic, and if I want to do something with the date, then it's simply a matter of math. I can add an hour, 3600 seconds, add a day, 86400 seconds, etc. time() generates a timestamp, mktime() makes one from month/day/year info. date() make a unix timestamp into a human readable one, with many many options.

In sql UNIX_TIMESTAMP() evauluates to the current unix timestamp, and FROM_UNIXTIME(datetime) (I think that's what it's called) returns a human readable datetime from a unixtimstamp.

All of my code (and my company's code) uses bigint's to store datetime. I've been programming php this way for about 2.5 years and haven't had any regrets whatsoever.

But hey, use whatever works best for you, and what you're used to.