Need to display timestamp from database

Question

My server is in another timezone, when I set mysql server to my timezone and display current time it shows the time minus 1 hour, as if we where still in winter time, although we have moved in my timezone to summer time DTS. my code, in php:

$sql->query("SET time_zone= '+02:00' ;");  //sets mysql timezone
   $result = $sql->query("SELECT NOW();");
   while($row = mysqli_fetch_array($result)){
    echo "<br/>Database Date: ".$row[0];      //display current mysql time
    }

   date_default_timezone_set("Asia/Jerusalem"); //sets php timezone
   echo "<br/>PHP Date: ".date('Y-m-d H:i:s')."<br/>";

Now php time displays perfectly, mysql time displays one hour earlier.(winter time) I need to fix mysql date, I'm using a TimeStamp that auto updates on insertion to table. I'm sure there has to be an option to enable Daylight time saving just never came across it.

I don't understand your question. Daylight saving time makes us travel through time. MySQL doesn't do that. If you would like the time to be one hour extra into the future you have to add that hour yourself. The best option would be to format time on display in PHP — Dharman, Apr 18 '19 at 19:30
`SET time_zone= 'Asia/Jerusalem'`? Using a pre-determined offset is incorrect, it'll ignore DST rules. — Salman A, Apr 18 '19 at 19:43

Dharman · Accepted Answer · 2019-04-18T19:52:51.480

I would strongly recommend to perform all operation on DB in the UTC time i.e. +00:00. When performing any query with WHERE clause convert your local date/time into UTC and execute the query. When SELECTing from DB use PHP's \DateTime class to convert the DB values into human output.
This way you know exactly what to expect and when. You will be less often surprised by some unjustified difference in time.

$now = $pdo->query("SELECT '2019-04-18 20:38:00+00:00'")->fetch(\PDO::FETCH_COLUMN);
$dateObj = new \DateTime($now);
$dateObj->setTimezone((new \DateTimeZone("Asia/Jerusalem")));
echo $dateObj->format(\DateTime::ATOM);
// outputs 2019-04-18T23:38:00+03:00
// now before DST change:
$now = $pdo->query("SELECT '2019-03-18 20:38:00+00:00'")->fetch(\PDO::FETCH_COLUMN);
$dateObj = new \DateTime($now);
$dateObj->setTimezone((new \DateTimeZone("Asia/Jerusalem")));
echo $dateObj->format(\DateTime::ATOM);
// outputs 2019-03-18T22:38:00+02:00

Consider in my example date 2019-04-18 20:38:00+00:00 coming from DB. If I want to display it in local time I pass the DateTimeZone object to setTimezone and I can get that date in the local time.

The problem with your example is that Jerusalem follows DST, which changes the timezone for 6 months every year. The offset changed on 29th March from +02:00 to +03:00 and your SQL didn't account for that change.

Need to display timestamp from database

1 Answers1