Postgres에서 AT TIME ZONE 'UTC'를 두 번 써야 하는 이유, timestamptz가 timestamp로 바뀌는 함정
- Postgres의
AT TIME ZONE 'UTC'는 시간대를 바꾸는 게 아니라 timestamptz를 timestamp(without time zone) 로 형변환하며, 결과에+00이 붙지 않는다 - timestamp와 timestamptz를 비교하면 항상 false가 나오는데, timestamp는 실제 시점을 나타내지 않기 때문이며 같은 값에
EXTRACT(EPOCH ...)를 적용하면 둘 다 같은 숫자가 나와 더 헷갈린다 - 월 단위 계산은 시간대에 의존해서 UTC 기준 제품은
(b.month_start AT TIME ZONE 'UTC' + INTERVAL '1 months') AT TIME ZONE 'UTC'처럼 AT TIME ZONE을 두 번 적용해야 등호 비교가 의도대로 동작한다 - 댓글에 따르면 비교가 항상 false라는 설명은 틀렸고, 세션 TimeZone 설정에 따라 결과가 달라져 UTC 서버에서는 참, 캘리포니아 개발자 노트북에서는 거짓이 나온다
- Postgres 16에는
date_add(b.month_start, interval '1 month', 'UTC')처럼 시간대를 세 번째 인자로 받는 함수가 있어 두 번 변환할 필요가 없다
Hacker News opinions
혼란의 근원은 AT TIME ZONE 하나가 양방향 변환에 다 쓰이기 때문인 것 같아. timestamp로 갈 때는 AS LOCAL, timestamptz로 갈 때는 AS ZONED 같은 다른 문법이었으면 훨씬 직관적이었을 텐데.
AT TIME ZONE은 헷갈리긴 해도 내부적으로 일관성은 있어. TIMESTAMPTZ AT TIME ZONE 'X'는 X 시간대의 TIMESTAMP를 주고, TIMESTAMP AT TIME ZONE 'X'는 입력을 X 시간대로 취급해서 TIMESTAMPTZ를 돌려줘. 원리만 알면 그대로 동작해.
SQL 표준의 시간 처리는 정말 끔찍해. timestamp는 고유한 시점을 담지 않으니 이름부터 datetime이어야 맞아. Java Instant를 JDBC setTimestamp/getTimestamp로 주고받으면 구현이 아예 틀려서 데이터가 깨질 수도 있고.
ZonedDateTime은 instant 더하기 시간대가 아니야. 시간대는 반복되거나 아예 존재하지 않는 경우가 있어서 타임라인상 위치가 모호하고, 미래 시점은 오프셋이 언제든 바뀔 수 있어서 불확실성까지 있어.
SET TIME ZONE 'UTC'; 한 줄이면 되는 걸 갖고 너무 난리치는 거 아닌가. UTC나 시간대 개념에 익숙한 사람이면 다른 플랫폼에서 이미 겪어봤을 문제라 금방 고쳐.
월 더하기 자체가 28일 넘는 날짜에서는 date 타입으로도 잘 정의되지 않아. 시스템이 이런 계산을 일반적으로 허용하는 게 실수고, 도메인 규칙은 애플리케이션 코드에서 다뤄야 한다고 봐.
pgsql 글을 읽을수록 왜 옮겨야 하나 싶어. mariadb도 시간은 이상하지만 vacuum이나 autoincrement, 마이그레이션 문제까지 생각하면 Postgres가 겁나.
Postgres는 정합적이야. 이상해 보이는 것도 대부분 SQL 표준을 따르는 것이고, MySQL/MariaDB는 어디서 내 삶을 개선했는지 알 수가 없어.
나는 DB에서 시간 계산을 안 해. UTC로 저장하고 시간대는 애플리케이션에서 처리해. 경계 조건까지 유닛 테스트로 다 검증할 수 있으니 훨씬 낫더라.
UTC로 영업시간을 저장한 제품을 본 적 있는데, 1년에 두 번씩 스크립트로 모든 날짜를 다시 써야 했다더라.
그게 유일한 방법이지. 아니면 자기 시간대 밖 세상을 안 생각하는 코더라는 뜻이고, 좋은 관행이라고 보기 어려워.
그럼 글에 나온 조인 예제는 어캐 풀어? 두 테이블을 다 애플리케이션으로 읽어오나?
DB냐 애플리케이션이냐는 데이터와 연산을 같이 두느냐의 문제라, 때로는 DB 쪽이 유리한 경우도 분명 있어.
timestamp와 timestamptz 비교가 항상 false라는 설명은 틀렸어. Postgres는 세션 TimeZone으로 timestamp를 조용히 변환해. UTC 프로덕션에선 참, 캘리포니아 노트북에선 거짓이 나와. 이게 항상 false보다 더 나쁜 상황이지.
그리고 Postgres 16 쓰면 두 번 AT TIME ZONE 안 해도 돼. date_add(b.month_start, interval '1 month', 'UTC')가 시간대까지 받아서 timestamptz로 유지해줘.
라이브러리나 DB가 전역 상태로 로컬 시간과 시점을 자동 변환하는 건 정말 큰 죄악이야. C# 쓰면서 이것 때문에 여러 번 당했어.
timestamp는 시간대 정보를 안 담는다는 설명도 좀 오해를 부른다. timestamptz도 시간대를 저장하지 않고 그냥 epoch 초 값이야. 입력할 때 UTC로 변환되면서 시간대는 사라져.
시간대가 중요하면 별도 필드에 저장해야 해. 사용자 프로필에 현재 시간대를 두고 렌더링할 때 적용하거나, 레코드마다 어떤 벽시계 시간이었는지 남기거나.
사용자가 보낸 원본 날짜와 계산용 UTC를 따로 저장하는 게 답이야. 보여줄 땐 원본을 쓰고, 산술 연산은 UTC로 하고.
UTC를 유도해서 얻는 값이면 굳이 또 저장할 이유가 있나? 성능 때문이라면 materialized view 같은 걸로도 되고.