PHP时间戳UTC [英] php timestamp utc
问题描述
我有一个PHP MySQL查询，该查询将一些数据插入MySQL数据库，并且其中包含时间戳.
I have a PHP MySQL query that inserts some data into a MySQL database and it includes a timestamp.
当前INSERT查询使用2012-07-24 13:13:02
不幸的是，服务器不在我所在的时区，它被列为America/Los_Angeles，如print date_default_timezone_get();
Unfortunately for me the Server is not in my time zone and it is listed as America/Los_Angeles as shown print date_default_timezone_get();
我希望执行以下操作:
date_default_timezone_set('Europe/London');
$timefordbLondonEU = date('Y-m-d H:i:s', time());
并将$timefordbLondonEU代替NOW()存入数据库；
and simply save into the database the $timefordbLondonEU in place of the NOW();
这是保存此类数据的好方法吗?
Is this a good way to save such data ?
非常感谢，
理查德
[添加文字]
我将MySQL数据库中的Type更改为DateTime并执行以下操作:
I changed the Type in the MySQL db to DateTime and did the following:
date_default_timezone_set('Europe/London');
$timefordbLondonEU = date('Y-m-d H:i:s', time());
它正在运行，但是我仍然没有获得总体概念.
It is working but Im still not getting the overall concept yet.
Assumptions based on your comments:
- MySQL =您只需使用INT类型就没有数据类型UTC.
- Unix_TimeStamp()是否保存当前时间或计数?采用UTC格式，例如1343247227.
- 由于UTC是从公共0点开始的计数，因此您可以从中获取任何时区.假设您不希望在1970年参考0点之前有一个日期.
根据您所说的话，我的猜想和得出的最好方法是将时间保存为INT中的UTC时间(1343247227)，然后从那里生成您想要的任何时区.再次假设您不需要在1970年参考0点之前存储日期.
My guess and lead on from what you have said is the best way to do it is save the time as UTC in an INT (1343247227) and then generate any time zones you want from there. Again assuming you don't need to store dates before the reference 0 point in 1970.
同样，为什么不在已知时区将其存储为日期时间YYYY-MM-DD HH:MM:SS，然后将其转换为UTC或其他时区.一切似乎都很混乱=(
Equally why not store as datetime YYYY-MM-DD HH:MM:SS at a known timezone and then convert to UTC or other timezones. It all seems pretty messy =(
推荐答案
正如@Petah在评论中所说，将您的时间存储在UTC中，并根据需要在应用程序中进行隐藏.
As @Petah said in the comments, store your times in UTC and covert them in the application as needed.
Unix时间戳以UTC表示，因此我通常将时间存储在数据库中作为时间戳.这样可以避免麻烦和麻烦，首先选择时先转换为UTC以插入，然后再转换为UTC.
Unix timestamps are in UTC so I usually store my times in the database as timestamps. This saves the headache and confusion of first converting to UTC to insert, and then from UTC when selecting.
也就是说，将您的时间字段设置为INT类型，并在插入时在MySQL中使用函数UNIX_TIMESTAMP()，或者使用
That is, make your time field an INT type, and use the function UNIX_TIMESTAMP() in MySQL when you insert, or get the timestamp from PHP using the time() function.
当您从数据库中获取时间戳时，它将使用UTC，但是当您使用date()在PHP应用程序中显示它时，它将显示在服务器时区中，或者您使用date_default_timezone_set设置的时区中.
When you fetch the timestamp from the DB it will be in UTC, but when you display it in your PHP application using date(), it will display in the server timezone, or whatever you set with date_default_timezone_set.
因此，以下两个查询将起作用:
Therefore the following two queries will work:
INSERT INTO `table` (id, time) VALUES(NULL, UNIX_TIMESTAMP());
// or
$time = time();
$query = "INSERT INTO `table` (id, time) VALUES(NULL, $time);
如果要从数据库中选择它作为DATETIME，则可以执行以下操作:
If you want to select it from the DB as a DATETIME, you can do this:
SELECT *, FROM_UNIXTIME(time) as dt FROM `table` WHERE 1
结果dt列的格式为yyyy-MM-dd hh:mm:ss.
您可以使用 date()
You can format the numeric timestamp in PHP using date()
如果您拥有的PHP版本是64位，则不限于1970年-2036年，PHP将支持64位时间戳，只要确保在这种情况下在MySQL中使用BIGINT列即可.
If the PHP version you have is 64-bit, you aren't limited to the 1970 - 2036 range, PHP will support 64-bit timestamps, just make sure to use a BIGINT column in MySQL in that case.
希望有帮助.
这篇关于PHP时间戳UTC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
