在SQLite中存储在UTC中，并使用PHP在本地时区中显示

Question

我有一个包含datetime列的表：

$db = new SQLite3('test.db');

$results = $db->query('CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT,

                                                        date DATE, foo TEXT);');

我添加了一行（在UTC中存储添加该行的日期时间）

$results = $db->query('INSERT INTO test (date, foo) VALUES(CURRENT_TIMESTAMP, "bar");');

这行得通。现在，当显示行时：

$results = $db->query('SELECT * FROM test ORDER BY date desc');

while ($row = $results->fetchArray()) {

    echo $row['date'];

}

日期以UTC：这样显示2019-04-27 16:41:33。

如何在当地时区显示它呢？（包括夏令时）

我可以想象有不同的选择：

• 使用本地时区直接存储在SQLite中（但我认为这不是一个好习惯）

• 存储在UTC的SQLite中，并在期间进行UTC->本地时区转换SELECT。如何？

• 存储在UTC的SQLite中，并通过PHP进行UTC->本地时区转换。

如何正确地做到这一点？

Answer 1

我要说的是，您需要将时间值存储为一种称为“ Unix时间戳”的东西，这实际上是自UTC 1970年1月1日00:00以来的秒数。您可以使用PHP的time()函数获取当前时间。然后将您的数据库“日期”列更改为整数类型。这样，存储在数据库中的时间数据完全不受时区等的影响，这是一件好事！为了正确解释这些时区，我们可以使用PHP DateTime对象，该对象将在时区和DST方面进行所有繁重的工作。以下代码段给出了基本思路：// These need to be provided from the client side// Native JS will get you the offset, read my comment below for where to get DST from $TimeOffset = $_POST['TZOffset'];$isDST = $_POST['isDST'];// DST needs to be an integerif ($isDST == 'true'){    $isDst = 1;}else{    $isDst = 0;}// Will give a result like Europe/London// In most use cases, save this into the users session :)$TimeZoneName = timezone_name_from_abbr('', $TimeZone * -60, $isDst);// Now to tell PHP we are working in this timezonedate_default_timezone_set($TimeZoneName);///// Somewhere else in your script// Fetched a unix timestamp from your DB; $timestamp$DT = new DateTime();$DT -> setTimestamp($timestamp);// Now you have control over how it is displayed// For instance, this gives a 2010-04-28 22:41:43 type format// This will be correct to the user's timezone we calculated earlierecho $DT -> format('Y-m-d H:i:s');客户端是否在DST中应从JS获取，以一种简单的方法查看此问题的答案，但是许多库等也可以做到这一点。因此，最大的问题是，为什么要走很长的路？数据库中的时间数据完全独立于数据库/服务器配置。随着时间的流逝，它们可能会四处游荡，并且可能会出现错误。您希望基本数据保持一致。它使PHP可以在每个区域/ DST体制下完成有关调整时区和DST的所有艰苦工作。如果您自己尝试解决此问题，很快就会变得头疼，DST非常尴尬。时间数学效率更高，更容易，尤其是在数据库调用中。您需要做的只是秒的数值比较，而不是处理SQL函数等。不依赖于特定的数据库功能或引擎；它只是一个整数值！使可移植性更加容易。请注意，请注意DB整数列中的最大值。找到最大/最小值，将其转换为日期时间（这是一个有用的工具），然后对照您的用例进行检查。最后要说的是，您可以使用DateTime对象解释UTC中的时间字符串（如您现在正在使用的那样），然后在打印它们之前设置时区。它将起作用，但是我觉得它更容易出错。希望对您有所帮助：）

Answer 2

如评论所建议，此页面提到了localtime修饰符。这是一个解决方案：$results = $db->query('SELECT datetime(date, "localtime") AS localdate, foo FROM test ORDER BY date desc');while ($row = $results->fetchArray()) {    echo $row['localdate'];}

Answer 3

看一下这个SQL解决方案：select ts,        cast( round( ( julianday( ts, 'localtime' ) - julianday( ts ) ) * 24 ) as int ) as diff,        datetime( ts, '+' || cast( round( ( julianday( ts, 'localtime' ) - julianday( ts ) ) * 24 ) as int ) || ' hours' ) as localfrom test结果：ts                  dif local2020-03-25 14:09:31 1   2020-03-25 15:09:312020-03-31 06:54:08 2   2020-03-31 08:54:082020-03-31 14:08:10 2   2020-03-31 16:08:102020-04-01 07:23:04 2   2020-04-01 09:23:042020-04-01 09:53:19 2   2020-04-01 11:53:19