Mr. Developer Mr. Developer - 1 year ago 63
SQL Question

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

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

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 Source

Instead of fiddling with timezones, why not just do

ALTER TABLE `your_table`
  CHANGE `date_added` `date_added`

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");
$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 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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download