날짜 함수
날짜 함수 (Date Functions)
이 섹션은 [DATE]({% link docs/current/sql/data_types/date.md %}) 값을 조사하고 조작하는 함수와 연산자들을 설명해 드릴게요. 날짜에 일수를 더하거나 뺄 수도 있고, 특정 부분(연·월·일)을 뽑아내는 것도 가능해요. 어떤 함수들이 있는지 하나씩 살펴볼게요.
출처: 문서
본문
날짜 연산자
아래 표는 DATE 타입에 사용할 수 있는 수학 연산자들을 보여줘요.
| Operator | Description | Example | Result |
|---|---|---|---|
+ |
addition of days (integers) | DATE '1992-03-22' + 5{:.language-sql .highlight} |
1992-03-27 |
+ |
addition of AN INTERVAL |
DATE '1992-03-22' + INTERVAL 5 DAY{:.language-sql .highlight} |
1992-03-27 00:00:00 |
+ |
addition of a variable INTERVAL |
SELECT DATE '1992-03-22' + INTERVAL (d.days) DAY FROM (VALUES (5), (11)) d(days){:.language-sql .highlight} |
1992-03-27 00:00:00 and 1992-04-02 00:00:00 |
- |
subtraction of DATEs |
DATE '1992-03-27' - DATE '1992-03-22'{:.language-sql .highlight} |
5 |
- |
subtraction of an INTERVAL |
DATE '1992-03-27' - INTERVAL 5 DAY{:.language-sql .highlight} |
1992-03-22 00:00:00 |
- |
subtraction of a variable INTERVAL |
SELECT DATE '1992-03-27' - INTERVAL (d.days) DAY FROM (VALUES (5), (11)) d(days){:.language-sql .highlight} |
1992-03-22 00:00:00 and 1992-03-16 00:00:00 |
[무한 값(infinite values)]({% link docs/current/sql/data_types/date.md %}#special-values)에 더하거나 빼면 같은 무한 값이 나와요.
날짜 함수
아래 표는 DATE 타입에 사용할 수 있는 함수들을 보여줘요. 날짜는 타입 승격을 통해 [timestamp 함수]({% link docs/current/sql/functions/timestamp.md %})로도 조작할 수 있어요.
| Name | Description |
|---|---|
date_add(date, interval) |
Add the interval to the date and return a DATETIME value. |
date_diff(part, startdate, enddate) |
The number of [part]({% link docs/current/sql/functions/datepart.md %}) boundaries between startdate and enddate, inclusive of the larger date and exclusive of the smaller date. |
date_part(part, date) |
Get [subfield]({% link docs/current/sql/functions/datepart.md %}) (equivalent to extract). |
date_sub(part, startdate, enddate) |
The signed length of the interval between startdate and enddate, truncated to whole multiples of [part]({% link docs/current/sql/functions/datepart.md %}). |
date_trunc(part, date) |
Truncate to specified [precision]({% link docs/current/sql/functions/datepart.md %}). |
dayname(date) |
The (English) name of the weekday. |
days_in_month(date) |
The number of days in the month of the given date. |
extract(part from date) |
Get [subfield]({% link docs/current/sql/functions/datepart.md %}) from a date. |
greatest(date, date) |
The later of two dates. |
isfinite(date) |
Returns true if the date is finite, false otherwise. |
isinf(date) |
Returns true if the date is infinite, false otherwise. |
julian(date) |
Extract the Julian Day number from a date. |
last_day(date) |
The last day of the corresponding month in the date. |
least(date, date) |
The earlier of two dates. |
make_date(year, month, day) |
The date for the given parts. |
monthname(date) |
The (English) name of the month. |
strftime(date, format) |
Converts a date to a string according to the [format string]({% link docs/current/sql/functions/dateformat.md %}). |
time_bucket(bucket_width, date[, offset]) |
Truncate date to a grid of width bucket_width. The grid is anchored at 2000-01-01[ + offset] when bucket_width is a number of months or coarser units, else 2000-01-03[ + offset]. Note that 2000-01-03 is a Monday. |
time_bucket(bucket_width, date[, origin]) |
Truncate timestamptz to a grid of width bucket_width. The grid is anchored at the origin timestamp, which defaults to 2000-01-01 when bucket_width is a number of months or coarser units, else 2000-01-03. Note that 2000-01-03 is a Monday. |
today() |
Current date (start of current transaction) in the local time zone. |
date_add(date, interval)
| Description | Add the interval to the date and return a DATETIME value. |
| Example | date_add(DATE '1992-09-15', INTERVAL 2 MONTH) |
| Result | 1992-11-15 00:00:00 |
date_diff(part, startdate, enddate)
| Description | The number of [part]({% link docs/current/sql/functions/datepart.md %}) boundaries between startdate and enddate, inclusive of the larger date and exclusive of the smaller date. |
| Example | date_diff('month', DATE '1992-09-15', DATE '1992-11-14') |
| Result | 2 |
| Alias | datediff |
date_part(part, date)
| Description | Get the [subfield]({% link docs/current/sql/functions/datepart.md %}) (equivalent to extract). |
| Example | date_part('year', DATE '1992-09-20') |
| Result | 1992 |
| Alias | datepart |
date_sub(part, startdate, enddate)
| Description | The signed length of the interval between startdate and enddate, truncated to whole multiples of [part]({% link docs/current/sql/functions/datepart.md %}). |
| Example | date_sub('month', DATE '1992-09-15', DATE '1992-11-14') |
| Result | 1 |
| Alias | datesub |
date_trunc(part, date)
| Description | Truncate to specified [precision]({% link docs/current/sql/functions/datepart.md %}). Always returns a TIMESTAMP, even when the input is a DATE. |
| Example | date_trunc('month', DATE '1992-03-07') |
| Result | 1992-03-01 00:00:00 |
| Alias | datetrunc |
dayname(date)
| Description | The (English) name of the weekday. |
| Example | dayname(DATE '1992-09-20') |
| Result | Sunday |
days_in_month(date)
| Description | The number of days in the month of the given date. |
| Example | days_in_month(DATE '1992-02-15') |
| Result | 29 |
extract(part from date)
| Description | Get [subfield]({% link docs/current/sql/functions/datepart.md %}) from a date. |
| Example | extract('year' FROM DATE '1992-09-20') |
| Result | 1992 |
greatest(date, date)
| Description | The later of two dates. |
| Example | greatest(DATE '1992-09-20', DATE '1992-03-07') |
| Result | 1992-09-20 |
isfinite(date)
| Description | Returns true if the date is finite, false otherwise. |
| Example | isfinite(DATE '1992-03-07') |
| Result | true |
isinf(date)
| Description | Returns true if the date is infinite, false otherwise. |
| Example | isinf(DATE '-infinity') |
| Result | true |
julian(date)
| Description | Extract the Julian Day number from a date. |
| Example | julian(DATE '1992-09-20') |
| Result | 2448886.0 |
last_day(date)
| Description | The last day of the corresponding month in the date. |
| Example | last_day(DATE '1992-09-20') |
| Result | 1992-09-30 |
least(date, date)
| Description | The earlier of two dates. |
| Example | least(DATE '1992-09-20', DATE '1992-03-07') |
| Result | 1992-03-07 |
make_date(year, month, day)
| Description | The date for the given parts. |
| Example | make_date(1992, 9, 20) |
| Result | 1992-09-20 |
monthname(date)
| Description | The (English) name of the month. |
| Example | monthname(DATE '1992-09-20') |
| Result | September |
strftime(date, format)
| Description | Converts a date to a string according to the [format string]({% link docs/current/sql/functions/dateformat.md %}). |
| Example | strftime(DATE '1992-01-01', '%a, %-d %B %Y') |
| Result | Wed, 1 January 1992 |
time_bucket(bucket_width, date[, offset])
| Description | Truncate date to a grid of width bucket_width. The grid is anchored at 2000-01-01[ + offset] when bucket_width is a number of months or coarser units, else 2000-01-03[ + offset]. Note that 2000-01-03 is a Monday. |
| Example | time_bucket(INTERVAL '2 months', DATE '1992-04-20', INTERVAL '1 month') |
| Result | 1992-04-01 |
time_bucket(bucket_width, date[, origin])
| Description | Truncate timestamptz to a grid of width bucket_width. The grid is anchored at the origin timestamp, which defaults to 2000-01-01 when bucket_width is a number of months or coarser units, else 2000-01-03. Note that 2000-01-03 is a Monday. |
| Example | time_bucket(INTERVAL '2 weeks', DATE '1992-04-20', DATE '1992-04-01') |
| Result | 1992-04-15 |
today()
| Description | Current date (start of current transaction) in the local time zone. |
| Example | today() |
| Result | 2022-10-08 |
| Alias | current_date (no parentheses necessary) |
날짜 부분 추출 함수
[부분(subfields)]({% link docs/current/sql/functions/datepart.md %}#part-functions)을 얻기 위한 전용 추출 함수도 있어요. 몇 가지 예로 날짜에서 일(day)을 추출하거나, 날짜에서 요일을 추출하는 경우가 있어요.
무한 날짜에 적용된 함수는 그 상황에서 "말이 되는 것"에 따라 같은 무한 날짜(예: greatest)를 반환하거나 NULL(예: date_part)을 반환해요. 일반적으로 함수가 무한 날짜의 부분을 검사해야 한다면 결과는 NULL이에요.