날짜/시간 함수와 연산자

날짜/시간 함수와 연산자 (Date/Time Functions and Operators)

날짜·시간 값에 "하루를 더한다", "시간대를 바꾼다", "년·월·일을 뽑아낸다" 같은 작업을 할 때 PostgreSQL이 제공하는 함수들을 잘 알아두면 유용해요. 이 페이지에서 날짜/시간 연산자와 함수, 그리고 EXTRACT, date_trunc, date_bin, AT TIME ZONE, 현재 시각 관련 함수를 정리해 볼게요.

출처: 공식문서

시작하기 전에

날짜/시간 타입에도 Table 9.1의 일반 비교 연산자를 쓸 수 있어요. 날짜와 타임스탬프(시간대 유무 무관)는 모두 서로 비교 가능하지만, 시간(time)과 인터벌은 같은 데이터 타입의 값끼리만 비교할 수 있어요. 시간대 없는 타임스탬프를 시간대 있는 것과 비교할 때는, 전자가 TimeZone 설정 파라미터가 지정한 시간대에 있다고 가정하고 비교를 위해 UTC로 회전시켜요(후자는 내부적으로 이미 UTC). 마찬가지로 날짜 값은 타임스탬프와 비교할 때 TimeZone 시간대의 자정을 나타낸다고 가정해요.

time 이나 timestamp 입력을 받는 모든 함수·연산자는 실제로 두 가지 변형이 있어요. 하나는 time with time zone/timestamp with time zone 을, 다른 하나는 time without time zone/timestamp without time zone 을 받는 형태죠. 간결성을 위해 이 변형들은 따로 표시하지 않아요. 또 + 와 * 연산자는 교환 가능한 쌍(예: date + integer 와 integer + date)으로 존재하는데, 각 쌍의 하나만 보여줄게요.

날짜/시간 연산자

연산자 설명 예시
date + integer → date 날짜에 일수를 더해요. date '2001-09-28' + 7 → 2001-10-05
date + interval → timestamp 날짜에 인터벌을 더해요. date '2001-09-28' + interval '1 hour' → 2001-09-28 01:00:00
date + time → timestamp 날짜에 시각을 더해요. date '2001-09-28' + time '03:00' → 2001-09-28 03:00:00
interval + interval → interval 인터벌끼리 더해요. interval '1 day' + interval '1 hour' → 1 day 01:00:00
timestamp + interval → timestamp 타임스탬프에 인터벌을 더해요. timestamp '2001-09-28 01:00' + interval '23 hours' → 2001-09-29 00:00:00
time + interval → time 시각에 인터벌을 더해요. time '01:00' + interval '3 hours' → 04:00:00
- interval → interval 인터벌을 부정해요. - interval '23 hours' → -23:00:00
date - date → integer 날짜를 빼서 경과 일수를 만들어요. date '2001-10-01' - date '2001-09-28' → 3
date - integer → date 날짜에서 일수를 빼요. date '2001-10-01' - 7 → 2001-09-24
date - interval → timestamp 날짜에서 인터벌을 빼요. date '2001-09-28' - interval '1 hour' → 2001-09-27 23:00:00
time - time → interval 시각을 빼요. time '05:00' - time '03:00' → 02:00:00
time - interval → time 시각에서 인터벌을 빼요. time '05:00' - interval '2 hours' → 03:00:00
timestamp - interval → timestamp 타임스탬프에서 인터벌을 빼요. timestamp '2001-09-28 23:00' - interval '23 hours' → 2001-09-28 00:00:00
interval - interval → interval 인터벌을 빼요. interval '1 day' - interval '1 hour' → 1 day -01:00:00
timestamp - timestamp → interval 타임스탬프를 빼요(24시간 인터벌을 일수로 변환, justify_hours() 와 비슷). timestamp '2001-09-29 03:00' - timestamp '2001-07-27 12:00' → 63 days 15:00:00
interval * double precision → interval 인터벌에 스칼라를 곱해요. interval '1 second' * 900 → 00:15:00, interval '1 day' * 21 → 21 days
interval / double precision → interval 인터벌을 스칼라로 나눠요. interval '1 hour' / 1.5 → 00:40:00

