The function DATETIME_DELTA returns returns the number of total years, total months, total days, total hours, total minutes, total seconds, and total milliseconds between two DateTimes.
This function takes two DateTimes as input. It returns he number of total years, total months, total days, total hours, total minutes, total seconds, and total milliseconds between the two DateTimes. This output is a is formatted like another DateTimes, but, unlike most DateTimes, it does not express a valid time or day. Calcuation takes into account irregularities such as daylight savings time and leap years as well as timezone discrepancies.
Declaration
DATETIME_DELTA(datetime1, datetime2) -> delta
Parameters
datetime1 (required, type: DateTime)
Any DateTime.
datetime2 (required, type: DateTime)
Any DateTime.
Return Values
delta (type: Datetime)
The number of total years, total months, total days, total hours, total minutes, total seconds, and total milliseconds between datetime1 and datetime2. Calculations takes into account timezone discrepancies as well as irregularities such as daylight savings time and leap years.
Unlike most DateTimes, delta does not express a valid time or day.
Examples
Assume the following examples have access to the following DateTime values:
example_date_and_time1 ={
"date": {
"day": 16,
"month": 4,
"year": 2021
},
"time": {
"hour": 9,
"minute": 33,
"second": 10,
"millisecond": 0
},
"timeZone": "UTC"
}
example_date_and_time2 = {
"date": {
"day": 26,
"month": 4,
"year": 2019
},
"time": {
"hour": 16,
"minute": 55,
"second": 46,
"millisecond": 500
},
"timeZone": "America/Anguilla"
}
The following example returns the the number of years, months, years, hours, minutes, seconds, and milliseconds between example_date_and_time1 and example_date_and_time2. Note that the values associated with "day", "month", "hour", "minute", "second", and millisecond" are too large to be valid values describing a real DateTimes rather, the describe the total number of days, months, hours, minutes, seconds and milliseconds (respectively) between the two given DateTimes. Note also that took the different timezones of example_date_and_time1 and example_date_and_time2, even though the returned result appears at first glance to be in UTC:
DATETIME_DELTA(example_date_and_time1, example_date_and_time2) = {
"date": {
"day": 720,
"month": 23,
"year": 1
},
"time": {
"hour": 17292,
"minute": 1037557,
"second": 62253443,
"millisecond": 62253443500
},
"timeZone": "UTC"
}
DATETIME_DELTA returns the absolute difference between the given DateTimes; it does not matter which is given first. The following example returns the the number of days, months, years, hours, minutes, seconds, and milliseconds between example_date_and_time2 and example_date_and_time1; note that the output is the same as the above example, even though the DateTime values are given in reverse order:
DATETIME_DELTA(example_date_and_time2, example_date_and_time1) = {
"date": {
"day": 720,
"month": 23,
"year": 1
},
"time": {
"hour": 17292,
"minute": 1037557,
"second": 62253443,
"millisecond": 62253443500
},
"timeZone": "UTC"
}
Discussion
The DateTime returned by DATETIME_DELTA does not contain conventional DateTime information; while it is technically possible to use it as DateTime input in functions such as FORMAT_DATETIME or TIMESTAMP_FROM_DATETIME, the output returned in such cases will not be meaningful. To avoid such confusion, it is best practice to use DATETIME_DELTA in tandem with the DAY, MONTH, YEAR, HOUR, MINUTE, or SECOND function. (There is no analogous MILLISECOND function; such small differences in time are effectively negligible in the context of real-world apps.) Examples can be found below:
DAY(DATETIME_DELTA(datetime1, datetime2)) -> day_delta
MONTH(DATETIME_DELTA(datetime1, datetime2)) -> month_delta
YEAR(DATETIME_DELTA(datetime1, datetime2)) -> year_delta
HOUR(DATETIME_DELTA(datetime1, datetime2)) -> hour_delta
MINUTE(DATETIME_DELTA(datetime1, datetime2)) -> minute_delta
SECOND(DATETIME_DELTA(datetime1, datetime2)) -> second_delta
This returns a single Number: the days, months, years, hours, minutes, or seconds, respectively, between the two DateTimes.
Assume the following examples have access to example_date_and_time1 and example_date_and_time2 from the Examples section. The isolated difference between days, months, years, hours, minutes, or seconds (respectively) between them can be found as follows:
DAY(DATETIME_DELTA(datetime1, datetime2)) -> 720
MONTH(DATETIME_DELTA(datetime1, datetime2)) -> 23
YEAR(DATETIME_DELTA(datetime1, datetime2)) -> 1
HOUR(DATETIME_DELTA(datetime1, datetime2)) -> 17292
MINUTE(DATETIME_DELTA(datetime1, datetime2)) -> 1037557
SECOND(DATETIME_DELTA(datetime1, datetime2)) -> 62253443