[php] MySQL datetime 필드 및 일광 절약 시간 — “추가”시간을 어떻게 참조합니까?

Program / 글쓴이

미국 / 뉴욕 시간대를 사용하고 있습니다. 가을에는 한 시간 “폴백”하여 오전 2시에 효과적으로 “이득”합니다. 전환 지점에서 다음이 발생합니다.

01:59:00 -04 : 00
, 1 분 후 :
01:00:00 -05 : 00

따라서 단순히 “1:30 am”이라고 말하면 첫 번째 시간 1:30이 롤백되는지 아니면 두 번째인지 여부가 모호합니다. 예약 데이터를 MySQL 데이터베이스에 저장하려고하는데 시간을 올바르게 저장하는 방법을 결정할 수 없습니다.

문제는 다음과 같습니다.
“2009-11-01 00:30:00″은 내부적으로 2009-11-01 00:30:00 -04 : 00
“2009-11-01 01:30:00″은 내부적으로 다음과 같이 저장됩니다. 2009-11-01 01:30:00 -05 : 00

이것은 훌륭하고 상당히 기대됩니다. 하지만 01:30:00 -04 : 00에 어떻게 저장 합니까? 문서 I는 그것을 정당하게 무시 된 것 오프셋 지정 시도한 경우는, 따라서, 오프셋 및 지정에 대한 지원을 표시하지 않습니다.

내가 생각한 유일한 솔루션은 일광 절약 시간을 사용하지 않는 시간대로 서버를 설정하고 스크립트에서 필요한 변환을 수행하는 것입니다 (이를 위해 PHP를 사용하고 있습니다). 그러나 그것은 필요한 것 같지 않습니다.

모든 제안에 감사드립니다.

답변

MySQL의 날짜 유형은 솔직히 깨져서 시스템이 UTC 또는 GMT-5와 같은 상수 오프셋 시간대로 설정되지 않는 한 모든 시간을 올바르게 저장할 수 없습니다 . (MySQL 5.0.45를 사용하고 있습니다)

일광 절약 시간이 끝나기 전 1 시간 동안은 시간을 저장할 수 없기 때문 입니다. 날짜를 입력하는 방법에 관계없이 모든 날짜 함수는 이러한 시간을 전환 후 한 시간 동안처럼 처리합니다.

내 시스템의 시간대는 America/New_York입니다. 1257051600 (Sun, 01 Nov 2009 06:00:00 +0100)을 저장해 봅시다.

다음은 독점적 인 INTERVAL 구문을 사용합니다.

SELECT UNIX_TIMESTAMP('2009-11-01 00:00:00' + INTERVAL 3599 SECOND); # 1257051599
SELECT UNIX_TIMESTAMP('2009-11-01 00:00:00' + INTERVAL 3600 SECOND); # 1257055200

SELECT UNIX_TIMESTAMP('2009-11-01 01:00:00' - INTERVAL 1 SECOND); # 1257051599
SELECT UNIX_TIMESTAMP('2009-11-01 01:00:00' - INTERVAL 0 SECOND); # 1257055200

심지어 FROM_UNIXTIME()정확한 시간을 반환하지 않습니다.

SELECT UNIX_TIMESTAMP(FROM_UNIXTIME(1257051599)); # 1257051599
SELECT UNIX_TIMESTAMP(FROM_UNIXTIME(1257051600)); # 1257055200

