Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

How to return local time SQL results using MariaDB

Tags:

timezone

php

mysql

pdo

mariadb

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
like image 545
user1032531 Avatar asked Feb 24 '18 18:02

user1032531

People also ask

How do I get the TIMESTAMP in MariaDB?

The CURRENT_TIMESTAMP function will return the current date as a 'YYYY-MM-DD HH:MM:SS' format, if used in a string context. The CURRENT_TIMESTAMP function will return the current date as a YYYYMMDDHHMMSS format, if used in a numeric context.

How do I extract data from MariaDB?

The simplest way to retrieve data from MariaDB is to use the SELECT statement. Since the SELECT statement is an essential SQL statement, it has many options available with it. It's not necessary to know or use them all—you could execute very basic SELECT statements if that satisfies your needs.

1 Answers

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

like image 134
eggyal Avatar answered Oct 29 '22 13:10

eggyal
Sign in to Comment

Related questions

Laravel Illuminate\Validation\Rule not found
How to get the sucess message in the same page after submitting the contact form?
How to get custom header from HTTP response in Laravel 5?
Install PHP intl extension on MacOS [closed]
Cannot connect to google cloudsql from app engine
Eloquent - Call to undefined relationship
yii2 unique validator only when field not empty
Creating a real-time chat with php and javascript
Laravel append URI in route
How can I create a custom rule set for php-cs-fixer?
Cannot connect via PHP to docker mysql
Loading more eloquent hasMany relation queries through ajax in Laravel?
Laravel 5: How can I load database values into the config/services.php file?
Unable to exchange encryption keys
MySql bit columns returns weird large number in PHP 7.1 (not in previous versions)
Why does addition "+" of two strings in PHP produce this result?
Running php files stored on OneDrive
POSTing to HTTPS in Java, weird behaviour
Required field only if another field has a value, must be empty otherwise
Symfony 4 - The autoloader expected class […] to be defined in file

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!