Sqlalchemy를 통한 postgresql timezone 설정

SQLAlchemy

개발을 하다보면 날짜를 다루는 일을 꽤 많이 하게 된다. 그만큼 중요한 업무이기도 하다.

보통 테이블을 설계할 때 Row 단위로 해당 Row가 생성된 일시와 수정된 일시, 그리고 삭제된 일시를 자동으로 남기도록 한다.

이 때 각 데이터베이스마다 날짜를 다루는 컬럼 타입이 조금씩 상이하다.

mysql에서는 timezone이라는 개념이 없이 timestamp, datetime, date 등의 형태로 지정한다.

이와 다르게 postgresql에서는 timestamp에 timezone을 같이 저장하는 형태와 저장하지 않고 timestamp만 저장하는 형태 두 가지로 구분하여서 생성한다.

국내용으로 개발할 때는 굳이 timezone을 저장할 필요없이 timestamp만 저장하면 될 것이고 해외도 같이 서비스를 할 경우 각 국가별 timezone을 반영하여야 하기 때문에 timezone 값을 같이 저장하여야 한다.

컬럼을 선언할 때는 "timestamp with time zone"과 "timestamp without time zone"으로 선언하여서 사용하게 된다.

얼마전 회사에서 postgresql에 테이블을 설계하였는데 늘 하던 습관과 같이 timestamp 값을 without time zone으로 선언하였다.

로컬에서 개발할 때는 어차피 timezone이 Asis/Seoul로 되어 있으니 이렇게 선언하여서 사용해도 무관하다.

이전에 테이블을 설계할 때도 계속 이렇게 했는데 이 때는 데이터베이스 서버를 별도로 구축하여서 사용하였기 때문에 timestamp에 민감하기 않았다.

반응형

근데 AWS에서 데이터베이스 서비스를 이용하니 여기서 문제가 발생한다. AWS 데이터베이스 시스템 시간이 PST로 국제 표준시로 설정되어있다.

이렇게 되면 국내 timezone 값과 시차가 발생하는데 이걸 코드에서 해결하기가 쉽지 않다.

왜냐하면 썸머타임이 적용되는 월과 그렇지 않은 월의 시차가 다르기 때문에 파이썬 코드에서 이걸 적용하려면 꽤 골치가 아프다.

데이터베이스 상에서 해결하려면 이미 등록되어 있는 Row도 값이 적용해주어야 해서 이것도 적용이 쉽지 않다.

그래서 쿼리에서 해결해보는 방법을 찾아보았다.

일단 postgresql에서 이런 쿼리를 지원한다. timestamp without time zone으로 설정된 created_at 컬럼이 있다고 했을 때 PST를 Asis/Seoul로 변경하는 쿼리는 다음과 같이 작성할 수 있다.

728x90

SELECT
    *,
    timezone('Asia/Seoul', timezone('PST', created_at)) AS created_at
FROM
    tb_sample

우선 timezone이 설정되지 않은 컬럼에 시스템의 timezone 값을 설정하고 이 값을 다시 원하는 timezone 값으로 변경하는 것이다.

굳이 이렇게 쿼리를 작성한 이유는 개발환경과 운영환경의 timezone 값이 다르기 때문이다.

개발할 때는 PST 대신 Asia/Seoul로 입력하면 한국 timezone을 설정하고 이걸 다시 한국 timezone으로 변경하는 것이니 문제없이 동작하게 된다.

이 코드를 이제 Sqlalchemy가 반영된 python 코드로 반영하면 다음과 같다.

반응형

from sqlalchemy.sql import func

def message_totals(dbsession, user):
    stmt = select(
        models.Sample.no,
        ....
        func.timezone('Asia/Seoul', func.timezone('PST', models.Sample.created_at,)).label('created_at'),
    )
    stmt = stmt.filter(models.Sample.is_deleted == "F")
    result = await db.execute(stmt)
    return result.fetchall()

sqlalchemy에서는 func를 통해서 각 데이터베이스에서 지원하는 함수를 호출할 수 있다.

그래서 func를 통해서 postgresql에서 지원하는 timezone 함수를 호출하도록 하였으며, 이 결과를 다시 created_at으로 alias 하였다.

이렇게 실행하면 timestamp without time zone으로 설정된 컬럼을 time zone이 있는 컬럼처럼 사용할 수 있게 된다.