如何使用MariadB返回本地时间SQL结果

多次向MySQL提出(并回答(这个问题，我确定这些答案也适用于Mariadb只是做错了什么。运行这些脚本几分钟后，date返回Sat Feb 24 18:20:38 UTC 2018。从那以后，我得出的结论是，MySQL/Mariadb应该在大多数情况下为UTC配置，并且我不会有所不同。我确实使事情正常工作，并在下面发布了结果。

<?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);

输出

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

命令行测试

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时区

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

编辑。新研究

<?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("nnINSERTED BEFORE SETTING SQL TIMEZONE.  NO CONVERTn");
print_r($stmtSelect->fetch());
$stmtSelectConvert1->execute([1]);
echo("nnINSERTED BEFORE SETTING SQL TIMEZONE.  YES CONVERT1n");
print_r($stmtSelectConvert1->fetch());
echo("nnSET SQL TIMEZONEn");
$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("nnINSERTED BEFORE SETTING SQL TIMEZONE.  NO CONVERTn");
print_r($stmtSelect->fetch());
$stmtSelectConvert1->execute([1]);
echo("nnINSERTED BEFORE SETTING SQL TIMEZONE.  YES CONVERT1n");
print_r($stmtSelectConvert1->fetch());
$stmtSelectConvert2->execute([1]);
echo("nnINSERTED BEFORE SETTING SQL TIMEZONE.  YES CONVERT2n");
print_r($stmtSelectConvert2->fetch());
$stmtSelect->execute([2]);
echo("nnINSERTED AFTER SETTING SQL TIMEZONE.  NO CONVERTn");
print_r($stmtSelect->fetch());
$stmtSelectConvert1->execute([2]);
echo("nnINSERTED AFTER SETTING SQL TIMEZONE.  YES CONVERT1n");
print_r($stmtSelectConvert1->fetch());
$stmtSelectConvert2->execute([2]);
echo("nnINSERTED AFTER SETTING SQL TIMEZONE.  YES CONVERT2n");
print_r($stmtSelectConvert2->fetch());

输出

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

...或更可能只是做错了什么。

宾果游戏！： - (

在这里看...(添加了强调(：

mariadb [datalogger]>解释点； ------------------- --------------------- ------- ----- ------------------- ---------------------- |字段|类型|null |键|默认|额外| ------------------- --------------------- ------- ----- ------------------- ---------------------- |id |int(11(|否|pri |null |auto_increment ||IDPUBLIC |int(11(|否|mul |0 |||帐户|int(11(|否|mul |null |||名称|Varchar(45(|否|mul |null |||值|浮点|是||null |||valueold |浮点|是||null |||单位|Varchar(45(|是||null |||类型|char(8(|否|mul |null |||坡度|浮点|否||1 |||拦截|浮点|否||0 |||tsvalueupdated | DateTime  |是||null || ------------------- --------------------- ------- ----- ------------------- ---------------------- 11行(0.00秒(

Mariadb有两种能够存储日期时间的数据类型：DATETIME(您正在使用(和TIMESTAMP(您不是(。

关键区别在于，TIMESTAMP从/转换为session time_zone并存储在UTC中，而DATETIME只是按照提供的简单存储和检索(不考虑TimeZone (。(。

。

这是在时区效应下记录的：

某些功能受时区设置的影响。这些包括
NOW()
CURTIME()
UNIX_TIMESTAMP()
以及从TIMESTAMP列存储和检索的值。后者在存储时将后者转换为UTC(协调的通用时间(，并在检索时转换回去。
某些功能不受影响。这些包括：
UTC_TIMESTAMP()
以及DATETIME，DATE和TIME列。

因此，无论您使用多少时区设置，如果您的值存储在DATETIME列中，则始终将它们完全按照输入而被检索。如果您希望Mariadb在插入/检索上处理会话时区之间的转换，则必须使用TIMESTAMP列。

相关内容

最新更新

热门标签：