MySQL Date/time help? |
Author: |
Message: |
Jimbo
Veteran Member
Posts: 1650 Reputation: 18
32 / /
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 |
|
|
absorbation
Elite Member
Posts: 3636 Reputation: 81
– / /
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 |
|
|
Jimbo
Veteran Member
Posts: 1650 Reputation: 18
32 / /
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 |
|
|
absorbation
Elite Member
Posts: 3636 Reputation: 81
– / /
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 |
|
|
Spunky
Former Super Mod
Posts: 3658 Reputation: 61
36 / /
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"
|
|
01-06-2010 07:20 PM |
|
|
Lou
Veteran Member
Posts: 2475 Reputation: 43
– / /
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 .
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 |
|
|
Spunky
Former Super Mod
Posts: 3658 Reputation: 61
36 / /
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"
|
|
01-06-2010 07:42 PM |
|
|
Jimbo
Veteran Member
Posts: 1650 Reputation: 18
32 / /
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 |
|
|
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 |
|
|
Jimbo
Veteran Member
Posts: 1650 Reputation: 18
32 / /
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 |
|
|
Pages: (2):
« First
[ 1 ]
2
»
Last »
|
|