Dates in PHP and MySQL

storing and retriving dates in MySQL for use in PHP.

One common solution is to store the dates in DATETIME fields and use PHP's date() and strtotime() functions to convert between PHP timestamps and MySQL DATETIMEs. For example:

    $mysqldate = date( 'Y-m-d H:i:s', $phpdate );
    $phpdate = strtotime( $mysqldate );
    
    

A second option is to let MySQL do the work. MySQL has functions to convert the data at the point where we access the database. UNIX_TIMESTAMP will convert from DATETIME to PHP timestamp and FROM_UNIXTIME will convert from PHP timestamp to DATETIME. The methods are used within the SQL query. So we insert and update dates using queries like this -.

    $query = "UPDATE table SET
    datetimefield = FROM_UNIXTIME($phpdate)
    WHERE...";
    $query = "SELECT UNIX_TIMESTAMP(datetimefield)
    FROM table WHERE...";);