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

You tried to change the time_zone directly it in Maria's my.cnf file? — Matheus Sant Anna de Oliveira, Feb 27 '18 at 10:45
`default_time_zone=Europe/Paris` [https://stackoverflow.com/questions/26349714/mariadb-set-timezone-in-config](https://stackoverflow.com/questions/26349714/mariadb-set-timezone-in-config) — Matheus Sant Anna de Oliveira, Feb 27 '18 at 10:52
Maybe I fail to see your problem. MariaDB uses system timezone. You can change it by going to /etc/my.cnf.d/mariadb-server.cnf , in [server] add "default-time-zone=+00:00" for UTC or what you need. — Paun Narcis Iulian, Feb 27 '18 at 13:47
(1) What is the Question? (2) What part of the output is not what you expect? (3) What is the point of "A few minutes after running these scripts, date returns ..."? — Rick James, Feb 27 '18 at 15:50
@MatheusOliveira Another question, but I don't seem to have one. Please see https://dba.stackexchange.com/questions/198737/next-step-to-configuring-mariadb-after-successful-installation. — user1032531, Feb 27 '18 at 20:27
@PaunNarcisIulian I read some silly blogs that recommended changing MariaDB's timezone via PHP. After spending a bit of time, I do not believe doing so is good advise unless the DB is just for reading,. — user1032531, Feb 27 '18 at 20:30
@RickJames It was returning UTC time regardless. I ended up abandoning the approach as it appears that if the DB is ever used for writing, one is way better sticking with UTC. I did mess around quite a bit now understand better and will post my results under this question. — user1032531, Feb 27 '18 at 20:32

score 7 · Answer 1 · answered Mar 03 '18 at 13:18

...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.

Thanks eggyal, I found I could set the timezone before inserting `NOW()` in a record and the value would be local time, however, this is never or rarely a good thing. Instead, I've been saving `DATETIME` using UTC time, retrieving using `CONVERT_TZ()` if local time is desired. — user1032531, Mar 04 '18 at 13:25
@user1032531: I’d recommend using `TIMESTAMP` and saving yourself the hassle (plus saving anyone who comes back to it in future trying to remember how it all works), but that’s up to you. — eggyal, Mar 04 '18 at 13:26

How to return local time SQL results using MariaDB

1 Answers1