sql datediff_SQL Server 2016中的新日期对象– DATEDIFF_BIG和AT TIME ZONE

最新推荐文章于 2023-01-17 09:03:42 发布

culuo4781

最新推荐文章于 2023-01-17 09:03:42 发布

阅读量809

点赞数

文章标签：字符串 python java linux 数据库

原文链接：https://www.sqlshack.com/new-date-objects-in-sql-server-2016-datediff_big-and-at-time-zone/

版权

sql datediff

SQL Server 2016 obtains the values of the date and time using the GetSystemTimeAsFileTime() Windows API, with precision fixed at 100 nanoseconds. The accuracy of these date and time values depends on the hardware specs and the version of the Windows of the server on which the SQL Server instance installed.

SQL Server 2016使用GetSystemTimeAsFileTime（）Windows API获取日期和时间的值，精度固定为100纳秒。这些日期和时间值的准确性取决于硬件规格和安装SQL Server实例的服务器的Windows版本。

In SQL Server 2016, new date and time objects are introduced that make it easier to deal with date and time values and with higher accuracy in nanoseconds similar to the one provided by the GetSystemTimeAsFileTime() Windows API.

在SQL Server 2016中，引入了新的日期和时间对象，使之更易于处理日期和时间值，并具有类似于GetSystemTimeAsFileTime（）Windows API提供的精度（以纳秒为单位）的更高精度。

The first date and time object is the DATEDIFF_BIG function that is used to evaluate the difference between two dates in the unit specified in the Datepart parameter.

第一个日期和时间对象是DATEDIFF_BIG函数，该函数用于以Datepart参数指定的单位评估两个日期之间的差。

The DATEDIFF_BIG function takes three parameters; datepart , startdate and enddate. The startdate and the enddate expressions can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset values. The datepart parameter can take one of the following: year, quarter, month, dayofyear, day, week, hour, minute, second, millisecond, microsecond, nanosecond values. Similar to the old DATEDIFF date and time function, the DATEDIFF_BIG function returns the count of the specified datepart boundaries crossed between the specified startdate and enddate, where the startdate will be subtracted from enddate. The difference between these two functions is the type of the result that will be returned from these date and time functions. The DATEDIFF function returns the difference between the startdate and the enddate as INT value, where the DATEDIFF_BIG function returns the difference between the startdate and the enddate as BIGINT value. The DATEDIFF_BIG function implicitly casts the string value as a datetime2 datatype. If the date value is passed as a string in the YDM format, you should explicitly cast the string to a datetime or smalldatetime datatypes, as the DATEDIFF_BIG does not support the YDM format.

DATEDIFF_BIG函数采用三个参数。 datepart，开始日期和结束日期。可以将开始日期和结束日期表达式解析为时间，日期，smalldatetime，datetime，datetime2或datetimeoffset值。 datepart参数可以采用以下之一：年，季，月，年，日，周，小时，分钟，秒，毫秒，微秒，纳秒值。与旧的DATEDIFF日期和时间函数类似，DATEDIFF_BIG函数返回在指定开始日期和结束日期之间交叉的指定日期部分边界的计数，其中将从结束日期中减去开始日期。这两个函数之间的区别是将从这些日期和时间函数返回的结果的类型。 DATEDIFF函数将开始日期和结束日期之间的差作为INT值返回，其中DATEDIFF_BIG函数将开始日期和结束日期之间的差作为BIGINT值返回。 DATEDIFF_BIG函数隐式将字符串值转换为datetime2数据类型。如果将日期值作为YDM格式的字符串传递，则应将字符串显式转换为datetime或smalldatetime数据类型，因为DATEDIFF_BIG不支持YDM格式。

The accuracy of the function result depends on the returned value data type. As you know, the value range of the integer datatype in SQL Server is from -2,147,483,648 to +2,147,483,647. So that, if we need to return a value beyond the integer range, then the DATEDIFF function is enough and will work fine. But if the returned value is more accurate and out the integer range, then this function will fail. You can think what will happen when using the Microseconds and Nanoseconds datepart with the DATEDIFF function that is out of the INT range. To overcome this limitation, SQL Server 2016 introduces the new DATEDIFF_BIG date and time function with BIGINT return data type, that will be accurate with too small datepart values, as the range of the BIGINT datatype is between -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807, which is wider than the INT datatype range. With this extended range, it will be easier to evaluate the date difference using the Microseconds and Nanoseconds dateparts. DATEDIFF_BIG function can be used within the columns list of the SELECT statement in addition to using it in the WHERE, HAVING, GROUP BY and ORDER BY clauses.

