different Timestamps when using strtotime() in PHP and UNIX_TIMESTAMP() in MySQL

Question

I have a date stored in the database. The date is 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 output it like date('d.m.Y', $mydate)

I get 28.02.2017 as a result.

Is this a "February-Problem"? How can I make date() do it right?

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

I put this in my code already 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 generated 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

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

Current Time is the same Time displayed on my Computer. So it is the correct Time and the Time-Settings seem to be ok. "Current Time" is generated by the date()-Function in PHP and with MySQLs NOW().

Col 1 is the UNIX_TIMESTAMP of the MySQL-Query.

Col 2 is the Date generated with the PHP-Date-Function and Col 1.

Col 3 is the Unix Timestamp of strtotime().

Col 4 is the Date generated with the PHP-Date-Function and Col 3.

Col 5 is the Date formated with DATE_FORMAT(xdate, '%Y-%m-%d').

Col 6 is the Date directly from the database.

As you can see, the first eight Rows are wrong calculated by the date()-Function (2nd Column) which is fed with the (wrong?) UNIX_TIMESTAMP() of the MySQL-Query:

date('d.m.Y', $mydate)

I tested what happens, if I replace the line

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

with

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

The date-Function gives back the correct Date, BUT the NOW() in MySQL deliveres the wrong Time then.

When I remove the part with the Settings from the Script (see Edit 1), everything is equal, but then I have the wrong Timezone.

Has anyone a clue for me?

Answer 1

Ok, my Settings seem to be okay and it seems to be, that strtotime() in PHP works with Timezones in opposition to UNIX_TIMESTAMP in MySQL. I decided to replace the SELECTS which select UNIX_TIMESTAMP()s and convert it to a Timestamp with strtotime(). Now it works as it shall work.

Answer 2

Expand your format to include hours and minutes, 1 gets you 10 that you are comparing LOCAL server date (according to its timezone) to UNIXTIME, which is GMT and that you are located in a timezone east of Greenwich.

Answer 3

I never understand the need to recalculate a valid presented date to the number of seconds since 1970. Certainly when afterwards these seconds are reformated to a readable date-presentation.

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

But in either case, one should be aware of the timezone settings of both the php and the database server

---

for timezone: DateTime, can have a 2nd parameter specifying the timezone

---

And as for the dates shown in the opening post: the date 31-3-2017 was the first date after the start of 2017's day light saving time (starting Sunday March 26th) Also a reason not to assume every day has 24*60*60 seconds