How to return local time SQL results using MariaDB

Question

This question has been asked (and answered) for MySQL multiple times, and I was certain those answers would apply to MariaDB as well, but either they don't, or more likely I am just doing something wrong. A few minutes after running these scripts, date returns Sat Feb 24 18:20:38 UTC 2018. I have since concluded that MySQL/MariaDB should remain configured for UTC under most circumstances, and I will not do differently. I did get things working as expected and have posted the results below.

<?php
//php.ini has set date.timezone =America/Los_Angeles

function displayTime($desc,$db) {
    echo("<h5>$desc</h5>");
    $stmt=$db->query('SELECT @@global.time_zone');
    echo 'MariaDb global.time_zone: '.$stmt->fetchColumn()."<br>";

    $stmt=$db->query('SELECT @@session.time_zone');
    echo 'MariaDb session.time_zone: '.$stmt->fetchColumn()."<br>";

    $stmt=$db->query('SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);');
    echo 'MariaDb offset: '.$stmt->fetchColumn()."<br>";

    $stmt=$db->query('SELECT tsValueUpdated FROM points WHERE id=6');
    echo 'Adjusted time: '.$stmt->fetchColumn()."<br>";
}

function getTimezoneFromDb() {
    $tzs = DateTimeZone::listIdentifiers();
    return $tzs[rand(0, count($tzs)-1)];
}

function getOffset() {
    $os=(new DateTime())->getOffset();
    if($os>(13*60*60)) $os=-24*60*60; //MySQL/MariaDB bug for Pacific/Kiritimati, Pacific/Chatham, and Pacific/Apia
    return $os >= 0?'+'.gmdate("G:i", $os):'-'.gmdate("G:i", -$os);
}

function test($sql, $value, $db) {
    $desc="Test for $sql using $value";
    $stmt=$db->prepare($sql);
    try{
        $stmt->execute([$value]);
        displayTime($desc,$db);
    }
    catch(PDOException $e) {
        echo("<h5>$desc</h5>".$e->getMessage().'<br>');
    }
}

//tsValueUpdated is type datetime and data was inserted using NOW()
$db=parse_ini_file(__DIR__.'/../config.ini',true)['mysql'];
$db=new PDO("mysql:host={$db['host']};dbname={$db['dbname']};charset={$db['charset']}",$db['username'],$db['password'],array(PDO::ATTR_EMULATE_PREPARES=>false,PDO::MYSQL_ATTR_USE_BUFFERED_QUERY=>true,PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION,PDO::ATTR_DEFAULT_FETCH_MODE=>PDO::FETCH_OBJ));

displayTime('Before changing timezone',$db);

$tz=getTimezoneFromDb();
echo "Timezone: $tz<br>";
date_default_timezone_set($tz);

displayTime('After changing PHP timezone',$db);

$os=getOffset();
echo "Offset: $os<br>";

//Reference https://stackoverflow.com/a/19069310/1032531
test('SET GLOBAL time_zone = ?', $os, $db);
test('SET GLOBAL time_zone = ?', $tz, $db);
test('SET @@global.time_zone = ?', $os, $db);
test('SET time_zone = ?', $os, $db);
test('SET time_zone = ?', $tz, $db);
test('SET @@session.time_zone = ?', $os, $db);

output

Before changing timezone
MariaDb global.time_zone: +06:00
MariaDb session.time_zone: +06:00
MariaDb offset: 06:00:00
Adjusted time: 2018-02-24 18:15:46
Timezone: Africa/Tripoli
After changing PHP timezone
MariaDb global.time_zone: +06:00
MariaDb session.time_zone: +06:00
MariaDb offset: 06:00:00
Adjusted time: 2018-02-24 18:15:46
Offset: +2:00
Test for SET GLOBAL time_zone = ? using +2:00
MariaDb global.time_zone: +02:00
MariaDb session.time_zone: +06:00
MariaDb offset: 06:00:00
Adjusted time: 2018-02-24 18:15:46
Test for SET GLOBAL time_zone = ? using Africa/Tripoli
SQLSTATE[HY000]: General error: 1298 Unknown or incorrect time zone: 'Africa/Tripoli'
Test for SET @@global.time_zone = ? using +2:00
MariaDb global.time_zone: +02:00
MariaDb session.time_zone: +06:00
MariaDb offset: 06:00:00
Adjusted time: 2018-02-24 18:15:46
Test for SET time_zone = ? using +2:00
MariaDb global.time_zone: +02:00
MariaDb session.time_zone: +02:00
MariaDb offset: 02:00:00
Adjusted time: 2018-02-24 18:15:46
Test for SET time_zone = ? using Africa/Tripoli
SQLSTATE[HY000]: General error: 1298 Unknown or incorrect time zone: 'Africa/Tripoli'
Test for SET @@session.time_zone = ? using +2:00
MariaDb global.time_zone: +02:00
MariaDb session.time_zone: +02:00
MariaDb offset: 02:00:00
Adjusted time: 2018-02-24 18:15:46

