PHP stores dates and times in Unix or UTC format. This format encodes a time as an integer count of the number of seconds since New Years Day began in 1970 (1st Jan 1970). MySQL on the other hand stores dates and times in string format. All three MySQL date/ time types DATETIME, DATE and TIMESTAMP store data in string format.
MySQL DATETIME and TIMESTAMP columns store in the format:
YYYY-MM-DD HH:MM:SS
MySQL DATE columns store in the format:
YYYY-MM-DD
PHP and MySQL date/ time comparisons
Obviously, this difference in format causes problems when inserting dates and times into MySQL databases and especially when comparing MySQL and PHP dates for example trying to select entries from a table where a date value in the table is older than a PHP date. The easiest way to do this is to use the UNIX_TIMESTAMP MySQL function. For example:
// You could get the date as three integer values from a querystring on the PHP page:
$day = $_GET['day'];
$month = $_GET['month'];
$year = $_GET['year'];
// Then use the day, month and year to construct a PHP time (in seconds since
// 1st Jan 1970 format).
$datetime = strtotime($year.'-'.$month.'-'.$day.' 00:00:00');
// Then find all entries newer than the date given by the querystring arguments.
$sql = "SELECT * FROM MyTable WHERE UNIX_TIMESTAMP(UpdateDate) >= '" . $datetime . "'";
$result=mysql_query($sql);
The above SQL query causes MySQL to convert the value of the DATETIME (or DATE or TIMESTAMP) value in the UpdateDate column in the hypothetical table into a PHP format date before doing the compare.
Storing a PHP date/ time value in MySQL
To convert a PHP format date/ time into MySQL's format, the MySQL FROM_UNIXTIME function can be used. For example:
$currenttime = strtotime("now");
$sql = "UPDATE MyTable SET UpdateDate = FROM_UNIXTIME(" . $currenttime . ") WHERE ... ";
$result=mysql_query($sql);
Don't use strings for PHP/ MySQL date > or <>
You might find some PHP code examples, trying to use the PHP date() function to format PP dates and times as strings in the correct format to match MySQL values. If you can remember the correct format strings for the conversions this will work as long as you are setting values in rows. For comparisons however, strings aren't any good as the comparison will be an alphabetic/ string comparion and not the required date comparison.
