DateTimeDiff (Azure Cosmos DB)DateTimeDiff (Azure Cosmos DB)
适用于:
SQL API
返回指定的 StartDate 和 EndDate 之间所跨的指定 DateTimePart 边界的计数(以带符号整数值的形式) 。Returns the count (as a signed integer value) of the specified DateTimePart boundaries crossed between the specified StartDate and EndDate.
语法Syntax
DateTimeDiff (<DateTimePart> , <StartDate> , <EndDate>)
参数Arguments
DateTimePartDateTimePart
DateTimeAdd 向其添加整数的日期部分。The part of date to which DateTimeAdd adds an integer number. 此表列出了所有有效的 DateTimePart 参数:This table lists all valid DateTimePart arguments:
DateTimePartDateTimePart | 缩写abbreviations |
---|---|
YearYear | "year", "yyyy", "yy""year", "yyyy", "yy" |
MonthMonth | "month", "mm", "m""month", "mm", "m" |
日期Day | "day", "dd", "d""day", "dd", "d" |
HourHour | "hour", "hh""hour", "hh" |
MinuteMinute | "minute", "mi", "n""minute", "mi", "n" |
秒Second | "second", "ss", "s""second", "ss", "s" |
MillisecondMillisecond | "millisecond", "ms""millisecond", "ms" |
MicrosecondMicrosecond | "microsecond", "mcs""microsecond", "mcs" |
NanosecondNanosecond | "nanosecond", "ns""nanosecond", "ns" |
StartDateStartDate
YYYY-MM-DDThh:mm:ss.fffffffZ
格式的 UTC 日期和时间 ISO 8601 字符串值,其中:UTC date and time ISO 8601 string value in the format YYYY-MM-DDThh:mm:ss.fffffffZ
where:
格式Format | 描述Description | |
---|---|---|
YYYYYYYY | 四位数的年份four-digit year | |
MMMM | 两位数的月份(01 = 1 月,依此类推。)two-digit month (01 = January, etc.) | |
DDDD | 两位数的月份日期(01 到 31)two-digit day of month (01 through 31) | |
TT | 时间元素开头的符号signifier for beginning of time elements | |
hhhh | 两位数的小时(00 到 23)two-digit hour (00 through 23) | |
MMmm | 两位数的分钟(00 到 59)two-digit minutes (00 through 59) | |
ssss | 两位数的秒(00 到 59)two-digit seconds (00 through 59) | |
.fffffff.fffffff | 七位数的小数秒seven-digit fractional seconds | |
ZZ | UTC(协调世界时)指示符UTC (Coordinated Universal Time) designator |
EndDateEndDate
YYYY-MM-DDThh:mm:ss.fffffffZ
格式的 UTC 日期和时间 ISO 8601 字符串值UTC date and time ISO 8601 string value in the format YYYY-MM-DDThh:mm:ss.fffffffZ
返回类型Return types
返回带符号整数值。Returns a signed integer value.
备注Remarks
DateTimeDiff 返回 undefined
,原因如下:DateTimeDiff will return undefined
for the following reasons:
- 指定的 DateTimePart 值无效The DateTimePart value specified is invalid
- StartDate 或 EndDate不是有效的 ISO 8601 DateTimeThe StartDate or EndDate is not a valid ISO 8601 DateTime
DateTimeDiff 始终返回带符号整数值,它度量跨越的 DateTimePart 边界数,而非时间间隔。DateTimeDiff will always return a signed integer value and is a measurement of the number of DateTimePart boundaries crossed, not measurement of the time interval.
示例Examples
以下示例计算 2020-01-01T01:02:03.1234527Z
和 2020-01-03T01:02:03.1234567Z
之间跨越的天的边界的数目。The following example computes the number of day boundaries crossed between 2020-01-01T01:02:03.1234527Z
and 2020-01-03T01:02:03.1234567Z
.
SELECT DateTimeDiff("day", "2020-01-01T01:02:03.1234527Z", "2020-01-03T01:02:03.1234567Z") AS DifferenceInDays
[
{
"DifferenceInDays": 2
}
]
以下示例计算 2028-01-01T01:02:03.1234527Z
和 2020-01-03T01:02:03.1234567Z
之间跨越的年份边界的数目。The following example computes the number of year boundaries crossed between 2028-01-01T01:02:03.1234527Z
and 2020-01-03T01:02:03.1234567Z
.
SELECT DateTimeDiff("yyyy", "2028-01-01T01:02:03.1234527Z", "2020-01-03T01:02:03.1234567Z") AS DifferenceInYears
[
{
"DifferenceInYears": -8
}
]
以下示例计算 2020-01-01T01:00:00.1234527Z
和 2020-01-01T01:59:59.1234567Z
之间跨越的小时边界的数目。The following example computes the number of hour boundaries crossed between 2020-01-01T01:00:00.1234527Z
and 2020-01-01T01:59:59.1234567Z
. 即使这些 DateTime 值之间相隔超过 0.99 小时,DateTimeDiff
仍将返回 0,因为没有超过小时边界。Even though these DateTime values are over 0.99 hours apart, DateTimeDiff
returns 0 because no hour boundaries were crossed.
SELECT DateTimeDiff("hh", "2020-01-01T01:00:00.1234527Z", "2020-01-01T01:59:59.1234567Z") AS DifferenceInHours
[
{
"DifferenceInHours": 0
}
]