函数结果的准确性取决于返回值数据类型。如您所知，SQL Server中整数数据类型的值范围是-2,147,483,648至+2,147,483,647。这样，如果我们需要返回一个超出整数范围的值，则DATEDIFF函数就足够了，并且可以正常工作。但是，如果返回的值更准确且超出整数范围，则此函数将失败。您可以考虑将微秒和纳秒日期部分与DATEDIFF函数一起使用而超出INT范围会发生什么。为了克服此限制，SQL Server 2016引入了带有BIGINT返回数据类型的新DATEDIFF_BIG日期和时间函数，该日期和时间函数对于太小的datepart值将是准确的，因为BIGINT数据类型的范围介于-9,223,372,036,854,775,808到9,223,372,036,854,775,807之间，且比INT数据类型范围。使用此扩展范围，使用微秒和纳秒日期部分可以更轻松地评估日期差。除了在WHERE，HAVING，GROUP BY和ORDER BY子句中使用DATEDIFF_BIG函数外，它还可以在SELECT语句的列列表中使用。

Assume that we want to get the date difference between the current date and time and the coming day using all datepart values until the millisecond datepart using the DATEDIFF function as below:

假设我们要使用DATEDIFF函数使用所有datepart值获取当前日期和时间与未来一天之间的日期差，如下所示，使用DATEDIFF函数获取毫秒日期部分：

 
SELECT 
	DATEDIFF(YEAR, GETDATE(), GETDATE()+1) AS InYear,
    DATEDIFF(QUARTER , GETDATE(), GETDATE()+1) AS InQuarter,
	DATEDIFF(MONTH, GETDATE(), GETDATE()+1) AS InMonth,
	DATEDIFF(DAYOFYEAR , GETDATE(), GETDATE()+1) AS InDayOfYear,
	DATEDIFF(WEEK  , GETDATE(), GETDATE()+1) AS InWeek,
    DATEDIFF(DAY, GETDATE(), GETDATE()+1) AS InDays,
	DATEDIFF(HOUR , GETDATE(), GETDATE()+1) AS InHour,
	DATEDIFF(MINUTE, GETDATE(), GETDATE()+1) AS InMinute,
	DATEDIFF(SECOND, GETDATE(), GETDATE()+1) AS InSec,
	DATEDIFF(MILLISECOND, GETDATE(), GETDATE()+1 ) AS DiffInMilSec

The previous query will be executed successfully without any error and the result in our case will be like the following snapshot:

上一个查询将成功执行，没有任何错误，我们的结果将类似于以下快照：

But if we try to expand the accuracy of the date difference between the current date and time and tomorrow with the microsecond and nanosecond datepart values using the DATEDIFF function:

但是，如果我们尝试使用DATEDIFF函数使用微秒和纳秒的datepart值来扩展当前日期和时间与明天之间的日期差的准确性，则：

 
SELECT 
	DATEDIFF(YEAR, GETDATE(), GETDATE()+1) AS InYear,
    DATEDIFF(QUARTER , GETDATE(), GETDATE()+1) AS InQuarter,
	DATEDIFF(MONTH, GETDATE(), GETDATE()+1) AS InMonth,
	DATEDIFF(DAYOFYEAR , GETDATE(), GETDATE()+1) AS InDayOfYear,
	DATEDIFF(WEEK  , GETDATE(), GETDATE()+1) AS InWeek,
    DATEDIFF(DAY, GETDATE(), GETDATE()+1) AS InDays,
	DATEDIFF(HOUR , GETDATE(), GETDATE()+1) AS InHour,
	DATEDIFF(MINUTE, GETDATE(), GETDATE()+1) AS InMinute,
	DATEDIFF(SECOND, GETDATE(), GETDATE()+1) AS InSec,
	DATEDIFF(MILLISECOND, GETDATE(), GETDATE()+1 ) AS DiffInMilSec,
    DATEDIFF(MICROSECOND, GETDATE(), GETDATE()+1 ) AS DiffInMicroSec,
    DATEDIFF(NANOSECOND, GETDATE(), GETDATE()+1 ) AS DiffInNanSec

