Store in UTC in SQLite and display in local timezone with 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?

All options seems legit. You can see how to do 2nd option here: https://stackoverflow.com/questions/15017799/how-to-convert-utc-date-to-local-time-zone-in-mysql-select-query. I guess there is no much difference between the options and it's more about what's more convenient for you. — HTMHell, Apr 27 '19 at 18:16
Carefully read https://www.sqlite.org/lang_datefunc.html and you'll see how to do the second option. — Shawn, Apr 27 '19 at 18:16

score 1 · Accepted Answer · answered Apr 27 '19 at 19:17

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'];
}

score 0 · Answer 2 · answered Apr 27 '19 at 22:16

I would say what you need to do is store you time values as something called a "Unix timestamp", which is literally the number of seconds since UTC 1st January 1970 00:00. You can get the current time by using PHP's time() function. Then change you DB "date" column to an integer type. In this way, the time data stored in your database is completely independent of timezone etc, which is a good thing!

To interpret these timezones properly we can use PHP DateTime object, which will do all the heavy lifting in terms of timezones and DST. The following code snippet gives the basic ideas:

// 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');

Whether the client is in DST should obtained from JS, have a look at the answer for this question for a simple method, but many libraries etc can also do it.

So the big question, why go the long way around?

The time data in your database is completely independent of DB/server configuration. Over time these can wander and bugs can come into play; you want your fundamental data to be consistent.
It lets PHP do all the hard work regarding adjusting for timezones and DST, across every region / DST regime. It becomes a big headache quickly if you try and solve this yourself, DST is very awkward.
Time maths is much more efficient and easier, especially in DB calls. All you need to do is numerical comparisons of seconds, rather than dealing with SQL functions etc etc.
No reliance on specific DB functions or engines; its just an integer value! Makes for much easier portability.

A word of caution, be careful of the maximum value in your DB integer column. Find the max/min values, convert them to date times (this is a useful tool) and check against your use case.

The final thing to say, is you can use the DateTime object to interpret time strings (as you are using now) in UTC, then set the timezone before printing them. It will work, but I feel it is much more prone to error.

I hope that helps :)

score 0 · Answer 3 · answered Apr 21 '20 at 09:07

Look at this SQL solution:

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

Result:

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

Store in UTC in SQLite and display in local timezone with PHP

3 Answers3