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?

hmmm I already put timezone-settings in my settings-file.. see my edit1 — Bernhard
– Bernhard, Commented Jun 13, 2017 at 17:52
@Bernhard, compare two UNIX timestamps to better understand where did it go wrong. If they are the same, the problem is in timezones. If they are different, server times are different. — Yury Fedorov
– Yury Fedorov, Commented Jun 13, 2017 at 18:27

Bernhard · Accepted Answer · 2017-06-16 12:24:08Z

5

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.

answered Jun 16, 2017 at 12:24

Bernhard

1,8701 gold badge12 silver badges19 bronze badges

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

Comments

tony gil · Accepted Answer · 2017-06-16 12:30:03Z

2

+25

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.

answered Jun 16, 2017 at 12:30

tony gil

9,5846 gold badges80 silver badges104 bronze badges

Comments

Ivo P · Accepted Answer · 2017-06-22 20:34:08Z

1

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

edited Jun 22, 2017 at 20:34

answered Jun 22, 2017 at 15:21

Ivo P

1,7101 gold badge9 silver badges18 bronze badges

1 Comment

tony gil Over a year ago

the point is having a standard date that is not determined / does not favor one specific religious viewpoint. Why is 1/1/1970 in London any worse than a winter solstice 2K+ years ago, which the christian faith chose to yearly celebrate the birth of a supposedly supernatural being on?

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related