MySQL Date/time help? - Printable Version
+-- Forum: MsgHelp Archive (/forumdisplay.php?fid=58)
+--- Forum: Skype & Technology (/forumdisplay.php?fid=9)
+---- Forum: Tech Talk (/forumdisplay.php?fid=17)
+----- Thread: MySQL Date/time help? (/showthread.php?tid=93451)
MySQL Date/time help? by Jimbo on 01-06-2010 at 05:33 PM
I have a mysql table that stores date/time in the format of "01/02/10 07:06:33", but when listing that table using PHP, and ordering by date/time, the 2010 records appear at the end, whereas, the latest 2009 records, so from the 31st of December appear at the top. Is there anyway to get around this? or to "SELECT *", but then print in the reverse order?
RE: MySQL Date/time help? by absorbation on 01-06-2010 at 06:15 PM
You could try using a timestamp instead, which gives you a lot more flexibility when handling time and should produce your database in chronological order.
RE: MySQL Date/time help? by Jimbo on 01-06-2010 at 06:51 PM
Okay thanks, I will look into using a timestamp instead.
RE: MySQL Date/time help? by absorbation on 01-06-2010 at 07:10 PM
Use your own personal identifier e.g. id > steam_id > desc > time
RE: MySQL Date/time help? by Spunky on 01-06-2010 at 07:20 PM
That won't work if the timestamps don't relate to the id or steam_id. For example, I could have the lowest id (1), but the most recent time stamp (say now for example) and a steam_id of 748476
I'd show up before someone with the earliest time stamp, but with a higher id
RE: MySQL Date/time help? by Lou on 01-06-2010 at 07:35 PM
quote:Usually, when a record is added, the unique id is set to that record, one above the last one. Thus, organizing in desc order by unique id, will give the newest records first .
RE: MySQL Date/time help? by Spunky on 01-06-2010 at 07:42 PM
Yeah, I suppose. Don't know what I was thinking actually =/
RE: MySQL Date/time help? by Jimbo on 01-06-2010 at 08:14 PM
Thanks, I'll go about adding a unique ID later
RE: MySQL Date/time help? by Adeptus on 01-07-2010 at 12:03 AM
Without making any changes to how you are storing your data, the easiest solution would be to use:
RE: MySQL Date/time help? by Jimbo on 01-07-2010 at 01:42 PM
Oh wow, thanks Adeptus. That works exactly how I want, and like you say, my table is only around 100 records, so that shouldnt be a performance concern, right?
RE: MySQL Date/time help? by Adeptus on 01-07-2010 at 03:51 PM
quote:Not at all. Ordering by a function return value obviously adds some overhead and I wouldn't want to use this on two million rows, but you will be just fine.