[PostgreSQL] 시간 계산하기

/* 현재시간과 지난시간의 차이를 구한다 */

              select now(),

                        extract(epoch from now())::integer as currentTime,

                       extract(epoch from to_timestamp('20210908165456','YYYYMMDDHH24MISS'))::integer as oldTime,

                          extract(epoch from now())::integer - extract(epoch from to_timestamp('20210908171731','YYYYMMDDHH24MISS'))::integer as diffTime

              ;

 

select now()::timestamp

 

select '2021-09-08 16:54:56'::timestamp as te

 

시간 타입

postgresql의 시간 관리용 타입으로는 timestamp, date, time 등이 있다.

timestamp는 날짜와 시간값을 저장하는 8바이트 값이다. 표현범위는 BC.4713~AD.294276년이다. 넉넉하다.

date는 날짜정보만을 저장하는 4바이트 값이다. 표현범위는 BC.4713~AD.5874897년이다.

time은 시간정보만 저장하는 8바이트 값이다.

​

​

시간값 획득하기

시간값 획득에는 몇가지 방법이 있다.

첫번째는 NOW 함수를 사용하는 것이다.

이건 타임스탬프에 타임존이 합쳐진 값을 반환한다.

​

두번째 방법은 CURRENT_TIMESTAMP라는 특수한 값을 사용하는 것이다.

NOW 함수를 호출하는 것과 동등하다.

​

이외에도 현재 시간을 가져오는 CURRENT_TIME과

​

현재 날짜를 가져오는 CURRENT_DATE 등이 있다.

​

​

​

타임스탬프<->문자열 간 변환

TO_CHAR 함수를 사용하면 된다.

첫번째 인자로 타임스탬프, 두번째 인자로 형식 문자열을 받는다.

​

저기서 Y는 연도, M은 월, D는 일이다. 이외에도 많다.

​

그 반대는 TO_TIMESTAMP 함수로 수행해낼 수 있다.

​

​

타임스탬프<->Date 간 변환

타임스탬프->Date 변환은 단순히 캐스팅으로도 해결할 수 있다,

​

반대도 마찬가지다.

​

​

타임스탬프<->Time 간 변환

이것도 마찬가지로 캐스팅만으로 해결이 가능하다.

​

​

유닉스 타임스탬프

유닉스 타임스탬프는 1970년~2038년 범위만 표현할 수 있는 얄팍하고 구시대적인 시간 형식이다. Epoch나 posix time으로도 부른다.

4바이트 정수로 표현되며, 최소 단위는 1초다.

​

유닉스 타임스탬프는 바로 획득할 방법은 없고, 타임스탬프에 특수한 함수를 사용해 추출해야 한다.

획득은 다음과 같이 할 수 있다.

​

근데 밀리초단위가 소수점으로 나오기 때문에, 정수 캐스팅도 해주는 게 좋다.

​

유닉스 타임스탬프를 다시 타임스탬프로 바꿀 때는, 그냥 TO_TIMESTAMP만 사용해도 된다.

​

​

시간 값 변경: INTERVAL

시간을 다루다보면 시간값에 다른 시간을 더하거나 빼서 범위를 체크하는 경우가 무수히 많다.

당연히도 postgres는 이를 위한 기능을 제공하는데, 그게 바로 INTERVAL 타입이다.

이건 날짜 계산만을 위한 특수한 타입이다.

INTERVAL이 앞에 붙는 문자열로 표현된다.

저기엔 YEAR, MONTH, DAY, HOUR, MINUTE, SECOND 등이 들어갈 수 있고

​

자유롭게 시간값에 대해 연산을 수행할 수 있다.

​

​

시간 간격 계산

보통 타임스탬프끼리 뺄셈을 해서 지난 날짜를 가져오려 하면 INTERVAL 값으로 반환을 한다.

​

AGE 함수를 사용하면 DATE 값이 반환된다.

​

​

시간 부분값 추출: EXTRACT

extract는 타임스탬프 값에서 특정 부분을 추출해서 반환한다.

일이라든지, 연도라든지...

​

​

시간값 내림: DATE_TRUNC

DATE_TRUNC는 타임스탬프 값을 받아서, 특정 단위 밑을 잘라버리는 함수다.

가령 minute을 받는다면, 초 값이 00으로 잘려나간다.

​

​

타임존

지금 내 환경은 한국이라 상관없는데, DB 환경에 따라 각각 다른 타임존을 갖고있을 수 있다.

내 경우엔 서울로 잘 되어있지만

​

리눅스인 경우엔 대부분 UTC다.

그리고 모든 배포 서버는 리눅스다.

​

그럼 분명 지금 한국시간은 오늘인데, NOW()로 보면 어제가 나오는 일이 벌어질 수 있다.

이 부조화를 보아라

DB 내에서만 잘 사용한다면 상관없겠지만, 사용자에게 뿌려주는 일이 생기면 문제가 생긴다.

​

그럴 때는 타임존을 명시적으로 지정하면 한국시간을 가져올 수 있다.