DateTime , DateTime2 ,DateTimeOffset 之间的小区别 (转载)
SQL Server中DateTime , DateTime2 ,DateTimeOffset的区别
闲来无事列了个表比对一下这3兄弟之间还是有一点差距的╮(╯_╰)╭
|
DateTime
|
DateTime2
|
DateTimeOffset
|
日期范围 |
1753-01-01到 9999-12-31
|
0001-01-01 到 9999-12-31
|
0001-01-01 到 9999-12-31
|
时间范围 |
00:00:00 到 23:59:59.997
|
00:00:00 到 23:59:59.9999999
|
00:00:00 到 23:59:59.9999999
|
存储字节大小
|
8字节
|
精度小于 3 时为 6 个字节;精度为 3 和 4 时为 7 个字节。 所有其他精度则需要 8 个字节
|
精度小于 3 时为 6 个字节;精度为 3 和 4 时为 7 个字节。 所有其他精度则需要 8 个字节
|
精度 |
舍入到 .000、.003 或 .007 秒三个增量。
|
100 纳秒
|
100 纳秒
|
支持自定义小数精度
|
否
|
是 |
是
|
时区
|
无
|
无
|
-14:59 到 +14:59
|
至于 DateTimeOffset ,时间部分都是UTC时间。
比方说现在我们在GMT+8:00 的位置,所以当地时间是 2019-12-09 21:33:26,如果用DateTimeOffset 来表示就是 2019-12-09 13:33:26 -08:00
DECLARE @dto DATETIMEOFFSET(7)=N'2019-12-09 21:33:26 +08:00' DECLARE @dtLocal DATETIME=N'2019-12-09 21:33:26' DECLARE @dtUTC DATETIME=N'2019-12-09 13:33:26' SELECT @dto,@dtLocal,DATEDIFF(HH,@dto,@dtLocal),@dtUTC,DATEDIFF(HH,@dto,@dtUTC)
结果如下:
然后就是在SQL Server中,当DateTimeOffset 转格式成为其它2兄弟的时候,时区会被舍去的啊……的啊……的啊
DECLARE @dto DATETIMEOFFSET(7)=SYSDATETIMEOFFSET() SELECT @dto,CAST(@dto AS DATETIME),CAST(@dto AS DATETIME2(7))
结果如下:
C#中DateTime和DateTimeOffset的区别
可以理解为DateTimeOffset是带时区偏差的DateTime,如下MSDN代码所示:
using System; using System.Collections.ObjectModel; public class TimeOffsets { public static void Main() { DateTime thisDate = new DateTime(2007, 3, 10, 0, 0, 0); DateTime dstDate = new DateTime(2007, 6, 10, 0, 0, 0); DateTimeOffset thisTime; thisTime = new DateTimeOffset(dstDate, new TimeSpan(-7, 0, 0)); ShowPossibleTimeZones(thisTime); thisTime = new DateTimeOffset(thisDate, new TimeSpan(-6, 0, 0)); ShowPossibleTimeZones(thisTime); thisTime = new DateTimeOffset(thisDate, new TimeSpan(+1, 0, 0)); ShowPossibleTimeZones(thisTime); } private static void ShowPossibleTimeZones(DateTimeOffset offsetTime) { TimeSpan offset = offsetTime.Offset; ReadOnlyCollection<TimeZoneInfo> timeZones; Console.WriteLine("{0} could belong to the following time zones:", offsetTime.ToString()); // Get all time zones defined on local system timeZones = TimeZoneInfo.GetSystemTimeZones(); // Iterate time zones foreach (TimeZoneInfo timeZone in timeZones) { // Compare offset with offset for that date in that time zone if (timeZone.GetUtcOffset(offsetTime.DateTime).Equals(offset)) Console.WriteLine(" {0}", timeZone.DisplayName); } Console.WriteLine(); } } // This example displays the following output to the console: // 6/10/2007 12:00:00 AM -07:00 could belong to the following time zones: // (GMT-07:00) Arizona // (GMT-08:00) Pacific Time (US & Canada) // (GMT-08:00) Tijuana, Baja California // // 3/10/2007 12:00:00 AM -06:00 could belong to the following time zones: // (GMT-06:00) Central America // (GMT-06:00) Central Time (US & Canada) // (GMT-06:00) Guadalajara, Mexico City, Monterrey - New // (GMT-06:00) Guadalajara, Mexico City, Monterrey - Old // (GMT-06:00) Saskatchewan // // 3/10/2007 12:00:00 AM +01:00 could belong to the following time zones: // (GMT+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna // (GMT+01:00) Belgrade, Bratislava, Budapest, Ljubljana, Prague // (GMT+01:00) Brussels, Copenhagen, Madrid, Paris // (GMT+01:00) Sarajevo, Skopje, Warsaw, Zagreb // (GMT+01:00) West Central Africa
微软官方对C#中DateTime和DateTimeOffset区别的解释:
Choosing between DateTime, DateTimeOffset, TimeSpan, and TimeZoneInfo