Postgre에서 어떤 타임스탬프 유형을 선택해야 합니까?SQL 데이터베이스?

Postgre에서 어떤 타임스탬프 유형을 선택해야 합니까?SQL 데이터베이스?

다중 시간대 프로젝트의 경우 Postgres 데이터베이스에 타임스탬프를 저장하는 모범 사례를 정의하고자 합니다.

제가 할 수 있어요.

1. 선택하세요.TIMESTAMP WITHOUT TIME ZONE 이 할 때 하세요.
2. 선택하세요.TIMESTAMP WITHOUT TIME ZONE 삽입 시합니다.
3. 선택하세요.TIMESTAMP WITH TIME ZONE를 그에 맞게 타임스탬프를 합니다.

저는 옵션 3(타임존이 있는 타임스탬프)을 약간 선호하지만 이 문제에 대해 교육적인 의견을 갖고 싶습니다.

우선, 포스트그레SQL의 시간 처리와 연산은 환상적이며 일반적인 경우 옵션 3이 좋습니다.그러나 이는 시간 및 시간대에 대한 불완전한 뷰이며, 다음과 같이 보완할 수 있습니다.

1. 사의시간이대사예환용로저으장합니다정설경자)으로 저장합니다.America/Los_Angeles,것은 아니다.-0700).
2. 이벤트UTC로부터의 이 가장 ).-0700).
3. 응용 프로그램에서 시간을 다음으로 변환합니다.UTC그리고 a를 사용하여 저장됩니다.TIMESTAMP WITH TIME ZONE기둥.
4. 로 시간 에서 ).UTCAmerica/Los_Angeles).
5. 데베이설을 합니다.timezoneUTC.

옵션이 하는 것은 의 시간대를 수 있기 을 사용하기 입니다. 왜냐하면 사용자의 시간대를 얻기가 어렵기 때문에 위험회피 권고를 사용하기 때문입니다.TIMESTAMP WITH TIME ZONE경량 용도에 적합합니다.그렇긴 하지만, 이 옵션 4의 몇 가지 배경 측면을 더 자세히 설명하겠습니다.

3과 로 옵3과의 , 그3는의 이유WITH TIME ZONE왜냐하면 어떤 일이 일어난 시간은 절대적인 순간이기 때문입니다. WITHOUT TIME ZONE상대 시간대를 산출합니다.절대 타임스탬프와 상대 타임스탬프를 절대로 혼합하지 마십시오.

프로그램 및 일관성의 관점에서 모든 계산이 UTC를 표준 시간대로 사용하여 수행되는지 확인합니다.이것은 포스트그레가 아닙니다.SQL이 필요하지만 다른 프로그래밍 언어 또는 환경과 통합할 때 도움이 됩니다.CHECK 0스크립트가 데이터를 파일로 덤프하고 다른 것이 어휘 정렬을 사용하여 시간 데이터를 정렬하는 등의 몇 가지 버그 클래스를 방지하는 방어 위치입니다., Postgre.SQL은 날짜 계산을 올바르게 수행하거나 시간대 간 변환(예: Postgre)을 위해 이 작업을 수행할 필요가 없습니다.SQL은 임의의 두 시간대 사이의 시간을 매우 능숙하게 변환합니다.데이터베이스로 들어가는 데이터를 오프셋 0으로 저장하려면:

CREATE TABLE my_tbl (
  my_timestamp TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  CHECK(EXTRACT(TIMEZONE FROM my_timestamp) = '0')
);
test=> SET timezone = 'America/Los_Angeles';
SET
test=> INSERT INTO my_tbl (my_timestamp) VALUES (NOW());
ERROR:  new row for relation "my_tbl" violates check constraint "my_tbl_my_timestamp_check"
test=> SET timezone = 'UTC';
SET
test=> INSERT INTO my_tbl (my_timestamp) VALUES (NOW());
INSERT 0 1

100% 완벽하지는 않지만 데이터가 이미 UTC로 변환되었는지 확인할 수 있는 충분히 강력한 발차기 방지 조치를 제공합니다.어떻게 해야 할지에 대한 의견들이 많지만, 제 경험으로 볼 때 이것이 실제 최고인 것 같습니다.

데이터베이스 시간대 처리에 대한 비판은 대체로 정당화됩니다(매우 무능하게 처리하는 데이터베이스가 많습니다). 그러나 PostgreSQL의 타임스탬프 및 시간대 처리는 몇 가지 "기능"에도 불구하고 매우 훌륭합니다.예를 들어, 다음과 같은 기능 중 하나가 있습니다.

-- Make sure we're all working off of the same local time zone
test=> SET timezone = 'America/Los_Angeles';
SET
test=> SELECT NOW();
              now              
-------------------------------
 2011-05-27 15:47:58.138995-07