Command line testing

MariaDB [datalogger]> explain points;
+----------------+-------------+------+-----+---------+----------------+
| Field          | Type        | Null | Key | Default | Extra          |
+----------------+-------------+------+-----+---------+----------------+
| id             | int(11)     | NO   | PRI | NULL    | auto_increment |
| idPublic       | int(11)     | NO   | MUL | 0       |                |
| accountsId     | int(11)     | NO   | MUL | NULL    |                |
| name           | varchar(45) | NO   | MUL | NULL    |                |
| value          | float       | YES  |     | NULL    |                |
| valueOld       | float       | YES  |     | NULL    |                |
| units          | varchar(45) | YES  |     | NULL    |                |
| type           | char(8)     | NO   | MUL | NULL    |                |
| slope          | float       | NO   |     | 1       |                |
| intercept      | float       | NO   |     | 0       |                |
| tsValueUpdated | datetime    | YES  |     | NULL    |                |
+----------------+-------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)

MariaDB [datalogger]> SELECT tsValueUpdated FROM points WHERE id=6;
+---------------------+
| tsValueUpdated      |
+---------------------+
| 2018-02-24 18:09:46 |
+---------------------+
1 row in set (0.00 sec)

MariaDB [datalogger]> SET time_zone ='+12:00';
Query OK, 0 rows affected (0.00 sec)

MariaDB [datalogger]> SELECT tsValueUpdated FROM points WHERE id=6;
+---------------------+
| tsValueUpdated      |
+---------------------+
| 2018-02-24 18:09:46 |
+---------------------+
1 row in set (0.00 sec)

MariaDB [datalogger]> SET GLOBAL time_zone ='+12:00';
Query OK, 0 rows affected (0.00 sec)

MariaDB [datalogger]> SELECT tsValueUpdated FROM points WHERE id=6;
+---------------------+
| tsValueUpdated      |
+---------------------+
| 2018-02-24 18:09:46 |
+---------------------+
1 row in set (0.00 sec)

MariaDB [datalogger]>

mysqld --help --verbose | grep time-zone

2018-02-24 18:07:19 140183024801920 [Warning] Changed limits: max_open_files: 1024  max_connections: 151  table_cache: 431
2018-02-24 18:07:19 140183024801920 [Note] Plugin 'FEEDBACK' is disabled.
2018-02-24 18:07:19 140183024801920 [Warning] Could not open mysql.plugin table. Some options may be missing from the help text
  --default-time-zone=name
default-time-zone                                          (No default value)
system-time-zone 

EDIT. New research

<?php
date_default_timezone_set('America/Los_Angeles');

$config=parse_ini_file(__DIR__.'/../config.ini',true);
$db = $config['mysql'];
$db=new \PDO("mysql:host={$db['host']};dbname={$db['dbname']};charset={$db['charset']}",$db['username'],$db['password'],array(\PDO::ATTR_EMULATE_PREPARES=>false,\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY=>true,\PDO::ATTR_ERRMODE=>\PDO::ERRMODE_EXCEPTION,\PDO::ATTR_DEFAULT_FETCH_MODE=>\PDO::FETCH_OBJ));

$os=(new \DateTime())->getOffset();
if($os>(13*60*60)) $os=-24*60*60; //MySQL/MariaDB bug for Pacific/Kiritimati, Pacific/Chatham, and Pacific/Apia
$os = $os >= 0?'+'.gmdate("G:i", $os):'-'.gmdate("G:i", -$os);

$stmtSelect = $db->prepare("SELECT id, NOW() now, mydatetime FROM test WHERE id = ?");
$stmtInsert = $db->prepare("INSERT INTO test(id, mydatetime) VALUES(?,NOW())");
$stmtSelectConvert1 = $db->prepare("SELECT id, NOW() now, CONVERT_TZ(mydatetime, '+00:00', '$os') mydatetime FROM test WHERE id = ?");

$stmtInsert->execute([1]);

$stmtSelect->execute([1]);
echo("\n\nINSERTED BEFORE SETTING SQL TIMEZONE.  NO CONVERT\n");
print_r($stmtSelect->fetch());

$stmtSelectConvert1->execute([1]);
echo("\n\nINSERTED BEFORE SETTING SQL TIMEZONE.  YES CONVERT1\n");
print_r($stmtSelectConvert1->fetch());

echo("\n\nSET SQL TIMEZONE\n");
$db->exec("SET time_zone='$os';");

$stmtInsert->execute([2]);

$stmtSelectConvert2 = $db->prepare("SELECT id, NOW() now, CONVERT_TZ(mydatetime, '+00:00', '$os') mydatetime FROM test WHERE id = ?");

$stmtSelect->execute([1]);
echo("\n\nINSERTED BEFORE SETTING SQL TIMEZONE.  NO CONVERT\n");
print_r($stmtSelect->fetch());