An error will be thrown showing that an overflow resulted from the DATEDIFF function exceeding its returned allowed range (the maximum value of the INT datatype) and recommend you to use a less precise datepart rather than the microsecond and nanosecond datepart values in the DATEDIFF function:

将引发错误，表明DATEDIFF函数导致的溢出超出了其返回的允许范围（INT数据类型的最大值），建议您在DATEDIFF函数中使用精度较低的datepart而不是微秒和纳秒datepart值：

If we replace the DATEDIFF function in the previous query with the new DATEDIFF_BIG function that is introduced in SQL Server 2016 to overcome the INT datatype overflow problem:

如果我们用SQL Server 2016中引入的新DATEDIFF_BIG函数替换了先前查询中的DATEDIFF函数，以克服INT数据类型溢出问题：

 
SELECT 
	DATEDIFF_BIG (YEAR, GETDATE(), GETDATE()+1) AS InYear,
    DATEDIFF_BIG (QUARTER , GETDATE(), GETDATE()+1) AS InQuarter,
	DATEDIFF_BIG (MONTH, GETDATE(), GETDATE()+1) AS InMonth,
	DATEDIFF_BIG (DAYOFYEAR , GETDATE(), GETDATE()+1) AS InDayOfYear,
	DATEDIFF_BIG (WEEK  , GETDATE(), GETDATE()+1) AS InWeek,
    DATEDIFF_BIG (DAY, GETDATE(), GETDATE()+1) AS InDays,
	DATEDIFF_BIG (HOUR , GETDATE(), GETDATE()+1) AS InHour,
	DATEDIFF_BIG (MINUTE, GETDATE(), GETDATE()+1) AS InMinute,
	DATEDIFF_BIG (SECOND, GETDATE(), GETDATE()+1) AS InSec,
	DATEDIFF_BIG (MILLISECOND, GETDATE(), GETDATE()+1 ) AS DiffInMilSec,
    DATEDIFF_BIG(MICROSECOND, GETDATE(), GETDATE()+1 ) AS DiffInMicroSec,
    DATEDIFF_BIG(NANOSECOND, GETDATE(), GETDATE()+1 ) AS DiffInNanSec

The query will be executed successfully without any error or overflow problem returning the date difference between the startdate and enddate with accuracy reaches to the nanoseconds, as the returned value will be with BIGINT datatype that has a wider range of values compared to the INT datatype range that is returned from the DATEDIFF function:

查询将成功执行，没有任何错误或溢出问题，返回起始日期和结束日期之间的日期差，精度达到纳秒，因为返回的值将是BIGINT数据类型，与INT数据类型范围相比，其值的范围更广从DATEDIFF函数返回的值：

Another date and time object introduced in SQL Server 2016 is the AT TIME ZONE expression that can be used to transform a date time value to a given target time zone. This is a very useful for global companies with branches distributed around the world. The syntax of the AT TIME ZONE expression is simple:

SQL Server 2016中引入的另一个日期和时间对象是AT TIME ZONE表达式，可用于将日期时间值转换为给定的目标时区。这对于在全球设有分支机构的跨国公司来说非常有用。 AT TIME ZONE表达式的语法很简单：

inputdate AT TIME ZONE timezone

输入日期AT TIME ZONE时区

It takes an inputdate value and the name of the destination time zone then converts the inputdate to the corresponding datetimeoffset value in the specified target time zone. The inputdate value can be resolved to a smalldatetime, datetime, datetime2, or datetimeoffset value.

它使用一个输入日期值和目标时区的名称，然后将输入日期转换为指定目标时区中的相应datetimeoffset值。 inputdate值可以解析为smalldatetime，datetime，datetime2或datetimeoffset值。

