Home > Enterprise >  TIMESTAMP inserting wrong date in database when run from PHP
TIMESTAMP inserting wrong date in database when run from PHP

Time:09-19

I have a insert query from PHP file to database.

While executing the query from MySQL Workbench it is inserting last_used value in database exact time given in the query. But when I try to run the same query from PHP code it is inserting 1 day future date.

In both scenarios created_at inserting correct value which is mentioned in the table structure

I have set timezone to Asia/Kolkata in php.ini file.

I have searched a lot in the google and executed all the things mentioned by all.

<?php
$servername = "localhost";
$username = "user";
$password = "password";
$dbname = "db";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = "INSERT INTO `login_token` (`userid`, `username`, `token`, `last_used`, `role_id`) VALUES ('aiIRX 5v35p4vOokrgVR Q==', '/McgiDGM0JpsyCSie2cIV4sTwrtkE ev', 'token1', '2022-09-19 12:47:59', '')";

if ($conn->query($sql) === TRUE) {
  echo "New record created successfully";
} else {
  echo "Error: " . $sql . "<br>" . $conn->error;
}
?>

Table Structure table structure

Result of records inserted from php

enter image description here

As you see in the above question i have last_used column in database with TIMESTAMP datatype. I have a insert query in my php code. If i run the query in php it is inserting '2022-09-20 01:77:59' instead of '2022-09-19 12:47:59'. If i run the same query in mysql workbench it is inserting same value '2022-09-19 12:47:59''

when i echo date('Y-M-d H:i:s') it is showing correct time which is matched with my local machine.

when i run SELECT CURRENT_TIMESTAMP() in workbench it is also showing same time which is given in date() in php.

CodePudding user response:

Add bellow line in php.ini file

date.timezone = Asia/Kolkata

Add bellow line in my.ini file

default-time-zone = "Asia/Kolkata"

Important Note : stop and start both Apache and MySQL in XAMPP control panel to apply changes.

Better to add date_default_timezone_set('Asia/Kolkata'); in first line of your PHP file after php tag starts

  •  Tags:  
  • php
  • Related