(1 row)

test=> SELECT NOW() AT TIME ZONE 'UTC';
          timezone          
----------------------------
 2011-05-27 22:48:02.235541
(1 row)

:AT TIME ZONE 'UTC'인 시간대 정보를 합니다.TIMESTAMP WITHOUT TIME ZONE것.UTC).

에서 할 때TIMESTAMP WITHOUT TIME ZONETIMESTAMP WITH TIME ZONE연결에서 누락된 시간대가 상속됩니다.

test=> SET timezone = 'America/Los_Angeles';
SET
test=> SELECT EXTRACT(TIMEZONE_HOUR FROM NOW());
 date_part 
-----------
        -7
(1 row)
test=> SELECT EXTRACT(TIMEZONE_HOUR FROM TIMESTAMP WITH TIME ZONE '2011-05-27 22:48:02.235541');
 date_part 
-----------
        -7
(1 row)

-- Now change to UTC    
test=> SET timezone = 'UTC';
SET
-- Create an absolute time with timezone offset:
test=> SELECT NOW();
              now              
-------------------------------
 2011-05-27 22:48:40.540119+00
(1 row)

-- Creates a relative time in a given frame of reference (i.e. no offset)
test=> SELECT NOW() AT TIME ZONE 'UTC';
          timezone          
----------------------------
 2011-05-27 22:48:49.444446
(1 row)

test=> SELECT EXTRACT(TIMEZONE_HOUR FROM NOW());
 date_part 
-----------
         0
(1 row)

test=> SELECT EXTRACT(TIMEZONE_HOUR FROM TIMESTAMP WITH TIME ZONE '2011-05-27 22:48:02.235541');
 date_part 
-----------
         0
(1 row)

결론:

• 사용자의 표준 시간대를 명명된 레이블로 저장합니다(예:America/Los_Angeles ( ) 및 UTC와의 오프셋 (예:-0700)
• 0이 아닌 오프셋을 저장해야 하는 특별한 이유가 없는 한 모든 것에 UTC 사용
• 0이 아닌 모든 UTC 시간을 입력 오류로 처리
• 상대 타임스탬프와 절대 타임스탬프를 혼합하여 일치시키지 않음
• 또한 사용합니다.UTC▁the로서timezone하면

랜덤 프로그래밍 언어 참고:Python의 데이터 유형은 절대 시간과 상대 시간의 차이를 유지하는 데 매우 유용합니다(PyTZ와 같은 라이브러리로 보완하기 전까지는 처음에는 좌절감을 주지만).

---

편집

상대 대 절대의 차이를 조금 더 설명하겠습니다.

절대 시간은 이벤트를 기록하는 데 사용됩니다."filename: "filename 123 로그인졸또는시사은업 "식용자에 PST 2011-05-28 후오작 ▁regard."." 현지발생한 할 수 발생하는 것을 할 수 현지 시간대에 상관없이 이벤트가 발생한 곳으로 순간 이동할 수 있다면 이벤트가 발생하는 것을 목격할 수 있습니다.데이터베이스의 대부분의 시간 데이터는 절대적입니다(따라서 다음과 같이 해야 합니다).TIMESTAMP WITH TIME ZONE+0 오프셋과 오프셋이 아닌 특정 시간대를 지배하는 규칙을 나타내는 텍스트 레이블을 사용하는 것이 이상적입니다.

상대적인 사건은 아직 결정되지 않은 시간대의 관점에서 어떤 것의 시간을 기록하거나 일정을 잡는 것입니다.예: "우리 사업장의 문은 오전 8시에 열고 오후 9시에 닫는다", "매주 월요일 오전 7시에 만나 아침 식사 모임을 갖자" 또는 "매주 할로윈 오후 8시에 만나자".일반적으로 상대 시간은 템플릿이나 공장에서 이벤트에 사용되고 절대 시간은 다른 거의 모든 항목에 사용됩니다.상대적인 시간의 가치를 설명해야 하는 지적할 가치가 있는 드문 예외가 하나 있습니다.어떤 일이 발생할 수 있는 절대 시간에 대한 불확실성이 있을 수 있는 미래의 이벤트의 경우 상대적 타임스탬프를 사용합니다.여기 실제 사례가 있습니다.

2004년 10월 31일 1시).America/Los_Angeles/PST8PDT) 를 사용하여 을 참조하십시오.’2008-10-31 21:00:00.000000+00’::TIMESTAMP WITH TIME ZONE배달은 미국 정부가 일광 절약 시간을 관리하는 규칙을 변경한 2005년의 에너지 정책 법을 통과시켰기 때문에 오후 2시에 나타났을 것입니다.2004년에 배송이 예약된 날짜.10-31-2008태평양 표준시)였을입니다.+8000), 하지만 2005년부터 + 표준 시간대 데이터베이스는 다음을 인식했습니다.10-31-2008시간이었을 입니다.+0700). 의회의 조작에 않기 될 수 있습니다 상대 타임스탬프를 시간대와 함께 저장하면 의회의 잘못된 정보 조작에 영향을 받지 않기 때문에 올바른 배달 일정이 생성될 수 있습니다.일정을 잡기 위해 상대 시간 대 절대 시간을 사용하는 것 사이의 컷오프는 모호한 선이지만, 제 경험의 법칙에 따르면 3-6mo 이상의 미래에 대한 일정은 상대 타임스탬프를 사용해야 합니다(예: = 절대 시간 대 계획 시간 = 상대 시간)??).