$stmtSelectConvert1->execute([1]);
echo("\n\nINSERTED BEFORE SETTING SQL TIMEZONE.  YES CONVERT1\n");
print_r($stmtSelectConvert1->fetch());

$stmtSelectConvert2->execute([1]);
echo("\n\nINSERTED BEFORE SETTING SQL TIMEZONE.  YES CONVERT2\n");
print_r($stmtSelectConvert2->fetch());

$stmtSelect->execute([2]);
echo("\n\nINSERTED AFTER SETTING SQL TIMEZONE.  NO CONVERT\n");
print_r($stmtSelect->fetch());

$stmtSelectConvert1->execute([2]);
echo("\n\nINSERTED AFTER SETTING SQL TIMEZONE.  YES CONVERT1\n");
print_r($stmtSelectConvert1->fetch());

$stmtSelectConvert2->execute([2]);
echo("\n\nINSERTED AFTER SETTING SQL TIMEZONE.  YES CONVERT2\n");
print_r($stmtSelectConvert2->fetch());

OUTPUT

INSERTED BEFORE SETTING SQL TIMEZONE.  NO CONVERT
stdClass Object
(
    [id] => 1
    [now] => 2018-02-27 20:16:22
    [mydatetime] => 2018-02-27 20:16:22
)




INSERTED BEFORE SETTING SQL TIMEZONE.  YES CONVERT1
stdClass Object
(
    [id] => 1
    [now] => 2018-02-27 20:16:22
    [mydatetime] => 2018-02-27 12:16:22
)




SET SQL TIMEZONE




INSERTED BEFORE SETTING SQL TIMEZONE.  NO CONVERT
stdClass Object
(
    [id] => 1
    [now] => 2018-02-27 12:16:22
    [mydatetime] => 2018-02-27 20:16:22
)




INSERTED BEFORE SETTING SQL TIMEZONE.  YES CONVERT1
stdClass Object
(
    [id] => 1
    [now] => 2018-02-27 12:16:22
    [mydatetime] => 2018-02-27 12:16:22
)




INSERTED BEFORE SETTING SQL TIMEZONE.  YES CONVERT2
stdClass Object
(
    [id] => 1
    [now] => 2018-02-27 12:16:22
    [mydatetime] => 2018-02-27 12:16:22
)




INSERTED AFTER SETTING SQL TIMEZONE.  NO CONVERT
stdClass Object
(
    [id] => 2
    [now] => 2018-02-27 12:16:22
    [mydatetime] => 2018-02-27 12:16:22
)




INSERTED AFTER SETTING SQL TIMEZONE.  YES CONVERT1
stdClass Object
(
    [id] => 2
    [now] => 2018-02-27 12:16:22
    [mydatetime] => 2018-02-27 04:16:22
)




INSERTED AFTER SETTING SQL TIMEZONE.  YES CONVERT2
stdClass Object
(
    [id] => 2
    [now] => 2018-02-27 12:16:22
    [mydatetime] => 2018-02-27 04:16:22
)
NotionCommotion
Quote
MultiQuote
Edit

Answer 1

...or more likely I am just doing something wrong.

Bingo! :-)

Look here... (emphasis added):

MariaDB [datalogger]> explain points;
+----------------+-------------+------+-----+---------+----------------+
| Field          | Type        | Null | Key | Default | Extra          |
+----------------+-------------+------+-----+---------+----------------+
| id             | int(11)     | NO   | PRI | NULL    | auto_increment |
| idPublic       | int(11)     | NO   | MUL | 0       |                |
| accountsId     | int(11)     | NO   | MUL | NULL    |                |
| name           | varchar(45) | NO   | MUL | NULL    |                |
| value          | float       | YES  |     | NULL    |                |
| valueOld       | float       | YES  |     | NULL    |                |
| units          | varchar(45) | YES  |     | NULL    |                |
| type           | char(8)     | NO   | MUL | NULL    |                |
| slope          | float       | NO   |     | 1       |                |
| intercept      | float       | NO   |     | 0       |                |
| tsValueUpdated | datetime    | YES  |     | NULL    |                |
+----------------+-------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)

MariaDB has two different data types capable of storing date + time: DATETIME (which you're using) and TIMESTAMP (which you're not).

The key difference is that TIMESTAMP is converted from/to the session time_zone and stored in UTC, whereas DATETIME is simply stored and retrieved as provided (without any regard for timezone).

This is documented under Time zone effects:

Some functions are affected by the time zone settings. These include

• NOW()
• CURTIME()
• UNIX_TIMESTAMP()

as well as values stored and retrieved from TIMESTAMP columns. The latter are converted to UTC (Coordinated Universal Time) when stored, and converted back when retrieved.

Some functions are not affected. These include:

• UTC_TIMESTAMP()

as well as DATETIME, DATE and TIME columns.

Therefore no matter how much you fiddle with timezone settings, if your values are stored in a DATETIME column then they will always be retrieved exactly as entered. If you want MariaDB to handle conversion between the session timezones on insertion/retrieval, you must use a TIMESTAMP column instead.