GMtime stamping for MySQL

coldfingertips has asked for the wisdom of the Perl Monks concerning the following question:

This was asked briefly in the chatterbox already, but it might be easier to ask through here.

I have an image gallery script that SHOULD sort out the images in ASC order (last uploaded picture will be displayed first). For some unknown reason, it doesn't do that.

After doing more scanning of this old code, the timestamp is in gimtime() format "my $timestamp = gmtime();" so it looks like "Sat Jun 25 01:32:22 2005". Is this format an acceptable timestamp that can be sorted in MySQL ASC or DESC?

I have to imagine it can't be and that would explain the problem. And if that were the case, is there a function to convert gmtime back into a sortable time?

Comment on GMtime stamping for MySQL

Replies are listed 'Best First'.
Re: GMtime stamping for MySQL by shiza (Hermit) on Jun 29, 2005 at 21:51 UTC
MySQL has a ton of Date/Time Functions. Also, they maintain some of the best documentation that I know of.	[reply]
Re: GMtime stamping for MySQL by fmerges (Chaplain) on Jun 29, 2005 at 22:07 UTC
Hi, If you have used a timestamp/date data type in the MySQL or RDB you can also do ORDER BY, because the RDB will support this function. But if you used a varchar or a text type, then you must do sorting outside the DB, fetching all values into a data type in perl and doing a "custom" sort. I don't know of any RDBMS which not support sorting of one of his time/date data types. And on most of them you have also functions or setting to say which format you will get when you retrieve the data, that has nothing to do with the sorting that the RDBMS will do. Regards, \|fire\| at irc	[reply]
Re: GMtime stamping for MySQL by Adrade (Pilgrim) on Jun 30, 2005 at 05:56 UTC
This is obviously untested in your application (the str_to_date part is correct tho) but it should do what you want. select `stuff`,`morestuff` from `table` order by str_to_date(`datecolumn`, '%a %b %e %H:%i:%s %Y') asc Hope it helps! -Adam -- Impossible! The Remonster can only be killed by stabbing him in the heart with the ancient bone saber of Zumakalis!	[reply] [d/l]
Re: GMtime stamping for MySQL by BUU (Prior) on Jun 29, 2005 at 21:43 UTC
UNIX_TIMESTAMP()	[reply]
Re: GMtime stamping for MySQL by TedPride (Priest) on Jun 30, 2005 at 16:58 UTC
Would probably be better to change the field to INT and convert all your gmtime() to time(). It's less work for mySQL than if it has to convert every date string every page access. Also, depending on how many accesses you have and how many image files, you could look into an INDEX for the field. This doesn't directly answer your question, but it may be useful things to keep in mind.	[reply]