What happened to the Messenger Plus! forums on msghelp.net?
Shoutbox » MsgHelp Archive » Skype & Technology » Tech Talk » MySQL Date/time help?

Pages: (2): « First [ 1 ] 2 » Last »
MySQL Date/time help?
Author: Message:
Jimbo
Veteran Member
*****

Avatar

Posts: 1650
Reputation: 18
31 / Male / Flag
Joined: Jul 2006
O.P. MySQL Date/time help?
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?


This post was edited on 01-06-2010 at 05:35 PM by Jimbo.
01-06-2010 05:33 PM
Profile E-Mail PM Find Quote Report
absorbation
Elite Member
*****

Avatar

Posts: 3636
Reputation: 81
– / Male / Flag
Joined: Feb 2005
RE: MySQL Date/time help?
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.

Are you just attempting to show the latest records first? If that is the case using your primary key (typically 'id') and order them from the top. If you don't have a primary key, I highly recommend using one, their vital when it comes to using relationships.
01-06-2010 06:15 PM
Profile PM Find Quote Report
Jimbo
Veteran Member
*****

Avatar

Posts: 1650
Reputation: 18
31 / Male / Flag
Joined: Jul 2006
O.P. RE: MySQL Date/time help?
Okay thanks, I will look into using a timestamp instead.

I do have a primary key, but the unique identifier is based on a players SteamID, where no two players can have the same ID, however, these do not increase at a standard rate. For example, one player could have a SteamID of STEAM_0:1:123456, but then another player, whose record is added at a later date, could have a SteamID of STEAM_0:0:116483.

This post was edited on 01-06-2010 at 06:54 PM by Jimbo.
01-06-2010 06:51 PM
Profile E-Mail PM Find Quote Report
absorbation
Elite Member
*****

Avatar

Posts: 3636
Reputation: 81
– / Male / Flag
Joined: Feb 2005
RE: MySQL Date/time help?
Use your own personal identifier e.g. id > steam_id > desc > time :)
01-06-2010 07:10 PM
Profile PM Find Quote Report
Spunky
Former Super Mod
*****

Avatar

Posts: 3658
Reputation: 61
35 / Male / Flag
Joined: Aug 2006
RE: MySQL Date/time help?
quote:
Originally posted by absorbation
Use your own personal identifier e.g. id > steam_id > desc > time :)

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
<Eljay> "Problems encountered: shit blew up" :zippy:
01-06-2010 07:20 PM
Profile PM Find Quote Report
Lou
Veteran Member
*****

Avatar

Posts: 2475
Reputation: 43
– / Male / Flag
Joined: Aug 2004
RE: MySQL Date/time help?
quote:
Originally posted by Spunky
quote:
Originally posted by absorbation
Use your own personal identifier e.g. id > steam_id > desc > time :)

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
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 :undecided:.
[Image: msghelp.net.png]
The future holds bright things in it\\\'s path, but only time will tell what they are and where they come from.
Messenger Stuff Forums
01-06-2010 07:35 PM
Profile PM Web Find Quote Report
Spunky
Former Super Mod
*****

Avatar

Posts: 3658
Reputation: 61
35 / Male / Flag
Joined: Aug 2006
RE: MySQL Date/time help?
Yeah, I suppose. Don't know what I was thinking actually =/
<Eljay> "Problems encountered: shit blew up" :zippy:
01-06-2010 07:42 PM
Profile PM Find Quote Report
Jimbo
Veteran Member
*****

Avatar

Posts: 1650
Reputation: 18
31 / Male / Flag
Joined: Jul 2006
O.P. RE: MySQL Date/time help?
Thanks, I'll go about adding a unique ID later :)
01-06-2010 08:14 PM
Profile E-Mail PM Find Quote Report
Adeptus
Senior Member
****


Posts: 732
Reputation: 40
Joined: Oct 2005
RE: MySQL Date/time help?
Without making any changes to how you are storing your data, the easiest solution would be to use:

SELECT * FROM myTable ORDER BY STR_TO_DATE(myTimestamp, '%m/%d/%y %H:%i:%s') DESC

That should do exactly what you want, by converting your date/time string to a DATETIME value for sorting on the fly.  I doubt your tables are large enough for this to be a performance concern.

The best practice would be to redesign the table to use DATETIME for the timestamp.  You can use the STR_TO_DATE() function with the above format string to convert the data as you insert it.

Relying on an incrementing ID for this is a kludge, a logic flaw, and asking for it to bite you in the posterior.  You shouldn't rely on the assumption that your data will always be inserted in chronological order, even if you believe it will be -- that is exactly the kind of assumption that leads to hard to find bugs down the road.
01-07-2010 12:03 AM
Profile E-Mail PM Find Quote Report
Jimbo
Veteran Member
*****

Avatar

Posts: 1650
Reputation: 18
31 / Male / Flag
Joined: Jul 2006
O.P. RE: MySQL Date/time help?
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?
01-07-2010 01:42 PM
Profile E-Mail PM Find Quote Report
Pages: (2): « First [ 1 ] 2 » Last »
« Next Oldest Return to Top Next Newest »


Threaded Mode | Linear Mode
View a Printable Version
Send this Thread to a Friend
Subscribe | Add to Favorites
Rate This Thread:

Forum Jump:

Forum Rules:
You cannot post new threads
You cannot post replies
You cannot post attachments
You can edit your posts
HTML is Off
myCode is On
Smilies are On
[img] Code is On