날짜/시간 함수

시간대·시각 관련

  • age ( timestamp, timestamp ) → interval — 인자를 빼서 일수 대신 년·월을 쓰는 "기호적" 결과를 만들어요. 예: age(timestamp '2001-04-10', timestamp '1957-06-13') → 43 years 9 mons 27 days
  • age ( timestamp ) → interval — 인자를 current_date(자정)에서 빼요. 예: age(timestamp '1957-06-13') → 62 years 6 mons 10 days
  • clock_timestamp ( ) → timestamp with time zone — 현재 날짜·시간(문 실행 중에도 변함). 예: clock_timestamp() → 2019-12-23 14:39:53.662522-05
  • current_date → date — 오늘 날짜. 예: current_date → 2019-12-23
  • current_time → time with time zone — 현재 시각. 예: current_time → 14:39:53.662522-05
  • current_time ( integer ) → time with time zone — 제한된 정밀도의 현재 시각. 예: current_time(2) → 14:39:53.66-05
  • current_timestamp → timestamp with time zone — 현재 날짜·시간(현재 트랜잭션 시작 시점). 예: current_timestamp → 2019-12-23 14:39:53.662522-05
  • current_timestamp ( integer ) → timestamp with time zone — 제한된 정밀도 버전. 예: current_timestamp(0) → 2019-12-23 14:39:53-05
  • date_add ( timestamp with time zone, interval [, text ] ) → timestamp with time zone — 타임스탬프에 인터벌을 더하되, 세 번째 인자가 이름 붙인 시간대(또는 생략 시 현재 TimeZone)에 따라 시각 계산과 일광 절약 조정을 해요. 두 인자 형태는 timestamp with time zone + interval 연산자와 동등해요. 예: date_add('2021-10-31 00:00:00+02'::timestamptz, '1 day'::interval, 'Europe/Warsaw') → 2021-10-31 23:00:00+00
  • date_subtract ( timestamp with time zone, interval [, text ] ) → timestamp with time zone — 인터벌을 빼는 버전이고, 나머지는 date_add 와 동일해요. 예: date_subtract('2021-11-01 00:00:00+01'::timestamptz, '1 day'::interval, 'Europe/Warsaw') → 2021-10-30 22:00:00+00
  • localtime → time — 현재 시각. 예: localtime → 14:39:53.662522
  • localtime ( integer ) → time — 제한 정밀도 버전. 예: localtime(0) → 14:39:53
  • localtimestamp → timestamp — 현재 날짜·시간(트랜잭션 시작). 예: localtimestamp → 2019-12-23 14:39:53.662522
  • localtimestamp ( integer ) → timestamp — 예: localtimestamp(2) → 2019-12-23 14:39:53.66
  • now ( ) → timestamp with time zone — 현재 날짜·시간(트랜잭션 시작). 예: now() → 2019-12-23 14:39:53.662522-05
  • statement_timestamp ( ) → timestamp with time zone — 현재 문 시작 시점. 예: statement_timestamp() → 2019-12-23 14:39:53.662522-05
  • timeofday ( ) → text — clock_timestamp 처럼 현재 시각이지만 text 문자열로. 예: timeofday() → Mon Dec 23 14:39:53.662522 2019 EST
  • transaction_timestamp ( ) → timestamp with time zone — 현재 트랜잭션 시작 시점. 예: transaction_timestamp() → 2019-12-23 14:39:53.662522-05
  • to_timestamp ( double precision ) → timestamp with time zone — 유닉스 에포크(1970-01-01 00:00:00+00 이후 초)를 타임스탬프로 변환해요. 예: to_timestamp(1284352323) → 2010-09-13 04:32:03+00

