Different timestamps when using strtotime () in PHP and UNIX_TIMESTAMP () in MySQL

Question

I have a date stored in a database. Date - 2017-03-01. The field is a date field.

When i use

DATE_FORMAT(orderdate, '%d.%m.%Y') as mydate 

in my MySQL Query, "01.03.2017" is displayed.

When i use

 UNIX_TIMESTAMP(orderdate) as mydate 

and print it as date('dmY', $mydate)

As a result, I get 02/28/2017.

Is this a "February issue"? How can I do date () do it right?

----------------- Edit 1 -----------------

I have put this in my code before.

 # set timezone date_default_timezone_set('Europe/Vienna'); # daylight-saving time if(date('I') < 1){ mysqli_query($db, "SET time_zone = '+01:00'"); }else{ mysqli_query($db, "SET time_zone = '+02:00'"); } 

----------------- Edit 2 -----------------

Ok, I created a MySQL table with this content (Field-Type: "date"):

 xdate 2017-01-01 2017-01-15 2017-01-31 2017-02-01 2017-02-15 2017-02-28 2017-03-01 2017-03-15 2017-03-31 2017-04-01 2017-04-15 2017-04-30 2017-05-01 2017-05-15 2017-05-31 2017-06-01 2017-06-15 

The generated output from my Script:

 Current Time 2017-06-16 02:31:08 PHP-Time 2017-06-16 02:31:08 MySQL-Time Col 1 Col 2 Col 3 Col 4 Col 5 Col 6 1483221600 2016-12-31 1483225200 2017-01-01 2017-01-01 2017-01-01 1484431200 2017-01-14 1484434800 2017-01-15 2017-01-15 2017-01-15 1485813600 2017-01-30 1485817200 2017-01-31 2017-01-31 2017-01-31 1485900000 2017-01-31 1485903600 2017-02-01 2017-02-01 2017-02-01 1487109600 2017-02-14 1487113200 2017-02-15 2017-02-15 2017-02-15 1488232800 2017-02-27 1488236400 2017-02-28 2017-02-28 2017-02-28 1488319200 2017-02-28 1488322800 2017-03-01 2017-03-01 2017-03-01 1489528800 2017-03-14 1489532400 2017-03-15 2017-03-15 2017-03-15 1490911200 2017-03-31 1490911200 2017-03-31 2017-03-31 2017-03-31 1490997600 2017-04-01 1490997600 2017-04-01 2017-04-01 2017-04-01 1492207200 2017-04-15 1492207200 2017-04-15 2017-04-15 2017-04-15 1493503200 2017-04-30 1493503200 2017-04-30 2017-04-30 2017-04-30 1493589600 2017-05-01 1493589600 2017-05-01 2017-05-01 2017-05-01 1494799200 2017-05-15 1494799200 2017-05-15 2017-05-15 2017-05-15 1496181600 2017-05-31 1496181600 2017-05-31 2017-05-31 2017-05-31 1496268000 2017-06-01 1496268000 2017-06-01 2017-06-01 2017-06-01 1497477600 2017-06-15 1497477600 2017-06-15 2017-06-15 2017-06-15 

The current time is the same time displayed on my computer. So this is the right time, and the time settings look fine. The "current time" is generated by the date () function - in PHP and with MySQL NOW ().

Col 1 is UNIX_TIMESTAMP MySQL-Query.

Col 2 is a date generated using the PHP-Date-Function and Col 1.

Col 3 is the Unix timestamp for strtotime ().

Col 4 is a date generated using the PHP-Date-Function and Col 3.

Col 5 is a date generated using DATE_FORMAT (xdate, '% Y-% m-% d').

Col 6 is a date directly from the database.

As you can see, the first eight rows are incorrect, calculated using the function date () - Function (2nd column), which is loaded using (incorrect?) UNIX_TIMESTAMP () MySQL-Query:

 date('dmY', $mydate) 

I checked what happens if I replace the string

  mysqli_query($db, "SET time_zone = '+02:00'"); 

from

  mysqli_query($db, "SET time_zone = '+01:00'"); 

The Date function returns the correct date, BUT NOW () in MySQL provides the wrong time.

When I delete the settings part from the Script (see Edit 1), it’s all the same, but then I have the wrong time zone.

Will someone tell me?

Answer 1

Ok, my settings look fine, and strtotime () in PHP seems to work with Timezones as opposed to UNIX_TIMESTAMP in MySQL. I decided to replace the SELECTS that select UNIX_TIMESTAMP () s and convert them to a timestamp using strtotime (). Now it works the way it will work.

Answer 2

Expand your format to include hours and minutes, 1 you get 10 that you are comparing the LOCAL server date (according to its time zone) with UNIXTIME, which is GMT and that you are in the time zone east of Greenwich.

Answer 3

I never understand the need to recalculate the actual date presented by the number of seconds since 1970. Of course, when after that these seconds are reformatted to a readable presentation date.

 ` <?php $dtDate = new \DateTime('2017-03-01'); echo $dtDate->format('dmY'); ?> ` 

But in any case, you should know the time zone settings of both php and the database server

---

for timezone: DateTime, may have a second parameter specifying the timezone

---

And as for the dates shown in the opening of the message: the date 31-3-2017 was the first date after the start of daylight hours in 2017 (starting March 26) There is also a reason not to assume that every day has 24 * 60 * 60 seconds