Mr. Developer Mr. Developer - 4 months ago 16
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
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

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

Comments