formatting MySQL dates

Anon

Hi,

I'm writing a forum and I want the main page to sort the threads with the threads having been last replied to on top, so if thread x was replied to 10 minutes ago and thread y was replied to 20 minutes ago, thread x would be on top. I have the data type in the mysql table set as datetime but is there any way to format this so that it appears like this:

03-05-2000 10:20:30

'date_format(varname, '%mm/%dd/%yy') as varname' works for date datatypes but I need timestamp so it can be sorted by minutes/seconds/etc.

Also, can I just sort this through the mysql query like "SELECT * FROM table WHERE blah ORDER BY last_post_date"?

Thanks,
Intaglio

toma42

use unix_timestamp(datetime) and format the time with date() in php.

Anon

here.
$access_from_date = date("YmdHis",strtotime("$date_from_month/$date_from_day/$date_from_year"));
$access_to_date = date("YmdHis",strtotime("1/1/2005"));
$query = "SELECT * FROM tablename WHERE datefield BETWEEN $access_from_date AND $access_to_date ORDER BY datefield DESC";

also, don't know if you'll find this useful or not but heres a function to convert a mysql timestamp to anything you want, it takes the same string arguments as "date()".

function parseMySQLTS($TS, $dformatStr){
$TSYEAR = substr($TS, 0, 4);
$TSMONTH= substr($TS, 5, 2);
$TSDAY = substr($TS, 7, 2);
$TSHOUR = substr($TS, 9, 2);
$TSMIN = substr($TS, 11, 2);
$TSSEC = substr($TS, 13, 2);

$TS = mktime($TSHOUR,$TSMIN,$TSSEC,$TSMONTH,$TSDAY,$TSYEAR);
$TS = date($dformatStr,$TS);
return $TS;
}