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

Question

>我有一个包含日期时间列的表：

$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 中，并在SELECT期间进行 UTC->本地时区转换。如何？

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

如何正确地做到这一点？

Answer 1

正如评论所建议的那样，此页面提到了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 2

我想说你需要做的是将你的时间值存储为称为"Unix 时间戳"的东西，这实际上是自 UTC 1st 1st 1st 一月 1970 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 integer
if ($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 timezone
date_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 earlier
echo $DT -> format('Y-m-d H:i:s');

客户端是否在DST中应该从JS获得，看看这个问题的答案一个简单的方法，但许多库等也可以做到这一点。

所以最大的问题是，为什么要走很长的路？

• 数据库中的时间数据完全独立于数据库/服务器配置。随着时间的流逝，这些可能会徘徊，错误可能会发挥作用;您希望基本数据保持一致。
• 它允许 PHP 在每个地区/DST 制度中完成有关调整时区和 DST 的所有艰苦工作。如果您尝试自己解决此问题，很快就会变得非常头疼，DST 非常尴尬。
• 时间数学更加高效和容易，尤其是在数据库调用中。您需要做的就是秒的数值比较，而不是处理SQL函数等。
• 不依赖于特定的数据库函数或引擎;它只是一个整数值！使便携性更加容易。

请注意，请注意数据库整数列中的最大值。找到最大值/最小值，将它们转换为日期时间(这是一个有用的工具)并检查您的用例。

最后要说的是，您可以使用 DateTime 对象以 UTC 格式解释时间字符串(就像您现在使用的那样)，然后在打印之前设置时区。它会起作用，但我觉得它更容易出错。

我希望这对:)有所帮助

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 local
from test

结果：

ts                  dif local
2020-03-25 14:09:31 1   2020-03-25 15:09:31
2020-03-31 06:54:08 2   2020-03-31 08:54:08
2020-03-31 14:08:10 2   2020-03-31 16:08:10
2020-04-01 07:23:04 2   2020-04-01 09:23:04
2020-04-01 09:53:19 2   2020-04-01 11:53:19