How to query TIMESTAMP columns

[monkeymafia]

Hi all

ive got a timestamp column in one of my tables (using mysql). ive read that you cant directly query these columns and that you have to convert it using UNIX_TIMESTAMP. so this is the code I have:

<?php

include 'dbconnect.php'; 


$result = mysql_query("SELECT UNIX_TIMESTAMP(`created`), fk_memberid2, subject, status1 FROM technicalproblems");
print "<h1>Customer List</h1>\n";
    if (mysql_num_rows($result)) {
        print "<TABLE BORDER=\"1\"><TR STYLE=\"font-weight: bold;\"><TD>Created</TD><TD>Member ID</TD><TD>Subject</TD><TD>Status</TD></TR>";

        while($row = mysql_fetch_assoc($result)) {
            extract($row);
            print "<TR><TD>$created</TD><TD>$fk_memberid2</TD><TD>$subject</TD><TD>$status1</TD></TR>";
        }
    
        print "</TABLE>";
    }
?>

but as expected the "created" field isnt displaying anything. Also how would i go about running a query like: display records from the last x amount of days, because its abit different than access and im pretty new to this.

appreciate any help.

[andreasblixt]

The timestamp column is generally returned as the amount of seconds since the Unix epoch. (Edit: Actually, if you only return the column without any functions or operators, it should display as a string with the format YYYY-MM-DD HH:MM:SS.)

PHP has a function called date() (http://php.net/date) which lets you format this type of value. So to save bandwidth between the database and PHP, I would use the date() (Like this: date($timestamp, 'Y-m-d H:i:s')) function rather than formatting it in the SQL query (which you would do with the following: FROM_UNIXTIME(timestamp, '%Y-%m-%d %H:%i:%s').)

To get the records from last x days, you'd select all records with a timestamp greater than or equal to UNIX_TIMESTAMP() - x * 86400 (the number of seconds in 24 hours.) UNIX_TIMESTAMP() returns the current number of seconds since the Unix epoch.