은 상시마다른/의유막은형입니다.INTERVAL예: "사용자가 로그인한 후 20분이 지나면 세션이 시간 초과됩니다."안INTERVAL할 수 .TIMESTAMP WITH TIME ZONE 상대 는 다음과 같습니다.TIMESTAMP WITHOUT TIME ZONE " 후 후에 + 또는 될 수 있습니다( + "로그인 성공 후 사용자 세션이 20분 후에 만료됩니다(login_utc + session_duration)." 또는 "아침 조찬 모임은 60분 동안만 지속될 수 있습니다(recurring_start_time + meeting_length)."라고 말하는 것도 마찬가지입니다.

마막혼란지:DATE,TIME,TIME WITHOUT TIME ZONE그리고.TIME WITH TIME ZONE모두 관련 데이터 유형입니다. 예:'2011-05-28'::DATE자정을 식별하는 데 사용할 수 있는 표준 시간대 정보가 없으므로 상대 날짜를 나타냅니다.유하게사,'23:23:59'::TIME시간대나 시간대를 모르기 때문에 상대적입니다.DATE시간으로 표시되는을 가지고도'23:59:59-07'::TIME WITH TIME ZONE당신은 그것이 무엇인지 모릅니다.DATE되지요.그리고 마지막으로.DATE가 있는 시대가있것사아실닙이다니은는간▁in가 아닙니다.DATE은 그은것입니다.TIMESTAMP WITH TIME ZONE:

test=> SET timezone = 'America/Los_Angeles';
SET
test=> SELECT '2011-05-11'::DATE AT TIME ZONE 'UTC';
      timezone       
---------------------
 2011-05-11 07:00:00
(1 row)

test=> SET timezone = 'UTC';
SET
test=> SELECT '2011-05-11'::DATE AT TIME ZONE 'UTC';
      timezone       
---------------------
 2011-05-11 00:00:00
(1 row)

데이터베이스에 날짜와 시간대를 넣는 것은 좋은 일이지만, 미묘하게 잘못된 결과를 얻기 쉽습니다.시간 정보를 정확하고 완벽하게 저장하기 위해서는 최소한의 추가 작업이 필요하지만, 그렇다고 해서 항상 추가 작업이 필요한 것은 아닙니다.

션의 대답은 너무 복잡하고 오해의 소지가 있습니다.

실제로 "WITH TIME ZONE"과 "WITH TIME ZONE" 모두 값을 유닉스 계열의 절대 UTC 타임스탬프로 저장합니다.타임스탬프가 표시되는 방식에 차이가 있습니다."WITH time zone"의 경우 표시되는 값은 사용자의 zone으로 변환되는 UTC 저장 값입니다."WITH time zone(시간대 미포함)" 시 UTC 저장 값이 꼬여 사용자가 설정한 시간대와 상관없이 동일한 시계면을 표시합니다."

"WITH time zone(시간대 없음)"을 사용할 수 있는 유일한 상황은 실제 시간대에 관계없이 시계 얼굴 값이 적용되는 경우입니다.예를 들어, 타임스탬프가 투표소가 닫힐 수 있는 시간을 나타내는 경우(예:그들은 사람의 시간대에 상관없이 20:00에 문을 닫습니다.

선택 3을 사용합니다.특별한 이유가 없는 한 항상 "WITH time zone"을 사용하십시오.

제가 선호하는 것은 옵션 3입니다. 그러면 Postgres는 시간대와 관련된 타임스탬프를 재계산하는 많은 작업을 수행할 수 있는 반면, 다른 두 가지 작업은 직접 수행해야 합니다.타임스탬프를 시간대와 함께 저장하는 추가 스토리지 오버헤드는 수백만 개의 레코드를 사용하지 않는 한 무시할 수 없는 수준입니다. 이 경우 이미 스토리지 요구 사항이 상당히 많을 수 있습니다.

언급URL : https://stackoverflow.com/questions/6151084/which-timestamp-type-should-i-choose-in-a-postgresql-database