만들기·절단·부분 추출

  • make_date ( year int, month int, day int ) → date — 년·월·일 필드로 날짜를 만들어요(음수 년은 BC). 예: make_date(2013, 7, 15) → 2013-07-15
  • make_interval ( [ years int [, months int [, weeks int [, days int [, hours int [, mins int [, secs double precision ]]]]]]] ) → interval — 각 필드(기본 0)로 인터벌을 만들어요. 예: make_interval(days => 10) → 10 days
  • make_time ( hour int, min int, sec double precision ) → time — 시·분·초로 시각을 만들어요. 예: make_time(8, 15, 23.5) → 08:15:23.5
  • make_timestamp ( year int, month int, day int, hour int, min int, sec double precision ) → timestamp — 필드로 타임스탬프를 만들어요(음수 년은 BC). 예: make_timestamp(2013, 7, 15, 8, 15, 23.5) → 2013-07-15 08:15:23.5
  • make_timestamptz ( year int, month int, day int, hour int, min int, sec double precision [, timezone text ] ) → timestamp with time zone — 필드로 시간대 있는 타임스탬프를 만들어요. timezone 을 지정하지 않으면 현재 시간대를 쓰고, 예시들은 세션 시간대가 Europe/London 이라고 가정해요. 예: make_timestamptz(2013, 7, 15, 8, 15, 23.5) → 2013-07-15 08:15:23.5+01
  • date_bin ( interval, timestamp, timestamp ) → timestamp — 입력을 지정 원점(origin)에 정렬된 지정 인터벌로 "빈(bin)" 처리해요. 예: date_bin('15 minutes', timestamp '2001-02-16 20:38:40', timestamp '2001-02-16 20:05:00') → 2001-02-16 20:35:00
  • date_trunc ( text, timestamp ) → timestamp — 지정 정밀도로 자릅니다. 예: date_trunc('hour', timestamp '2001-02-16 20:38:40') → 2001-02-16 20:00:00
  • date_trunc ( text, timestamp with time zone, text ) → timestamp with time zone — 지정 시간대에서 지정 정밀도로 자릅니다. 예: date_trunc('day', timestamptz '2001-02-16 20:38:40+00', 'Australia/Sydney') → 2001-02-16 13:00:00+00
  • date_trunc ( text, interval ) → interval — 인터벌을 지정 정밀도로 자릅니다. 예: date_trunc('hour', interval '2 days 3 hours 40 minutes') → 2 days 03:00:00
  • date_part ( text, timestamp ) → double precision — 타임스탬프 하위 필드를 얻어요(extract 와 동등). 예: date_part('hour', timestamp '2001-02-16 20:38:40') → 20
  • date_part ( text, interval ) → double precision — 인터벌 하위 필드를 얻어요. 예: date_part('month', interval '2 years 3 months') → 3
  • extract ( field from timestamp ) → numeric — 타임스탬프 하위 필드를 얻어요. 예: extract(hour from timestamp '2001-02-16 20:38:40') → 20
  • extract ( field from interval ) → numeric — 인터벌 하위 필드를 얻어요. 예: extract(month from interval '2 years 3 months') → 3
  • isfinite ( date|timestamp|interval ) → boolean — 유한한 값인지(+/-무한대가 아닌지) 확인해요. 예: isfinite(date '2001-02-16') → true, isfinite(timestamp 'infinity') → false
  • justify_days ( interval ) → interval — 30일 기간을 월로 변환해 인터벌을 조정해요. 예: justify_days(interval '1 year 65 days') → 1 year 2 mons 5 days
  • justify_hours ( interval ) → interval — 24시간 기간을 일로 변환해요. 예: justify_hours(interval '50 hours 10 minutes') → 2 days 02:10:00
  • justify_interval ( interval ) → interval — justify_days 와 justify_hours 를 부호 조정과 함께 적용해요. 예: justify_interval(interval '1 mon -1 hour') → 29 days 23:00:00