But how can we get the timezone name to use it in the AT TIME ZONE function? The AT TIME ZONE expression depends on the Windows mechanism in transforming the datetime values across the different time zones. It also uses the time zones that are stored in the Windows Registry under Time Zones registry path. From the Windows Date and Time page, if you click on the Change Time Zone button, you can see the full list of the time zones that are supported:

但是，如何获得时区名称以在AT TIME ZONE函数中使用它呢？ AT TIME ZONE表达式在不同时区之间转换日期时间值时取决于Windows机制。它还使用“时区”注册表路径下Windows注册表中存储的时区。在Windows“日期和时间”页面上，如果单击“更改时区”按钮，则可以看到受支持的时区的完整列表：

However, these time zones names are not the ones that you can use in the AT TIME ZONE expression. The correct names are the ones stored under the

但是，这些时区名称不是您可以在AT TIME ZONE表达式中使用的名称。正确的名称是存储在

KEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones registry path that can be expanded as follows:

KEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ Windows NT \ CurrentVersion \ Time Zones注册表路径，可以如下扩展：

Again, it is not that easy to browse this list each time we want to search for a specific time zone and use it in the AT TIME ZONE expression. To make it easy to get all the time zones that are stored in Windows, a new DMV sys.time_zone_info is introduced in SQL Server 2016 that exposes all supported time zone information stored under the

同样，每次我们想要搜索特定时区并在AT TIME ZONE表达式中使用它时，浏览此列表都不是那么容易。为了使获取Windows中存储的所有时区变得容易，SQL Server 2016中引入了新的DMV sys.time_zone_info ，它公开了存储在Windows下的所有受支持的时区信息。

KEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones registry path. Executing the below SELECT statement:

KEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ Windows NT \ CurrentVersion \ Time Zones注册表路径。执行以下SELECT语句：

 
SELECT * FROM sys.time_zone_info

This will return about 132 records of different time zones that are supported in SQL Server 2016. This information includes the time zone name, the offset of that time zone from UTC time zone and final the is_currently_dst that is True if this time zone is currently observing daylight savings time:

这将返回SQL Server 2016支持的大约132个不同时区的记录。此信息包括时区名称，该时区与UTC时区的偏移量以及最终is_currently_dst（如果当前正在观察该时区）为true夏令时：

We can also check the current time zone for the server on which the SQL Server instance is installed using the xp_regread system object that is used to read the registry keys values as follows:

我们还可以使用xp_regread系统对象检查用于安装SQL Server实例的服务器的当前时区，该对象用于读取注册表项值，如下所示：

 
DECLARE @CurrentTimeZone NVARCHAR(100) 
EXEC master.dbo.xp_regread 
    N'HKEY_LOCAL_MACHINE',
    N'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
    N'TimeZoneKeyName',
    @CurrentTimeZone OUTPUT
SELECT @CurrentTimeZone AS LocalTimeZone

The previous command executed successfully under a login with SYSADMIN rights and fails under unprivileged login. The result in my situation will be like:

先前的命令在具有SYSADMIN权限的登录名下成功执行，而在非特权登录名下失败。在我的情况下，结果将是：

The following example will list the TransactionDate column in different time zones and in the last step convert the value and display it in US Eastern time zone:

下面的示例将列出不同时区的TransactionDate列，并在最后一步转换该值并将其显示在美国东部时区：

 
SELECT TOP 10 [TransactionID]
      	  , [TransactionDate]
	  , [TransactionDate] AT TIME ZONE 'Jordan Standard Time'    AS InJordanTZ
	  , [TransactionDate] AT TIME ZONE 'Arabian Standard Time'   AS InArabTZ
	  , [TransactionDate] AT TIME ZONE 'US Eastern Standard Time' AS InUSTZ
, [TransactionDate] AT TIME ZONE 'Jordan Standard Time'  AT TIME ZONE 'US   Eastern Standard Time' AS InUSTZ2
 
  FROM [SQLShackDemo].[Production].[TransactionHistory]

The result will be similar to:

结果将类似于：

