Order by time

joice

I have a very small MySQL database that looks like this:

id name password time
3 xxx-xxxxx password 09 Oct 2002 15:38:46
4 xxx-xxxxx password 18 Sep 2002 16:16:43
22 xxx-xxxxx password 08 Oct 2002 09:56:15
24 xxx-xxxxx password 08 Oct 2002 09:51:45

Now what I want to do is get the latest (last) date from this table. As you can see, the last (modified) date is not always the last record. Now when I say:
SELECT time FROM x ORDER BY time ASC LIMIT 0, 1 (or DESC), it orders by the day of the date (08 - 08 - 09 - 18). How do I get the latest date from this table, to print a last modified on my web page?? Thanks!

daveyboy

It sounds like you're using a VARCHAR for your date, instead of DATE. You should change the row to DATE, if you want MySQL to sort your results by date and not as strings.

IceD

Think about timestamp, datetime or even int type for storing dates. I don't use date type at all, because if you need only date at the moment, probably, you will need time in the future, and you will go into the trouble with date type.

joice

Thanks! And ehm, is there some kind of function to automatically insert the current date/time in an INSERT-query?

Joyce

cahva

Yep.. Use NOW() as a value for your 'time' in your INSERT-query.

mrJazz

I've always been a big fan of using unix_timstamp() in an INT(11) column....

Doing this means you can convert it into any date string you wish, with date().

joice

Thanks for all the answers, I used the timestamp or unix_timestamp solution and it worked perfectly well. Cheers.

Joyce

mrJazz

It's worth noting that TIMESTAMP() and UNIX_TIMSTAMP() are completely different things!