OVERLAPS 연산자

(start1, end1) OVERLAPS (start2, end2)
(start1, length1) OVERLAPS (start2, length2)

이 표현식은 두 기간(끝점으로 정의)이 겹치면 true, 아니면 false예요. 끝점은 날짜·시간·타임스탬프 쌍, 또는 날짜·시간·타임스탬프 뒤에 인터벌을 붙인 형태로 지정할 수 있어요. 값 쌍이 주어지면 시작·끝 중 어느 것을 먼저 써도 되고, OVERLAPS 가 알아서 더 이른 값을 시작으로 취급해요. 각 기간은 반개방 구간 start <= time < end 으로 간주되는데, start 와 end 가 같으면 그 단일 시점을 나타내요. 그래서 끝점만 공유하는 두 기간은 겹치지 않아요. 예:

SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS
       (DATE '2001-10-30', DATE '2002-10-30');   -- true
SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS
       (DATE '2001-10-30', DATE '2002-10-30');   -- false
SELECT (DATE '2001-10-29', DATE '2001-10-30') OVERLAPS
       (DATE '2001-10-30', DATE '2001-10-31');   -- false

인터벌 산술의 세부 규칙

타임스탬프에 인터벌을 더하거나 뺄 때는 인터벌의 months·days·microseconds 필드를 순서대로 처리해요. 먼저 0이 아닌 months 필드가 날짜를 해당 월 수만큼 옮기는데, 새 달의 끝을 지나가면 그 달의 마지막 날을 써요(3월 31일 + 1개월은 4월 30일, +2개월은 5월 31일). 그다음 days 필드가 날짜를 옮기고, 이 두 단계 모두 하루 중 시각은 그대로 유지해요. 마지막으로 0이 아닌 microseconds 필드는 말 그대로 더하거나 빼요.

DST를 인식하는 시간대에서 timestamp with time zone 을 연산할 때는 interval '1 day' 를 더하는 것과 interval '24 hours' 를 더하는 것이 결과가 다를 수 있어요. 세션 시간대가 America/Denver 일 때 2005-04-02 12:00:00-07 + interval '1 day' 는 2005-04-03 12:00:00-06 이지만, + interval '24 hours' 는 2005-04-03 13:00:00-06 이에요. 2005-04-03 02:00:00 에 일광 절약 변경으로 한 시간이 건너뛰어졌기 때문이에요.

age 가 반환하는 months 필드는 달마다 일수가 달라 애매할 수 있어요. PostgreSQL은 부분 월 계산에 두 날짜 중 더 이른 쪽의 달을 사용해요. 예를 들어 age('2004-06-01', '2004-04-30') 은 4월을 써서 1 mon 1 day 를 내지만, 5월을 쓰면 5월이 31일이라 1 mon 2 days 가 돼요.

참고로 뺄셈에도 여러 방식이 있어요. 개념적으로 간단한 방법은 각 값을 EXTRACT(EPOCH FROM ...) 로 초 단위로 바꿔 빼는 것이고, 그러면 두 값 사이의 초 수를 얻을 수 있어요. 이건 각 달의 일수, 시간대 변경, 일광 절약 조정을 다 반영해요. - 연산자로 날짜·타임스탬프를 빼면 두 값 사이의 일수(24시간)와 시·분·초를 반환하고 같은 조정을 해요. age 는 필드별로 뺀 뒤 음수 필드를 조정해 년·월·일·시/분/초를 반환해요. 예시(timezone = 'US/Eastern', 두 날짜 사이에 DST 변경 존재):

SELECT EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -
       EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00');  -- 10537200.000000
SELECT timestamptz '2013-07-01 12:00:00' - timestamptz '2013-03-01 12:00:00';  -- 121 days 23:00:00
SELECT age(timestamptz '2013-07-01 12:00:00', timestamptz '2013-03-01 12:00:00');  -- 4 mons

