- 카테고리:
TIMEDIFF¶
Calculates the difference between two date, time, or timestamp expressions based on the specified date or time part. The function returns the result of subtracting the second argument from the third argument.
DATEDIFF 를 대체합니다.
구문¶
TIMEDIFF( <date_or_time_part> , <date_or_time_expr1> , <date_or time_expr2> )
인자¶
date_or_time_part
The unit of time. Must be one of the values listed in 지원되는 날짜 및 시간 부분 (e.g.
month
). The value can be a string literal or can be unquoted (e.g.'month'
ormonth
).date_or_time_expr1
,date_or_time_expr2
The values to compare. Must be a date, a time, a timestamp, or an expression that can be evaluated to a date, a time, or a timestamp. The value
date_or_time_expr1
is subtracted fromdate_or_time_expr2
.
Returns¶
Returns an integer representing the number of units (seconds, days, etc.) difference between date_or_time_expr2
and
date_or_time_expr1
.
사용법 노트¶
Output values can be negative, for example, -12 days.
date_or_time_expr1
anddate_or_time_expr2
can be a date, time, or timestamp.The function supports units of years, quarters, months, weeks, days, hours, minutes, seconds, milliseconds, microseconds, and nanoseconds.
date_or_time_part
은 지원되는 날짜 및 시간 부분 에 나열된 값 중 하나여야 합니다.If
date_or_time_part
isweek
(or any of its variations), the output is controlled by the WEEK_START session parameter. For more details, including examples, see 달력상의 주 및 요일.The unit (e.g.
month
) used to calculate the difference determines which parts of the DATE, TIME, or TIMESTAMP field are used to determine the result and thus determines the precision of the result.Smaller units are not used, so values are not rounded. For example, even though the difference between January 1, 2021 and February 28, 2021 is closer to two months than to one month, the following returns one month:
DATEDIFF(month, '2021-01-01'::DATE, '2021-02-28'::DATE)
For a DATE value:
year
uses only the year and disregards all the other parts.month
uses the month and year.day
uses the entire date.
For a TIME value:
hour
uses only the hour and disregards all the other parts.minute
uses the hour and minute.second
uses the hour, minute, and second, but not the fractional seconds.millisecond
uses the hour, minute, second, and first three digits of the fractional seconds. Fractional seconds are not rounded. For example,DATEDIFF(milliseconds, '00:00:00', '00:00:01.1239')
returns 1.123 seconds, not 1.124 seconds.microsecond
uses the hour, minute, second, and first six digits of the fractional seconds. Fractional seconds are not rounded.nanosecond
uses the hour, minute, second, and all nine digits of the fractional seconds.
For a TIMESTAMP value:
The rules match the rules for DATE and TIME data types above. Only the specified unit and larger units are used.
예¶
이는 두 날짜를 뺀 결과를 보여주며, 여기서 두 번째 날짜가 첫 번째 날짜보다 2년 늦습니다.
SELECT TIMEDIFF(YEAR, '2017-01-01', '2019-01-01') AS Years; +-------+ | YEARS | |-------| | 2 | +-------+
이는 값이 반올림되지 않고 잘림을 나타냅니다. 차이는 11개월보다 12개월에 가깝지만, Snowflake는 차이를 11개월로 계산합니다.
SELECT TIMEDIFF(MONTH, '2017-01-1', '2017-12-31') AS Months; +--------+ | MONTHS | |--------| | 11 | +--------+
DATEDIFF 에 추가 예가 있습니다.