날짜 부분 함수
날짜 부분 함수 (Date Part Functions)
date_part, date_trunc 그리고 date_diff 함수는 [TIMESTAMP]({% link docs/current/sql/data_types/timestamp.md %}), [TIMESTAMPTZ]({% link docs/current/sql/data_types/timestamp.md %}), [DATE]({% link docs/current/sql/data_types/date.md %}), [INTERVAL]({% link docs/current/sql/data_types/interval.md %}) 같은 시간 타입의 부분을 추출하거나 조작할 때 쓸 수 있어요.
추출하거나 조작할 부분은 아래 표의 문자열 중 하나로 지정돼요. 예시 열은 타임스탬프 2021-08-03 11:59:44.123456의 해당 부분을 보여줘요. 첫 번째 표의 항목만 INTERVAL에서 추출하거나 그것을 구성하는 데 사용할 수 있어요.
julian과epoch은DOUBLE을 반환하는 것을 제외하고, 모든 부분은 정수로 추출돼요. DuckDB에는 무한 정수 값이 없으므로 무한 타임스탬프에 대해서는NULL이 반환돼요.
출처: 문서
본문
날짜 부분 지정자이면서 간격에도 사용할 수 있는 것
| Specifier | Description | Synonyms | Example |
|---|---|---|---|
century |
Gregorian century | cent, centuries, c |
21 |
day |
Gregorian day | days, d, dayofmonth |
3 |
decade |
Gregorian decade | dec, decades, decs |
202 |
hour |
Hours | hr, hours, hrs, h |
11 |
microseconds |
Sub-minute microseconds | microsecond, us, usec, usecs, usecond, useconds |
44123456 |
millennium |
Gregorian millennium | mil, millenniums, millenia, mils, millenium |
3 |
milliseconds |
Sub-minute milliseconds | millisecond, ms, msec, msecs, msecond, mseconds |
44123 |
minute |
Minutes | min, minutes, mins, m |
59 |
month |
Gregorian month | mon, months, mons |
8 |
quarter |
Quarter of the year (1-4) | quarters |
3 |
second |
Seconds | sec, seconds, secs, s |
44 |
year |
Gregorian year | yr, y, years, yrs |
2021 |
날짜 부분 지정자로만 사용할 수 있는 것
| Specifier | Description | Synonyms | Example |
|---|---|---|---|
dayofweek |
Day of the week (Sunday = 0, Saturday = 6) | weekday, dow |
2 |
dayofyear |
Day of the year (1-365/366) | doy |
215 |
epoch |
Seconds since 1970-01-01 | 1760465850.6698709 |
|
era |
Gregorian era (CE/AD, BCE/BC) | 1 |
|
isodow |
ISO day of the week (Monday = 1, Sunday = 7) | 2 |
|
isoyear |
ISO Year number (Starts on Monday of week containing Jan 4th) | 2021 |
|
julian |
Julian Day number. | 2459430.4998162435 |
|
timezone_hour |
Time zone offset hour portion | 0 |
|
timezone_minute |
Time zone offset minute portion | 0 |
|
timezone |
Time zone offset in seconds | 0 |
|
week |
Week number | weeks, w |
31 |
yearweek |
ISO year and week number in YYYYWW format |
202131 |
타임존 부분은 [ICU]({% link docs/current/core_extensions/icu.md %}) 같은 타임존 확장이 설치되어 TIMESTAMP WITH TIME ZONE을 지원하지 않는 한 모두 0이에요.
부분 함수 (Part Functions)
특정 하위 필드를 얻기 위한 전용 추출 함수가 있어요.
| Name | Description |
|---|---|
century(date) |
Century. |
day(date) |
Day. |
dayofmonth(date) |
Day (synonym). |
dayofweek(date) |
Numeric weekday (Sunday = 0, Saturday = 6). |
dayofyear(date) |
Day of the year (starts from 1, i.e., January 1 = 1). |
decade(date) |
Decade (year / 10). |
epoch(date) |
Seconds since 1970-01-01. |
era(date) |
Calendar era. |
hour(date) |
Hours. |
isodow(date) |
Numeric ISO weekday (Monday = 1, Sunday = 7). |
isoyear(date) |
ISO Year number (Starts on Monday of week containing Jan 4th). |
julian(date) |
DOUBLE Julian Day number. |
microsecond(date) |
Sub-minute microseconds. |
millennium(date) |
Millennium. |
millisecond(date) |
Sub-minute milliseconds. |
minute(date) |
Minutes. |
month(date) |
Month. |
quarter(date) |
Quarter. |
second(date) |
Seconds. |
timezone_hour(date) |
Time zone offset hour portion. |
timezone_minute(date) |
Time zone offset minutes portion. |
timezone(date) |
Time zone offset in seconds. |
week(date) |
ISO Week. |
weekday(date) |
Numeric weekday synonym (Sunday = 0, Saturday = 6). |
weekofyear(date) |
ISO Week (synonym). |
year(date) |
Year. |
yearweek(date) |
BIGINT of combined ISO Year number and 2-digit version of ISO Week number. |
century(date)
| Description | Century. |
| Example | century(DATE '1992-02-15') |
| Result | 20 |
day(date)
| Description | Day. |
| Example | day(DATE '1992-02-15') |
| Result | 15 |
dayofmonth(date)
| Description | Day (synonym). |
| Example | dayofmonth(DATE '1992-02-15') |
| Result | 15 |
dayofweek(date)
| Description | Numeric weekday (Sunday = 0, Saturday = 6). |
| Example | dayofweek(DATE '1992-02-15') |
| Result | 6 |
dayofyear(date)
| Description | Day of the year (starts from 1, i.e., January 1 = 1). |
| Example | dayofyear(DATE '1992-02-15') |
| Result | 46 |
decade(date)
| Description | Decade (year / 10). |
| Example | decade(DATE '1992-02-15') |
| Result | 199 |
epoch(date)
| Description | Seconds since 1970-01-01. |
| Example | epoch(DATE '1992-02-15') |
| Result | 698112000.0 |
era(date)
| Description | Calendar era. |
| Example | era(DATE '0044-03-15 (BC)') |
| Result | 0 |
hour(date)
| Description | Hours. |
| Example | hour(timestamp '2021-08-03 11:59:44.123456') |
| Result | 11 |
isodow(date)
| Description | Numeric ISO weekday (Monday = 1, Sunday = 7). |
| Example | isodow(DATE '1992-02-15') |
| Result | 6 |
isoyear(date)
| Description | ISO Year number (Starts on Monday of week containing Jan 4th). |
| Example | isoyear(DATE '2022-01-01') |
| Result | 2021 |
julian(date)
| Description | DOUBLE Julian Day number. |
| Example | julian(DATE '1992-09-20') |
| Result | 2448886.0 |
microsecond(date)
| Description | Sub-minute microseconds. |
| Example | microsecond(timestamp '2021-08-03 11:59:44.123456') |
| Result | 44123456 |
millennium(date)
| Description | Millennium. |
| Example | millennium(DATE '1992-02-15') |
| Result | 2 |
millisecond(date)
| Description | Sub-minute milliseconds. |
| Example | millisecond(timestamp '2021-08-03 11:59:44.123456') |
| Result | 44123 |
minute(date)
| Description | Minutes. |
| Example | minute(timestamp '2021-08-03 11:59:44.123456') |
| Result | 59 |
month(date)
| Description | Month. |
| Example | month(DATE '1992-02-15') |
| Result | 2 |
quarter(date)
| Description | Quarter. |
| Example | quarter(DATE '1992-02-15') |
| Result | 1 |
second(date)
| Description | Seconds. |
| Example | second(timestamp '2021-08-03 11:59:44.123456') |
| Result | 44 |
timezone_hour(date)
| Description | Time zone offset hour portion. |
| Example | timezone_hour(DATE '1992-02-15') |
| Result | 0 |
timezone_minute(date)
| Description | Time zone offset minutes portion. |
| Example | timezone_minute(DATE '1992-02-15') |
| Result | 0 |
timezone(date)
| Description | Time zone offset in minutes. |
| Example | timezone(DATE '1992-02-15') |
| Result | 0 |
week(date)
| Description | ISO Week. |
| Example | week(DATE '1992-02-15') |
| Result | 7 |
weekday(date)
| Description | Numeric weekday synonym (Sunday = 0, Saturday = 6). |
| Example | weekday(DATE '1992-02-15') |
| Result | 6 |
weekofyear(date)
| Description | ISO Week (synonym). |
| Example | weekofyear(DATE '1992-02-15') |
| Result | 7 |
year(date)
| Description | Year. |
| Example | year(DATE '1992-02-15') |
| Result | 1992 |
yearweek(date)
| Description | BIGINT of combined ISO Year number and 2-digit version of ISO Week number. |
| Example | yearweek(DATE '1992-02-15') |
| Result | 199207 |