在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 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函数等。

• 不依赖于特定的数据库功能或引擎；它只是一个整数值！使可移植性更加容易。

请注意，请注意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 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