Jump to content

MYSQL Datetime functions

Recommended Posts




I realy wish I had more time for testing



I just realised a minor thing.


I have used a varchar field for my date tut tut which I input into using


$time = date("d M Y H:i:s");


I then sort my posts by a column last_post_date_time. This is fine until the month end rollover they then display in the wrong orde. I can't seem to find a clear cut way to get this to sort by the actual last_post_date_time.


Can anyone give me a hand with this.



Share this post

Link to post
Share on other sites

Sounds like a case where you may as well do it "right" - make a new field that is the right type and run a script through to read the text field and convert it. Bit annoying but bound to be worth it in the long run.

Share this post

Link to post
Share on other sites

I use VARCHAR also, but store the result of date(), (numerical (unixtime) format, such as 1175800216) - I then read that and do the date("d M Y H:i:s") conversion after.. (using date('l d-m-y \@ H:i', $row["date"]) )


I then use month(FROM_UNIXTIME(`date`)) or year(FROM_UNIXTIME(`date`)) (where date is my sql column name) when im selecting..


this is used when listing my history items, as its by $month within $year...


$sql = "SELECT
                   month(FROM_UNIXTIME(`date`)) = '$month'
                   year(FROM_UNIXTIME(`date`)) = '$year'
           ORDER BY id DESC


so you could do, "ORDER BY month(FROM_UNIXTIME(`date`))" as your ORDER -- however if your using the end result and not unixtime, then you may have to seperate using explode() and sort manually in the php -- probably easier if you change it over to unixtime tbh...


Hope this helps you get to where you want...

Share this post

Link to post
Share on other sites

strtotime may be able to convert your date string to a unix timestamp which you can sort on.

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.