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.1234527Z2020-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.1234527Z2020-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.1234527Z2020-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
    }
]

后续步骤Next steps