Maria를 사용하여 현지 시간 SQL 결과를 반환하는 방법DB

it-source

Maria를 사용하여 현지 시간 SQL 결과를 반환하는 방법DB

criticalcode 2022. 12. 9. 21:52

Maria를 사용하여 현지 시간 SQL 결과를 반환하는 방법DB

이 질문은 MySQL에 대해 여러 번 질문(및 답변)되었으며, 이러한 답변이 MariaDB에도 적용될 것이라고 확신했습니다. 하지만 그렇지 않거나 제가 잘못된 일을 하고 있을 가능성이 높습니다. 몇 분 에, 「」가 됩니다.dateSat 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("\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());

산출량

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]> 포인트 설명+----------------+-------------+------+-----+---------+----------------+| 필드 | 유형 | 특수한 순서 | 키 | 기본값 | 추가 |+----------------+-------------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || id Public | int(11) | NO | MUL | 0 | || accountsId | int(11) | NO | MUL | NULL | || name | varchar (45) | NO | MUL | NULL | || value | float | YES | | NULL | || 오래된 값 | float | YES | | NULL | || 유닛 | varchar (45) | YES | | NULL ||| type | char (8) | NO | MUL | NULL | || 슬로프 | 플로트 | NO || 1 ||| 가로채기 | float | NO | | 0 | || ts Value Updated | datetime | YES | 특수 | ||+----------------+-------------+------+-----+---------+----------------+11행 세트(0.00초)

MariaDB에는 날짜와 시간을 저장할 수 있는 두 가지 데이터 유형(사용 중인 데이터 유형)과 사용하지 않는 데이터 유형(사용하지 않는 데이터 유형)이 있습니다.

중요한 차이점은 이라는 것이다.TIMESTAMP세션에서/세션으로 변환됩니다.time_zoneUTC에 저장되어 있는데 반해DATETIME는 (타임존에 관계없이) 지정된 대로 단순히 저장 및 취득됩니다.

이것은, 「타임 존 효과」에 기재되어 있습니다.

일부 기능은 시간대 설정의 영향을 받습니다.여기에는 다음이 포함됩니다.

NOW()

CURTIME()

UNIX_TIMESTAMP()

또한 열에서 저장 및 검색된 값도 포함됩니다.후자는 저장 시 UTC(Conordinated Universal Time)로 변환되고 취득 시 다시 변환됩니다.

일부 기능은 영향을 받지 않습니다.여기에는 다음이 포함됩니다.

UTC_TIMESTAMP()

, 및 컬럼을 지정합니다.

따라서 타임존 설정을 아무리 만지작거려도 값이 에 저장되어 있는 경우DATETIME그러면 항상 정확하게 입력된 대로 검색됩니다.삽입/취득 시 MariaDB가 세션타임존 간의 변환을 처리하도록 하려면TIMESTAMP를 누릅니다.

언급URL : https://stackoverflow.com/questions/48966248/how-to-return-local-time-sql-results-using-mariadb

'it-source' 카테고리의 다른 글

JavaScript 알림 상자의 새 줄 (0)	2022.12.09
MariaDB 교착 상태 방지 (0)	2022.12.09
#1055 - SELECT 목록의 식이 GROUP BY 절에 없으며 집계되지 않은 열이 포함되어 있습니다. 이 열은 sql_mode=only_full_group_by와 호환되지 않습니다. (0)	2022.12.09
Python에서 정렬된 배열의 인덱스를 가져오는 방법 (0)	2022.12.09
참조 - 이 기호는 PHP에서 무엇을 의미합니까? (0)	2022.12.09

현재글Maria를 사용하여 현지 시간 SQL 결과를 반환하는 방법DB

각종 프로그래밍 정보를 다루는 블로그입니다.

sql-server, JQuery, mariaDB, AJAX, javascript, C, git, Android, Wordpress, Powershell, AngularJS, Python, mysql, oracle, java, spring-boot, reactjs, Excel, PHP, JSON,

Today :
Yesterday :

criticalcode

Maria를 사용하여 현지 시간 SQL 결과를 반환하는 방법DB

Maria를 사용하여 현지 시간 SQL 결과를 반환하는 방법DB

'it-source' 카테고리의 다른 글

'it-source'의 다른글

티스토리툴바

« 2025/03 »
일	월	화	수	목	금	토
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31

Maria를 사용하여 현지 시간 SQL 결과를 반환하는 방법DB

Maria를 사용하여 현지 시간 SQL 결과를 반환하는 방법DB

'it-source' 카테고리의 다른 글

'it-source'의 다른글

관련글

티스토리툴바