MYSQL Datetime functions

[liam1412 14]

Arrrrrr

 

 

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.

 

Thanks

[rich951 10]

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.

[Ghozer 112]

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
                   *
           FROM
                   blog
           WHERE
                   month(FROM_UNIXTIME(`date`)) = '$month'
           AND
                   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...

[LL200 10]

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