如何使用php mysqli将实时事件插入数据库？ [英] How can i insert real time of an event into database using php mysqli?
问题描述
我正在为添加日期时间添加检查记录更改。我在表中使用datetime数据类型。
`date_added` datetime DEFAULT'0000-00-00 00:00:00'，
我使用以下php内置函数在查询中使用datetime列
date（Ymd H：i：s）;
问题是这个函数 date（Ymd H：i：s ）; 给我两个不同的日期和时间，当我在服务器上同时检查。
Localhost结果
date（Ymd H：i：s ）; == $ 7 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $
$ b date（Ymd H：i：s）; == 2016-07-12 05:08:07
所以当我使用 TimeAgo 功能 date_added 列它给我错误的时间，我的意思是服务器的时间。例如我添加一个记录，然后函数将返回我记录添加了8小时的时间所以完全错误。我想知道如何将事件的实时添加到数据库中，我可以使用 TimeAgo（）函数显示。
有没有办法做到这一点，没有更改服务器时区，因为如果我更改时区，那么它将显示正确的时间只对那些在同一个地区但什么会得到别人？我认为他们会面对同样的问题。
我想开发类似Facebook DateTime功能的东西。
任何人都可以指导我如何实现这一点亲切的功能？我想感谢谢谢
解决方案而不是使用时区，为什么不只是
ALTER TABLE`your_table`
CHANGE`date_added`` date_added`
TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
这将使您的列从DATE列更改为TIMESTAMP列，将所有日期转换为其进程中各自的UTC时间戳。
插入新行时，它将使用当前时间戳作为值。时间戳始终为UTC，因此您不必更改MySql服务器上的时区，也不会在插入新行时提供日期。
如果您不能您也可以通过
SELECT UNIX_TIMESTAMP（'date_added'）FROM your_table选择时间戳;
对于您的TimeAgo，您只需执行
$ now = new DateTime;
$ dateAdded = new DateTime（@ $ yourTimestampFromDb）;
$ dateAdded-> setTimezone（$ now-> getTimezone（））;
$ timeSinceAdded = $ dateAdded-> diff（$ now）;
当您向DateTime提供时间戳记时，无论您的默认服务器时区设置如何，都将始终使用UTC。因此，您必须将 $ dateAdded 转换为默认时区（如上所示），或将 $ timeSinceAdded 转换为UTC 。
要将dateTime更改为当前访问的用户的时区，您可以
- 需要在您的数据库中拥有此信息，例如因为您要求注册用户提供此信息
- 或 您可以在运行时确定它，通常是通过对访问用户的IP进行GeoIP查找，或者从用户浏览器发送DateTime Offset。
无论如何，您只需将两个DateTimes更改为该时区。 这很容易完成通过 setTimezone（）。
$ timeSinceAdded 然后将是一个 DateInterval 对象，你可以这样使用
echo $ timeSinceAdded->格式（'％a total days'）;
请参阅链接了解更多详细信息，例如可用的格式修饰符。
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
解决方案 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.
这篇关于如何使用php mysqli将实时事件插入数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
