PHP & MySQL are returning different dates for same timestamp

Question

I am encountering a slightly frustrating problem and I have a feeling there is a simple solution to it. When I pass the same UNIX timestamp to the PHP date and MySQL FROM_UNIXTIME methods they are returning two very different dates. I would like to return a value from MySQL that matches the one returned by PHP.

Here is the code I am currently using along with it's output. The timestamp provided represents the date Tue, 01 Jan 2013 (01/01/2013). Also for reference, here are the format values.

MySQL Format

• %j = Day of year (001..366).
• %m = Month, numeric (00..12).
• %y = Year, numeric (two digits).

PHP Format

• z = The day of the year starting from 0 (0 through 365).
• m = Numeric representation of a month, with leading zeros (01 through 12).
• y = A two digit representation of a year (Examples: 99 or 03).

MySQL Query

SELECT FROM_UNIXTIME(1357018200, '%j-%m-%y');
-> 366-12-12

PHP Code

echo date('z-m-y', 1357018200);
-> 0-01-13

Any help would be greatly appreciated, thanks for your time. :)

Other Information

• MySQL Version: 5.5.23
• MySQL system_time_zone: CDT
• MySQL time_zone: SYSTEM
• PHP date_default_timezone_get: America/Chicago (CDT)

Answer 1

Once you've got your timezone issues sorted out, the answer to your actual question is:

function    sqlStyleDate($timestamp){

    $dayOfYear = date("z", $timestamp) + 1;
    $year = date("y", $timestamp);
    $month = date("n", $timestamp);

    $result = "$dayOfYear-$year-$month";

    return $result;
}

echo sqlStyleDate(1357018200)."\r\n";

That will convert PHPs 0-365 day of year, to be the same as MySQL's 1-366 day of year.