EXTRACT 와 date_part

extract 는 날짜/시간 값에서 년이나 시 같은 하위 필드를 꺼내요.

EXTRACT(field FROM source)

source 는 timestamp, date, time, interval 타입의 값이고, field 는 추출할 필드를 고르는 식별자 또는 문자열이에요. 모든 필드가 모든 입력 타입에 유효한 건 아니에요(예: 하루보다 작은 필드는 date 에서, 하루 이상 필드는 time 에서 추출 불가). extract 는 numeric 타입을 반환해요.

유효한 필드 이름과 의미는 아래와 같아요.

  • century — 세기(인터벌은 연도 필드를 100으로 나눈 값). EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13') → 20, '2001-02-16 ...' → 21
  • day — 월 중 일(1–31), 인터벌은 일수. EXTRACT(DAY FROM INTERVAL '40 days 1 minute') → 40
  • decade — 연도 필드를 10으로 나눈 값. EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40') → 200
  • dow — 요일, 일요일(0)~토요일(6). EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40') → 5. to_char(..., 'D') 의 요일 번호와는 다르다는 점에 주의하세요.
  • doy — 연중 일수(1–365/366). EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40') → 47
  • epoch — timestamp with time zone 은 1970-01-01 00:00:00 UTC 이후 초(이전은 음수), date·timestamp 는 시간대·DST 무시한 명목 초, interval 은 총 초. EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08') → 982384720.120000. 에포크 값은 to_timestamp 로 되돌릴 수 있지만, date·timestamp 에서 추출한 에포크에 적용하면 원래 값이 UTC였다고 가정해 오해 소지가 있어요.
  • hour — 시 필드(타임스탬프는 0–23, 인터벌은 무제한). EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40') → 20
  • isodow — 요일, 월요일(1)~일요일(7). EXTRACT(ISODOW FROM TIMESTAMP '2001-02-18 20:38:40') → 7. 일요일만 제외하면 dow 와 동일하고 ISO 8601 요일 번호와 일치해요.
  • isoyear — 날짜가 속한 ISO 8601 주 번호 연도. EXTRACT(ISOYEAR FROM DATE '2006-01-01') → 2005. 각 ISO 주 연도는 1월 4일이 들어 있는 주의 월요일로 시작해서, 1월 초·12월 말에는 그레고리 연도와 다를 수 있어요.
  • julian — 날짜·타임스탬프에 해당하는 율리우스 날짜. 로컬 자정이 아닌 타임스탬프는 분수 값. EXTRACT(JULIAN FROM DATE '2006-01-01') → 2453737
  • microseconds — 소수부 포함 초 필드에 1,000,000을 곱한 값(전체 초 포함). EXTRACT(MICROSECONDS FROM TIME '17:12:28.5') → 28500000
  • millennium — 천년기(인터벌은 연도를 1000으로 나눈 값). EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40') → 3. 1900년대는 2번째 천년기, 3번째는 2001년 1월 1일에 시작.
  • milliseconds — 소수부 포함 초에 1000을 곱한 값(전체 초 포함). EXTRACT(MILLISECONDS FROM TIME '17:12:28.5') → 28500.000
  • minute — 분 필드(0–59). EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40') → 38
  • month — 연중 월 번호(1–12), 인터벌은 월 수를 12로 나눈 나머지(0–11). EXTRACT(MONTH FROM INTERVAL '2 years 13 months') → 1
  • quarter — 분기(1–4), 인터벌은 월 필드를 3으로 나눈 값 + 1. EXTRACT(QUARTER FROM INTERVAL '1 year 6 months') → 3
  • second — 소수 초 포함 초 필드. EXTRACT(SECOND FROM TIME '17:12:28.5') → 28.500000
  • timezone — UTC로부터의 오프셋(초). 동쪽이 양수, 서쪽이 음수. (기술적으로 PostgreSQL은 윤초를 처리하지 않아 UTC를 쓰지 않아요.)
  • timezone_hour — 시간대 오프셋의 시 성분.
  • timezone_minute — 시간대 오프셋의 분 성분.
  • week — ISO 8601 주 번호. ISO 주는 월요일 시작, 첫 주는 1월 4일을 포함해요. 1월 초 날짜가 이전 해 52·53주에, 12월 말이 다음 해 1주에 속할 수 있어요. isoyear 와 함께 쓰길 권장해요. 인터벌은 정수 일수를 7로 나눈 값. EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40') → 7
  • year — 연도 필드. 0 AD 는 없으니 BC 연도를 AD 연도에서 빼는 것은 주의하세요. EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40') → 2001

