CODEWITHSUNDEEP
phpmysqldatetime
user3521737
user3521737

Reputation: 281

MySQL timestamp columns have mismatched timestamps

I have a database table that keeps track of when an item goes out of stock and when it comes back in stock. An additional column keeps track of the timestamp for when any given record is updated. The OOStock/InStock columns are both datetime and the update column is just a timestamp that requires no input on my part.

I was checking the records today to make sure my script was running properly and I noticed that all of the records have mismatched time values, and in some cases, mismatched dates. For example, the newest record shows that some item went out of stock at 1:45:06 on 2016/1/5, but the update column for that record shows a timestamp of 18:45:06 on 2016/1/4. There's always a difference of seven hours between the OOStock value and LastUpdate value for any given record (it only started running yesterday, so there's been no opportunity to test this with the InStock column).

The PHP code I'm using to get the current time and date for a new insertion is as follows:

$currentDate = date("Y-m-d H:i:s");
$start = "INSERT INTO out_of_stock (ID, OOStockTS, InStockTS, Status) VALUES (";
$end = sprintf("'%s', '%s', null, '%s')", $item->ID, $currentDate, "OPEN");
$start .= $end;
$db_controller->insert($start);

Why is this happening? Is it just a difference in time zones or timestamp implementation? Aside from this, my script is working as intended. I just wanted to know why these dates and times don't match when it seems like they should.

Upvotes: 1

Views: 170

Answers (2)

Axel Amthor
Axel Amthor

Reputation: 11106

It seems to be a timezone issue. Your PHP script is obviously in another TZ than the database. Which one is proper is not clear by the question so far. Just one recommendation: It's not necessary to retrieve the current time client side, let this be done by the DB itself:

$currentDate = "NOW()";  // just use the MySql function for current TS
$start = "INSERT INTO out_of_stock (ID, OOStockTS, InStockTS, Status) VALUES (";
$end = sprintf("'%s', %s, null, '%s')", $item->ID, $currentDate, "OPEN");
$start .= $end;
$db_controller->insert($start);

This should at least give consistent TS within the DB.

Upvotes: 0

Haymaker
Haymaker

Reputation: 121

Check to see if your database is using a different timezone from your version of PHP. Your PHP .ini file will have the setting for what timezone you are set to. If you run this code on your DB:

SELECT @@system_time_zone;

You should see if they either align, or mismatch.

Upvotes: 3

Related Questions