How can i insert real time of an event into database using php mysqli?

Question

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',

I use following php built-in function using for datetime column in the query

date("Y-m-d H:i:s");

Problem is that this function date("Y-m-d H:i:s"); giving me two different date and time when i check in same time on server.

Localhost Result

 date("Y-m-d H:i:s"); == 2016-07-12 13:10:04

Server Result

 date("Y-m-d H:i:s"); == 2016-07-12 05:08:07

So when i use TimeAgo function on date_added column it is giving me wrong time, I mean the server time. For example I add a record then function will return me Record Added 8 Hours Ago so its totally wrong. I would like to know how can i add real time of an event into database that i can show using TimeAgo() function.

Is there any way to do that without change the server timezone, because if I change the timezone then it will be showing correct time only for those who are in the same region but what will be get others? I think they will face same issue.

I wanted to develop something like Facebook DateTime Functionality.

Can any one guide me how can I achieve this kind functionality? I would like to appreciate. Thank You

Answer 1

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

• need to have this information in your database, e.g. because you are asking registered users to supply this information
• or you determine it at runtime, usually by doing a GeoIP lookup on the visiting user's IP or by sending the DateTime Offset from the user's browser.

In any case, you then just change both DateTimes to that timezone. This is easily done via setTimezone().

The $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.

Answer 2

If you're accessing the same database server from clients with different timezone settings, you could also insert and check the date/time fields in sql:

INSERT INTO my_table SET date_added = NOW(); and then also check with something like

SELECT * FROM my_table WHERE TIMESTAMPDIFF(SECOND, date_added, NOW()) > 3600; to select rows that are older than 1 hour.