인터벌을 처리할 때 extract 는 인터벌 출력 함수가 쓰는 해석과 일치하는 필드 값을 만들어요. 비정규화된 인터벌 표현에서 시작하면 놀라운 결과가 나올 수 있어요. 예: INTERVAL '80 minutes' 는 01:20:00 으로 표시되는데, EXTRACT(MINUTES FROM INTERVAL '80 minutes') 는 20 이에요.

참고로 입력이 +/-무한대면, 단조 증가 필드(epoch, julian, year, isoyear, decade, century, millennium)에서는 extract 가 +/-무한대를 반환하고, 그 외 필드는 NULL을 반환해요. (9.6 이전 버전은 무한 입력에 모든 경우 0을 반환했어요.)

date_part 는 전통적인 Ingres 함수로, SQL 표준 extract 의 대응 버전이에요. 여기서 field 파라미터는 이름이 아니라 문자열 값이어야 해요. 유효한 필드 이름은 extract 와 같고, 역사적 이유로 date_part 는 double precision 을 반환해서 정밀도 손실이 있을 수 있어요. extract 사용을 권장해요.

date_trunc

date_trunc 는 숫자용 trunc 함수와 개념적으로 비슷해요.

date_trunc(field, source [, time_zone ])

source 는 timestamp, timestamp with time zone, interval 타입이고(date·time 은 자동 캐스팅), field 는 어느 정밀도로 자를지를 고릅니다. 반환 값도 같은 타입이며, 선택된 필드보다 덜 중요한 모든 필드는 0(day·month는 1)으로 설정돼요.

유효한 field 값: microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century, millennium.

timestamp with time zone 입력이면 특정 시간대 기준으로 절단돼요. 예를 들어 day 로 자르면 그 시간대의 자정 값이 나와요. 기본은 현재 TimeZone 설정 기준이지만, 선택 time_zone 인자로 다른 시간대를 지정할 수 있어요. timestamp without time zone 이나 interval 입력에는 시간대를 지정할 수 없고 항상 표면 값 그대로 처리돼요. 예(지역 시간대 America/New_York 가정):

SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');  -- 2001-02-16 20:00:00
SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');  -- 2001-01-01 00:00:00
SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00', 'Australia/Sydney');  -- 2001-02-16 08:00:00-05

date_bin

date_bin 은 입력 타임스탬프를 지정 원점(origin)에 정렬된 지정 인터벌(스트라이드)로 "빈" 처리해요.

date_bin(stride, source, origin)

source 는 timestamp 또는 timestamp with time zone(date 는 자동 캐스팅), stride 는 interval 타입이에요. 반환 값은 같은 타입이며 source 가 놓인 빈의 시작을 표시해요.

예:

SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01');        -- 2020-02-11 15:30:00
SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01 00:02:30');  -- 2020-02-11 15:32:30

정수 단위(1분, 1시간 등)에서는 대응하는 date_trunc 와 같은 결과를 주지만, date_bin 은 임의의 인터벌로 자를 수 있다는 차이가 있어요. stride 인터벌은 0보다 커야 하고 월 이상 단위를 포함할 수 없어요.

