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

Question

I have a table containing a datetime column:

$db = new SQLite3('test.db');
$results = $db->query('CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT,
                                                        date DATE, foo TEXT);');

and I add a row (storing the datetime of the addition of the row in UTC) with

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

This works. Now when displaying the rows:

$results = $db->query('SELECT * FROM test ORDER BY date desc');
while ($row = $results->fetchArray()) {
    echo $row['date'];
}

the date is displayed like this, in UTC: 2019-04-27 16:41:33.

How to display it in the local timezone instead? (including Daylight Saving)

I can imagine there are different options:

• store directly in SQLite with local timezone (but I think this is not good practice)

• store in SQLite in UTC, and do the UTC->local timezone conversion during the SELECT. How?

• store in SQLite in UTC, and do the UTC->local timezone conversion via PHP.

How to do this properly?

Answer 1

As suggested by a comment, this page mentions the localtime modifier. Here is a solution:

$results = $db->query('SELECT datetime(date, "localtime") AS localdate, foo FROM test ORDER BY date desc');
while ($row = $results->fetchArray()) {
    echo $row['localdate'];
}