Kategorien:

Datums- und Uhrzeitfunktionen

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.

Alternative zu DATEDIFF.

Syntax

TIMEDIFF( <date_or_time_part> , <date_or_time_expr1> , <date_or time_expr2> )

Argumente

date_or_time_part

The unit of time. Must be one of the values listed in Unterstützte Datums- und Zeitabschnitte (e.g. month). The value can be a string literal or can be unquoted (e.g. 'month' or month).

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 from date_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.

Nutzungshinweise

  • Output values can be negative, for example, -12 days.

  • date_or_time_expr1 and date_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 muss einer der in Unterstützte Datums- und Zeitabschnitte aufgeführten Werte sein.

    If date_or_time_part is week (or any of its variations), the output is controlled by the WEEK_START session parameter. For more details, including examples, see Kalenderwochen und Wochentage.

  • 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.

Beispiele

Hier sehen Sie das Ergebnis der Subtraktion von zwei Datumsangaben, bei denen das zweite Jahr zwei Jahre hinter dem ersten liegt:

SELECT TIMEDIFF(YEAR, '2017-01-01', '2019-01-01') AS Years;
+-------+
| YEARS |
|-------|
|     2 |
+-------+

Dies zeigt, dass der Wert nicht gerundet, sondern abgeschnitten ist. Die Differenz liegt näher bei 12 Monaten als bei 11, aber Snowflake berechnet die Differenz als 11 Monate:

SELECT TIMEDIFF(MONTH, '2017-01-1', '2017-12-31') AS Months;
+--------+
| MONTHS |
|--------|
|     11 |
+--------+

Weitere Beispiele finden Sie unter DATEDIFF.

⇧
Zurück zum Anfang