AT TIME ZONE 과 AT LOCAL

AT TIME ZONE 연산자는 시간대 없는 타임스탬프를 시간대 있는 것으로(및 반대) 변환하고, time with time zone 값을 다른 시간대로 바꿔요.

  • timestamp without time zone AT TIME ZONE zone → timestamp with time zone — 주어진 값이 이름 붙은 시간대에 있다고 가정해 변환해요. 예: timestamp '2001-02-16 20:38:40' at time zone 'America/Denver' → 2001-02-17 03:38:40+00
  • timestamp without time zone AT LOCAL → timestamp with time zone — 세션의 TimeZone 값을 시간대로 써서 변환해요. 예: timestamp '2001-02-16 20:38:40' at local → 2001-02-17 03:38:40+00
  • timestamp with time zone AT TIME ZONE zone → timestamp without time zone — 그 시간대에 나타날 시각으로 변환해요. 예: timestamp with time zone '2001-02-16 20:38:40-05' at time zone 'America/Denver' → 2001-02-16 18:38:40
  • timestamp with time zone AT LOCAL → timestamp without time zone — 세션 TimeZone 값에 나타날 시각으로. 예: timestamp with time zone '2001-02-16 20:38:40-05' at local → 2001-02-16 18:38:40
  • time with time zone AT TIME ZONE zone → time with time zone — 새 시간대로 변환해요. 날짜가 없어 목적지 시간대의 현재 활성 UTC 오프셋을 써요. 예: time with time zone '05:34:17-05' at time zone 'UTC' → 10:34:17+00
  • time with time zone AT LOCAL → time with time zone — 세션 TimeZone 값의 현재 활성 오프셋을 써서 변환. 예(세션 시간대가 UTC 라면): time with time zone '05:34:17-05' at local → 10:34:17+00

여기서 시간대 zone 은 텍스트 값(예: 'America/Los_Angeles')이나 인터벌(예: INTERVAL '-08:00')로 지정할 수 있어요. 인터벌 형태는 UTC에서 고정 오프셋인 시간대에만 유용해요. AT LOCAL 문법은 세션 TimeZone 값인 AT TIME ZONE local 의 축약형이에요.

몇 가지 유용한 예시를 볼게요(현재 TimeZone 설정 America/Los_Angeles 가정).

SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'America/Denver';          -- 2001-02-16 19:38:40-08
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'America/Denver';  -- 2001-02-16 18:38:40
SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'Asia/Tokyo' AT TIME ZONE 'America/Chicago';  -- 2001-02-16 05:38:40

여기서 첫 예는 시간대 없는 값에 시간대를 붙이고 현재 TimeZone 설정으로 표시하는 것, 둘째는 시간대 있는 값을 지정 시간대로 옮겨 시간대 없는 값으로 반환하는 것, 셋째는 도쿄 시간을 시카고 시간으로 변환하는 거예요. 함수 timezone(zone, timestamp) 는 timestamp AT TIME ZONE zone 과 동등하고, timezone(timestamp) 는 timestamp AT LOCAL 과 동등해요.

현재 날짜/시간

PostgreSQL은 현재 날짜·시간 관련 값을 반환하는 여러 함수를 제공해요. 아래 SQL 표준 함수들은 모두 현재 트랜잭션의 시작 시점을 기준으로 값을 반환해요.

CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME(precision)
CURRENT_TIMESTAMP(precision)
LOCALTIME
LOCALTIMESTAMP
LOCALTIME(precision)
LOCALTIMESTAMP(precision)

CURRENT_TIME 과 CURRENT_TIMESTAMP 는 시간대를 포함한 값을, LOCALTIME 과 LOCALTIMESTAMP 는 시간대 없는 값을 돌려줘요. 정밀도 파라미터를 주면 초 필드의 소수 자리를 그만큼 반올림해요. 없으면 전체 정밀도로 줘요.

