I'm trying to add datetime for check record changes. I'm using datetime datatype in table.
`date_added` datetime DEFAULT '0000-00-00 00:00:00',
date("Y-m-d H:i:s"); == 2016-07-12 13:10:04
date("Y-m-d H:i:s"); == 2016-07-12 05:08:07
Record Added 8 Hours Ago
Instead of fiddling with timezones, why not just do
ALTER TABLE `your_table` CHANGE `date_added` `date_added` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
This will change your column from a DATE column to a TIMESTAMP column, converting all the dates to their respective UTC timestamps in the process.
When a new row is inserted, it will use the current timestamp as a value. Timestamps are always in UTC, so you don't have to change the timezone on your MySql server, nor supply the date when inserting a new row.
If you cannot or want not change your columns, you can also just select the timestamp via
SELECT UNIX_TIMESTAMP('date_added') FROM your_table;
For your TimeAgo, you can then just do
$now = new DateTime; $dateAdded = new DateTime("@$yourTimestampFromDb"); $dateAdded->setTimezone($now->getTimezone()); $timeSinceAdded = $dateAdded->diff($now);
When you supply a timestamp to DateTime, it will always use UTC regardless of your default server timezone set. Consequently, you have to either convert
$dateAdded to the default timezone (as shown above) or convert
$timeSinceAdded to UTC.
To change the dateTime to the currently visiting user's timezone, you either
In any case, you then just change both DateTimes to that timezone. This is easily done via
$timeSinceAdded will then be a
DateInterval object, which you can use like this
echo $timeSinceAdded->format('%a total days');
Please refer to the links for further details, for instance on the available format modifiers.