Another example for the AT TIME ZONE usage is converting between the date times. Assume we live in Jordan with time zone GMT+2 and we need to know the current time in USA. We can easily accomplish that using the below script, that change the current time to use the time zone of Jordan, use the SWITCHOFFSET function to change the time zone offset to the UTC time zone offset, use the DATENAME function to get the USA time zone offset to use it again in the SWITCHOFFSET function to change the offset to the USA time zone offset:

使用AT TIME ZONE的另一个示例是在日期时间之间进行转换。假设我们居住在时区为GMT + 2的约旦，我们需要知道美国的当前时间。我们可以使用下面的脚本轻松地完成该操作：将当前时间更改为使用约旦的时区，使用SWITCHOFFSET函数将时区偏移量更改为UTC时区偏移量，使用DATENAME函数获取美国时区偏移量以在SWITCHOFFSET函数中再次使用它以将偏移量更改为美国时区偏移量：

 
DECLARE @JordanCurrentDateTime datetime 
DECLARE @JordanOffset datetimeoffset
DECLARE @USADateTime datetime
DECLARE @USTimeZone nvarchar(10)
 
SET @JordanCurrentDateTime=  GETDATE()
SELECT @JordanOffset = @JordanCurrentDateTime AT TIME ZONE 'Jordan Standard Time'
SELECT @JordanOffset AS JordanTimeWithOffset
 
DECLARE @UTCOffset datetimeoffset
SELECT @UTCOffset = SWITCHOFFSET(@JordanOffset, '+00:00')
SELECT @UTCOffset AS JordanTimeWithUTCOffset
SELECT @USTimeZone= DATENAME(TZ, @UTCOffset  AT TIME ZONE 'US Eastern Standard Time')
SELECT @USTimeZone AS USOffset
SELECT @USADateTime = SWITCHOFFSET(@UTCOffset, @USTimeZone)
SELECT @USADateTime AS DateTimeInUSA

The output of each step will be as follows:

每个步骤的输出如下：

The combination of the AT TIME ZONE expression with the sys.time_zone_info DMV can be used to create your own dynamic calendar table that lists the current time at all supported time zones using the below simple script:

AT TIME ZONE表达式与sys.time_zone_info DMV的组合可用于创建您自己的动态日历表，该表使用以下简单脚本列出所有受支持时区的当前时间：

 
CREATE TABLE #AllTimeZones
( TZ_Name NVARCHAR(100),is_currently_dst bit) 
 
INSERT INTO #AllTimeZones
SELECT  Name , is_currently_dst  FROM sys.time_zone_info
 
SELECT SYSUTCDATETIME() AT TIME ZONE TZ_Name as TZ_Time, TZ_Name,is_currently_dst from #AllTimeZones

The result will be like:

结果将如下所示：

结论： (Conclusion:)

SQL Server 2016 introduced two new date and time objects that make it easier for us to deal with the date and time values. The first function is the DATEDIFF_BIG function that overcomes the INT range limitation of the DATEDIFF function by returning BIGINT value, providing us with more accurate date and time differences. The second object is the AT TIME ZONE expression that is used to convert the current time to the target time zone specified after the AT TIME ZONE. In this article, number of examples are shown to describe how we can take benefits from these new objects.

SQL Server 2016引入了两个新的日期和时间对象，使我们可以更轻松地处理日期和时间值。第一个函数是DATEDIFF_BIG函数，它通过返回BIGINT值克服了DATEDIFF函数的INT范围限制，从而为我们提供了更准确的日期和时间差。第二个对象是AT TIME ZONE表达式，用于将当前时间转换为AT TIME ZONE之后指定的目标时区。在本文中，显示了许多示例来描述我们如何从这些新对象中受益。

有用的链接： (Useful links:)

翻译自: https://www.sqlshack.com/new-date-objects-in-sql-server-2016-datediff_big-and-at-time-zone/

sql datediff

culuo4781

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
sql datediff_SQL Server 2016中的新日期对象– DATEDIFF_BIG和AT TIME ZONE

sql datediff SQL Server 2016 obtains the values of the date and time using the GetSystemTimeAsFileTime() Windows API, with precision fixed at 100 nanoseconds. The accuracy of these date and time va...
复制链接

扫一扫