3

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)
Improve this question
3
  • 4
    I think it may be a timezone issue. Check what timezone MySQL (show variables like '%time_zone';) and PHP uses (date_default_timezone_get). Commented Dec 9, 2012 at 6:21
  • Yep - as well as posting those variables can you post the MySQL version you're using. Commented Dec 9, 2012 at 6:23
  • I've updated the post with the timezone information and SQL version. :) Commented Dec 9, 2012 at 6:40

2 Answers 2

Reset to default
2

Your PHP and MySQL aren't agreeing on the time. Using the time converter at:

http://www.4webhelp.net/us/timestamp.php?action=stamp&stamp=1357018200&timezone=-6

gives the result "Monday, December 31st 2012, 23:30:00 (GMT -6)" so your PHP is giving the wrong result. Although you've given the timezone that PHP is running in, can you double check by running:

date_default_timezone_set ("America/Chicago");
echo date('z-m-y', 1357018200)."\r\n";

Which should give "365-12-12".

I guess it's possible either something is setting the timezone incorrectly somewhere else or possibly that "America/Chicago (CDT)" is an old setting in your php.ini file from a previous version of PHP.

Looking at the list of allowed timezone values from http://php.net/manual/en/timezones.america.php there is no "America/Chicago (CDT)" listed, so you should figure out where it's getting set to that bogus value as it may cause other issues.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

MySQL returns '2012-12-09' and '00:45:19'. PHP returns 'Sun, 09 Dec 12 00:45:19 -0600'.
Forcing it to set the timezone did indeed make it return the expected value, which is odd since none of my scripts mess with timezones. Thank you so much for your help!
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.

Improve this answer

1 Comment

You should probably edit this into your existing answer, as it dives to the heart of the problem and is otherwise correct.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.