0

This is my code :

date_default_timezone_set('Europe/Rome');
$dt2=date("Y-m-d H:i:s");
$query = $connection->prepare("INSERT INTO `sessions` ( `user_id`, `session_key`, `session_address`, `session_useragent`, `session_expires`) VALUES ( ?, ?, ?, ?, DATE_ADD('$dt2',INTERVAL 1 HOUR) )");

Since now() gave me a 5 hour time zone back, I use $dt2 (contains timestamp value of Europe/rome). In DATE_ADD, the first parameter will be the $dt2 instead of now(). I need to retrieve the actual Italian time by doing this code, I have already set the time zone in Italy in php.ini and echo the $dt2 it works perfectly but it fails to pass in query.

What wrong on my code? The format of the date on the database is datetime.

JM Gelilio
  • 3,482
  • 1
  • 11
  • 23
andrea
  • 1
  • 1
  • I think your sql expression is correct. You can verify it by running `SELECT DATE_ADD("2020-01-01 11:11:11",INTERVAL 1 HOUR)` - it works perfect. It's good to know the error you get while your code got executed. This is how you can also make the question more specific. – Ilia Kondrashov Oct 02 '20 at 20:33
  • what I meant is that despite the php.ini settings I am on europa / rome, neither the now () function nor the one mentioned can give me the right time, the time that comes back to me on the db is 5 hours back and I need the right Italian time – andrea Oct 02 '20 at 22:05
  • Mysql server is using it's own timezone - it's a separate application with own settings. Best practice is use same source of truth for all you date/time related operations, and in most cases application in the right choice for it. You can add few hours to certain time using DateTime and DateInterval classes - https://www.php.net/manual/en/datetime.add.php – Ilia Kondrashov Oct 02 '20 at 22:47
  • your answer is a bit generic I have already read from different sources, the example codes are limited, I need a practical example related to my case to be able to add the 5 hours also on the db, on the php I can see the right time – andrea Oct 02 '20 at 23:02
  • Your code is correct. To understand underlaying issue it's good to deep-dive into storing datetime in mysql. Looks like you are `session_expires` column has `timestamp` type. Return value of this type is being adjusted according to time zone of current mysql session. You can find some examples [here](https://www.mysqltutorial.org/mysql-timestamp.aspx) – Ilia Kondrashov Oct 03 '20 at 08:13

0 Answers0