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

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

---

미국 / 뉴욕 시간대를 사용하고 있습니다. 가을에는 한 시간 "폴백"하여 오전 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'이 가능한 2 개의 다른 시간에 매핑되기 때문).

With DATETIME you can store your data in any timezone you want and be confident that you'll get back whatever you send it (you don't get forced into the lossy roundtrip conversions that TIMESTAMP fields foist on you). So the solution is to use a DATETIME field and before saving to the field convert from your system time zone into whatever non-DST zone you want to save it in (I think UTC is probably the best option). This allows you to build the conversion logic into your scripting language so that you can explicitly save the UTC equivalent of "2009-11-01 01:30:00 -04:00" or ""2009-11-01 01:30:00 -05:00".

Another important thing to note is that MySQL's date/time math functions don't work properly around DST boundaries if you store your dates in a DST TZ. So all the more reason to save in UTC.

In a nutshell I now do this:

When retrieving the data from the database:

Explicitly interpret the data from the database as UTC outside of MySQL in order to get an accurate Unix timestamp. I use PHP's strtotime() function or its DateTime class for this. It can not be reliably done inside of MySQL using MySQL's CONVERT_TZ() or UNIX_TIMESTAMP() functions because CONVERT_TZ will only output a 'YYYY-MM-DD hh:mm:ss' value which suffers from ambiguity problems, and UNIX_TIMESTAMP() assumes its input is in the system timezone, not the timezone the data was ACTUALLY stored in (UTC).

When storing the data to the database:

Convert your date to the precise UTC time that you desire outside of MySQL. For example: with PHP's DateTime class you can specify "2009-11-01 1:30:00 EST" distinctly from "2009-11-01 1:30:00 EDT", then convert it to UTC and save the correct UTC time to your DATETIME field.

Phew. Thanks so much for everyone's input and help. Hopefully this saves someone else some headaches down the road.

BTW, I am seeing this on MySQL 5.0.22 and 5.0.27

---

I think micahwittman's link has the best practical solution to these MySQL limitations: Set the session timezone to UTC when you connect:

SET SESSION time_zone = '+0:00'

Then you just send it Unix timestamps and everything should be fine.

---

이 스레드는 변경된 레코드를 추적하고 데이터웨어 하우스에 대한 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 inherently solves this problem using time_zone_name table from mysql db. Use CONVERT_TZ while CRUD to update the datetime without worrying about daylight savings time.

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;

---

I was working on logging counts of visits of pages and displaying the counts in graph (using Flot jQuery plugin). I filled the table with test data and everything looked fine, but I noticed that at the end of the graph the points were one day off according to labels on x-axis. After examination I noticed that the view count for day 2015-10-25 was retrieved twice from the database and passed to Flot, so every day after this date was moved by one day to right.
After looking for a bug in my code for a while I realized that this date is when the DST takes place. Then I came to this SO page...
...but the suggested solutions was an overkill for what I needed or they had other disadvantages. I am not very worried about not being able to distinguish between ambiguous timestamps. I just need to count and display records per days.

First, I retrieve the date range:

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

Then, in a for loop, starting with min_date, ending with max_date, by step of one day (60*60*24), I'm retrieving the counts:

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
}

My final and quick solution to my problem was this:

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

So I am not staring the loop in the beginning of the day, but two hours later. The day is still the same, and I am still retrieving correct counts, since I explicitly ask the database for records between 00:00:00 and 23:59:59 of the day, regardless of the actual time of the timestamp. And when the time jumps by one hour, I am still in the correct day.

Note: I know this is 5 year old thread, and I know this is not an answer to OPs question, but it might help people like me who encountered this page looking for solution to the problem I described.

참고URL : https://stackoverflow.com/questions/1646171/mysql-datetime-fields-and-daylight-savings-time-how-do-i-reference-the-extra