이 함수들은 트랜잭션 시작 시점을 반환하므로 트랜잭션 동안 값이 변하지 않아요. 이는 의도된 기능인데, 단일 트랜잭션이 일관된 "현재" 시간 개념을 갖게 해서 같은 트랜잭션 안의 여러 수정이 같은 타임스탬프를 갖게 하려는 거예요.

PostgreSQL은 또한 현재 문의 시작 시점과, 함수가 호출된 순간의 실제 현재 시각을 반환하는 함수도 제공해요. 비 SQL 표준 시간 함수 전체 목록은 transaction_timestamp(), statement_timestamp(), clock_timestamp(), timeofday(), now() 예요.

  • transaction_timestamp() 는 CURRENT_TIMESTAMP 와 동등하지만 이름이 반환 내용을 더 명확히 드러내요.
  • statement_timestamp() 는 현재 문의 시작 시점(정확히는 클라이언트로부터 최신 명령 메시지를 받은 시각)을 반환해요. 트랜잭션 첫 문에서는 transaction_timestamp() 와 같지만 이후 문에서는 다를 수 있어요.
  • clock_timestamp() 는 실제 현재 시각을 반환해서 단일 SQL 문 안에서도 값이 변해요.
  • timeofday() 는 역사적인 함수로, clock_timestamp() 처럼 실제 시각을 반환하지만 timestamp with time zone 대신 포맷된 text 문자열로 돌려줘요.
  • now() 는 transaction_timestamp() 의 전통적인 동등 함수예요.

모든 날짜/시간 타입은 특수 리터럴 값 now 도 받아들여(역시 트랜잭션 시작 시점으로 해석) 현재 날짜·시간을 지정할 수 있어요. 그래서 아래 세 개는 같은 결과예요.

SELECT CURRENT_TIMESTAMP;
SELECT now();
SELECT TIMESTAMP 'now';  -- but see tip below

팁: DEFAULT 절처럼 나중에 평가할 값을 지정할 때는 세 번째 형태(TIMESTAMP 'now')를 쓰지 마세요. 상수가 파싱되는 즉시 now 가 timestamp 로 변환되므로, 기본값이 필요해질 때는 테이블 생성 시각이 들어가요! 앞의 두 형태는 함수 호출이라 기본값이 사용될 때까지 평가되지 않아 행 삽입 시각으로 기본값을 주는 원하는 동작을 얻을 수 있어요.

실행 지연 (Delaying Execution)

서버 프로세스의 실행을 지연하는 함수들이 있어요: pg_sleep ( double precision ), pg_sleep_for ( interval ), pg_sleep_until ( timestamp with time zone ).

  • pg_sleep 은 주어진 초가 지날 때까지 현재 세션 프로세스를 재웁니다. 소수 초도 지정 가능해요.
  • pg_sleep_for 는 수면 시간을 interval 로 지정할 수 있게 하는 편의 함수예요.
  • pg_sleep_until 은 특정 기상 시각이 필요할 때 쓰는 편의 함수예요.
SELECT pg_sleep(1.5);
SELECT pg_sleep_for('5 minutes');
SELECT pg_sleep_until('tomorrow 03:00');

수면 인터벌의 유효 해상도는 플랫폼별로 달라요(0.01초가 흔한 값). 수면 지연은 지정보다 항상 길거나 같고, 서버 부하에 따라 더 길 수 있어요. 특히 pg_sleep_until 은 정확히 지정 시각에 깨어난다고 보장되지 않지만 그보다 이르게 깨지는 않아요.

경고: pg_sleep 이나 그 변형을 호출할 때 세션이 필요한 것보다 많은 잠금을 잡고 있지 않게 하세요. 그렇지 않으면 다른 세션이 재우는 프로세스를 기다려야 해서 전체 시스템이 느려질 수 있어요.

더 알아보기 (Learn more)