order by date, time

[Anon]

I have a simple web log that I have built and I am trying to order news postings in a date and time order using order by date,time desc. I am not getting an accurate order of the news posting (Can't really tell what pattern it is following). I am getting the date and time using PHP, then storing the info in 2 seperate fields in a mySQL table (date, time). I am wondering if a better way to do this in a way that gets the correct date and time order is by setting the date and time stamp using mySQL functions, rather than PHP. Does it matter? Am I just going about this the wrong way?

Many thanks for any help!

[wmvdwerf]

Easiest is to use MySQL datetime or timestamp field type. Is fully automatic. (When null it's using defaults like Now() in the proper format.

[Anon]

but when using the mySQL built in time stamp, the out put needs to be structured into an inteligible format, right? What is the best way to do this?

[wmvdwerf]

Look at date() and mktime() functions.

[djresonance]

Here's the function I use in my database class to format the date for MySQL and PostgreSQL:

function format_date()
{
    switch ($this->db_type) {

        case "mysql":
            return date("Y-m-d H:i:s");    // 2001-12-06 18:00:00
        break;

        case "postgres":
            return date("Y/m/d H:i:s T");  // 2001/12/06 18:00:00 PDT
        break;

        case "msql":
            return date("Y/m/d H:i:s T");
        break;
    }
}

This returns the proper format for DATETIME in MySQL and TIMESTAMP in PostgreSQL.

Hope this helps,
Brandon