이상하게도 DATETIME 은 DST가 시작되는 “잃어버린”시간 (예 🙂 내에 시간을 저장하고 반환합니다 (문자열 형식으로 만! 2009-03-08 02:59:59). 그러나 MySQL 함수에서 이러한 날짜를 사용하는 것은 위험합니다.

SELECT UNIX_TIMESTAMP('2009-03-08 01:59:59'); # 1236495599
SELECT UNIX_TIMESTAMP('2009-03-08 02:00:00'); # 1236495600
# ...
SELECT UNIX_TIMESTAMP('2009-03-08 02:59:59'); # 1236495600
SELECT UNIX_TIMESTAMP('2009-03-08 03:00:00'); # 1236495600

요점 : 일년 중 매번 저장하고 검색 해야하는 경우 몇 가지 바람직하지 않은 옵션이 있습니다.

  1. 시스템 시간대를 GMT + 일정한 오프셋으로 설정합니다. 예 : UTC

  2. 날짜를 INT로 저장 (Aaron이 발견했듯이 TIMESTAMP는 신뢰할 수 없음)

  3. DATETIME 유형에 일정한 오프셋 시간대가 있다고 가정하십시오. 예를 들어에있는 경우 MySQL 외부의America/New_York GMT-5로 날짜를 변환 한 다음 DATETIME으로 저장합니다 (필수적임이 밝혀졌습니다 : Aaron의 답변 참조). 그런 다음 MySQL의 날짜 / 시간 함수를 사용하는 데주의를 기울여야합니다. 일부는 값이 시스템 시간대라고 가정하고 다른 일부 (특히 시간 산술 함수)는 “시간대에 구애받지 않음”(시간이 UTC 인 것처럼 동작 할 수 있음)이기 때문입니다.

Aaron과 저는 자동 생성 TIMESTAMP 열도 손상되었다고 생각합니다. 2009-11-01 01:30 -0400및 둘 다 2009-11-01 01:30 -0500모호한 2009-11-01 01:30.

답변

내 목적을 위해 알아 냈어요. 제가 배운 내용을 요약하겠습니다 (죄송합니다.이 메모는 장황합니다. 다른 어떤 것보다도 향후 추천을위한 것입니다).

이전 의견 중 하나에서 말한 것과 달리 DATETIME 및 TIMESTAMP 필드 는 다르게 작동합니다. TIMESTAMP 필드 (문서에서 알 수 있듯이)는 “YYYY-MM-DD hh : mm : ss”형식으로 보내는 모든 항목을 가져와 현재 시간대에서 UTC 시간으로 변환합니다. 그 반대는 데이터를 검색 할 때마다 투명하게 발생합니다. DATETIME 필드는이 변환을 수행하지 않습니다. 그들은 당신이 보내는 모든 것을 가져다가 직접 저장합니다.

DATETIME 및 TIMESTAMP 필드 유형 모두 DST를 준수하는 시간대의 데이터를 정확하게 저장할 수 없습니다. . “2009-11-01 01:30:00″을 저장하면 필드에서 원하는 오전 1시 30 분 버전 (-04 : 00 또는 -05 : 00 버전)을 구분할 방법이 없습니다.

좋습니다. 따라서 데이터를 비 DST 시간대 (예 : UTC)에 저장해야합니다. TIMESTAMP 필드는 설명 할 이유 때문에이 데이터를 정확하게 처리 할 수 ​​없습니다. 시스템이 DST 시간대로 설정되어있는 경우 TIMESTAMP에 입력 한 내용이 반환되지 않을 수 있습니다. 이미 UTC로 변환 한 데이터를 보내더라도 데이터가 현지 시간대에 있다고 가정하고 또 다른 UTC로 변환합니다. 이 TIMESTAMP 적용 로컬 -UTC- 백-로-로컬 왕복은 현지 시간대가 DST를 준수 할 때 손실이 발생합니다 ( ‘2009-11-01 01:30:00’이 가능한 두 시간에 매핑되기 때문).

DATETIME을 사용하면 원하는 시간대에 데이터를 저장할 수 있으며 전송 한 데이터를 모두 되 찾을 수 있습니다 (TIMESTAMP 필드가 제공하는 손실 왕복 변환에 강요되지 않음). 따라서 해결책은 DATETIME 필드를 사용하고 필드에 저장하기 전에 시스템 시간대에서 저장하려는 비 DST 영역으로 변환하는 것입니다 (UTC가 아마도 가장 좋은 옵션이라고 생각합니다). 이를 통해 “2009-11-01 01:30:00 -04 : 00″또는 “”2009-11-01 01:30 “에 해당하는 UTC를 명시 적으로 저장할 수 있도록 스크립트 언어로 변환 논리를 빌드 할 수 있습니다. 00 -05 : 00 “

주목해야 할 또 다른 중요한 점은 날짜를 DST TZ에 저장하면 MySQL의 날짜 / 시간 수학 함수가 DST 경계에서 제대로 작동하지 않는다는 것입니다. 따라서 UTC로 저장할 더 많은 이유가 있습니다.

요약하자면 이제 이렇게합니다.

데이터베이스에서 데이터를 검색 할 때 :

정확한 Unix 타임 스탬프를 얻기 위해 데이터베이스의 데이터를 MySQL 외부의 UTC로 명시 적으로 해석합니다. 이를 위해 PHP의 strtotime () 함수 또는 DateTime 클래스를 사용합니다. CONVERT_TZ는 모호성 문제가있는 ‘YYYY-MM-DD hh : mm : ss’값만 출력하고 UNIX_TIMESTAMP ()는이를 가정하기 때문에 MySQL의 CONVERT_TZ () 또는 UNIX_TIMESTAMP () 함수를 사용하여 MySQL 내부에서 안정적으로 수행 할 수 없습니다. 입력은 데이터가 실제로 (UTC)에 저장된 시간대가 아니라 시스템 시간대에 있습니다.

데이터를 데이터베이스에 저장할 때 :

MySQL 외부에서 원하는 정확한 UTC 시간으로 날짜를 변환하십시오. 예 : PHP의 DateTime 클래스를 사용하면 “2009-11-01 1:30:00 EDT”와는 별도로 “2009-11-01 1:30:00 EST”를 지정한 다음 UTC로 변환하고 정확한 UTC 시간을 저장할 수 있습니다. DATETIME 필드에.

휴. 모든 사람의 의견과 도움에 감사드립니다. 바라건대 이것은 다른 사람의 두통을 덜어주기를 바랍니다.

BTW, MySQL 5.0.22 및 5.0.27에서 이것을보고 있습니다.

답변

micahwittman의 링크 가 이러한 MySQL 제한 사항에 대한 가장 실용적인 솔루션 이라고 생각 합니다. 연결할 때 세션 시간대를 UTC로 설정합니다.

SET SESSION time_zone = '+0:00'

그런 다음 Unix 타임 스탬프를 보내면 모든 것이 정상입니다.

답변

이 스레드는 변경된 레코드를 추적하고 데이터웨어 하우스에 대한 ETL을 추적하기 위해 (예 🙂 TIMESTAMP열을 사용하기 때문에 나를 괴롭 혔습니다.On UPDATE CURRENT_TIMESTAMPrecordTimestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

누군가 궁금해하는 경우이 경우 TIMESTAMP올바르게 작동 TIMESTAMP하고을 유닉스 타임 스탬프 로 변환하여 두 개의 유사한 날짜를 구별 할 수 있습니다 .

select TestFact.*, UNIX_TIMESTAMP(recordTimestamp) from TestFact;

id  recordTimestamp         UNIX_TIMESTAMP(recordTimestamp)
1   2012-11-04 01:00:10.0   1352005210
2   2012-11-04 01:00:10.0   1352008810

답변

하지만 01:30:00 -04 : 00까지 어떻게 저장합니까?

다음과 같이 UTC로 변환 할 수 있습니다.

SELECT CONVERT_TZ('2009-11-29 01:30:00','-04:00','+00:00');

더 좋은 방법은 날짜를 TIMESTAMP 필드 로 저장하는 것 입니다. 그것은 항상 UTC로 저장되며 UTC는 여름 / 겨울 시간에 대해 알지 못합니다.

CONVERT_TZ를 사용하여 UTC에서 현지 시간으로 변환 할 수 있습니다 .

SELECT CONVERT_TZ(UTC_TIMESTAMP(),'+00:00','SYSTEM');

여기서 ‘+00 : 00’은 UTC, from timezone, ‘SYSTEM’은 MySQL이 실행되는 OS의 현지 시간대입니다.

답변

Mysql은 본질적으로 mysql db의 time_zone_name 테이블을 사용하여이 문제를 해결합니다. CRUD 동안 CONVERT_TZ를 사용하여 일광 절약 시간에 대한 걱정없이 datetime을 업데이트하십시오.

SELECT
  CONVERT_TZ('2019-04-01 00:00:00','Europe/London','UTC') AS time1,
  CONVERT_TZ('2019-03-01 00:00:00','Europe/London','UTC') AS time2;

답변

나는 페이지 방문 수를 기록하고 그래프에 수를 표시하는 작업을하고있었습니다 (Flot jQuery 플러그인 사용). 나는 테스트 데이터로 테이블을 채웠고 모든 것이 괜찮아 보였지만 그래프의 끝에서 x 축의 레이블에 따라 포인트가 하루 쉬었다는 것을 알았습니다. 검사 후 2015-10-25 일에 대한 조회수가 데이터베이스에서 두 번 검색되어 Flot에 전달되었으므로이 날짜 이후의 매일이 오른쪽으로 하루 씩 이동했습니다.
잠시 동안 내 코드에서 버그를 찾은 후이 날짜가 DST가 발생하는 날짜라는 것을 깨달았습니다. 그런 다음 나는이 SO 페이지에 왔습니다 …
… 제안 된 솔루션은 내가 필요한 것에 대한 과잉이거나 다른 단점이 있습니다. 모호한 타임 스탬프를 구별 할 수없는 것에 대해별로 걱정하지 않습니다.하루에 기록을 세고 표시하면됩니다.

먼저 날짜 범위를 검색합니다.

SELECT
    DATE(MIN(created_timestamp)) AS min_date,
    DATE(MAX(created_timestamp)) AS max_date
FROM page_display_log
WHERE item_id = :item_id

그런 다음 for 루프에서으로 시작하여 min_date, max_date하루 단위 ( 60*60*24)로 끝나는 횟수를 검색합니다.

for( $day = $min_date_timestamp; $day <= $max_date_timestamp; $day += 60 * 60 * 24 ) {
    $query = "
        SELECT COUNT(*) AS count_per_day
        FROM page_display_log
        WHERE
            item_id = :item_id AND
            (
                created_timestamp BETWEEN
                '" . date( "Y-m-d 00:00:00", $day ) . "' AND
                '" . date( "Y-m-d 23:59:59", $day ) . "'
            )
    ";
    //execute query and do stuff with the result
}

내 최종 빠른 해결책 을 내 문제는 이것입니다 :

$min_date_timestamp += 60 * 60 * 2; // To avoid DST problems
for( $day = $min_date_timestamp; $day <= $max_da.....

그래서 나는 하루가 시작될 때가 아니라 두 시간 후에 루프를 쳐다보고 있습니다. 그날은 여전히 ​​똑같고, 타임 스탬프의 실제 시간에 관계없이 데이터베이스에 하루의 00:00:00에서 23:59:59 사이의 레코드를 명시 적으로 요청하기 때문에 정확한 카운트를 검색하고 있습니다. 그리고 시간이 한 시간 씩 뛰더라도 나는 여전히 올바른 날입니다.

참고 : 이것이 5 년 된 스레드라는 것을 알고 있으며 이것이 OP 질문에 대한 대답이 아니라는 것을 알고 있지만이 페이지를 만난 저와 같은 사람들이 제가 설명한 문제에 대한 해결책을 찾는 데 도움이 될 수 있습니다.