数据库日期时间类型笔记

最新推荐文章于 2025-02-26 02:23:00 发布

kfepiza

最新推荐文章于 2025-02-26 02:23:00 发布

阅读量3.1k

点赞数

分类专栏：日期时间时区等文章标签：数据库

本文链接：https://blog.youkuaiyun.com/kfepiza/article/details/115469717

版权

日期时间时区等专栏收录该内容

11 篇文章

订阅专栏

文章目录

前言
ISO-8601 日期和时间表示方法简介
- 特殊字符 - , : , T , Z
- 常用例子
MySql8.0.x : DATE, TIME, DATETIME, TIMESTAMP, YEAR. (5种)
- 0值显示
- 范围
SqlServer 2019 (没有 timestamp )

前言

整理数据库日期时间类型方便使用时查找

ISO-8601 日期和时间表示方法简介

特殊字符 - , : , T , Z

-(减号)用于年月日分割
:(冒号)用于时分秒分割
T用于日期与时间分割
Z代表UTC

常用例子

当前时区
2021-04-06T23:08:07.442902
2021-04-06T23:08:07
UTC
2021-04-06T23:08:07.442902Z
2021-04-06T23:08:07Z
偏移时区
2021-04-06T23:08:07.442902+08:00
2021-04-06T23:08:07.442902+8
2021-04-06T23:08:07+08:00
2021-04-06T23:08:07+8

MySql8.0.x : DATE, TIME, DATETIME, TIMESTAMP, YEAR. (5种)

0值显示

Data Type	“Zero” Value
DATE	‘0000-00-00’
TIME	‘00:00:00’
DATETIME	‘0000-00-00 00:00:00’
TIMESTAMP	‘0000-00-00 00:00:00’
YEAR	0000

范围

Data Type	最小值	最大值
DATE	1000-01-01	9999-12-31
TIME TIME(0)	-838:59:59	838:59:59
TIME	-838:59:59.000000	838:59:59.000000
DATETIME DATETIME(0)	1000-01-01 00:00:00	9999-12-31 23:59:59
DATETIME(6)	1000-01-01 00:00:00.000000	9999-12-31 23:59:59.999999
TIMESTAMP TIMESTAMP(0)	1970-01-01 00:00:01	2038-01-19 03:14:07
TIMESTAMP(6)	1970-01-01 00:00:01.000000	2038-01-19 03:14:07.999999
YEAR YEAR(4)	1901	2155

SqlServer 2019 (没有 timestamp )

官方建议使用 time、date、datetime2 和 datetimeoffset 数据类型

在这里插入图片描述

官方文档链接-日期和时间数据类型及函数 (Transact-SQL)

日期和时间数据类型

数据类型	格式	范围	精确度	存储大小（字节）	用户定义的秒的小数部分精度	时区偏移量
time	hh:mm:ss[.nnnnnnn]	00:00:00.0000000 到 23:59:59.9999999	100 纳秒	3 到 5	是	否
date	YYYY-MM-DD	0001-01-01 到 31.12.99	1 天	3	否	否
smalldatetime	YYYY-MM-DD hh:mm:ss	1900-01-01 到 2079-06-06	1 分钟	4	否	否
datetime	YYYY-MM-DD hh:mm:ss[.nnn]	1753-01-01 到 9999-12-31	0.00333 秒	8	否	否
datetime2	YYYY-MM-DD hh:mm:ss[.nnnnnnn]	0001-01-01 00:00:00.0000000 到 9999-12-31 23:59:59.9999999	100 纳秒	6 到 8	是	否
datetimeoffset	YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+	-]hh:mm	0001-01-01 00:00:00.0000000 到 9999-12-31 23:59:59.9999999（以 UTC 时间表示）	100 纳秒	8 到 10	是

返回结果示例

在这里插入图片描述

date和datetime支持 / - . 拆分年月日 datetime2只支持连字符 (-)

date和datetime支持正斜杠符号 (/)、连字符 (-) 或句点 (.) 作为分隔符的字符串中指定年、月、日。

2021/04/07
2021-04-07
2021.04.07

date

properties	值
语法	date
示例	CREATE TABLE Table1 ( Column1 date)
默认的字符串文字格式	YYYY-MM-DD
范围	0001-01-01 到 9999-12-31（对于 Informatica，为 1582-10-15 到 9999-12-31）
字符长度	10 位
精度	10
小数位数	0
存储大小	固定 3 个字节
精确度	一天
默认值	1900-01-01
日历	公历
夏时制感知	否

支持使用斜线 (/)、连字符 (-) 或句点 (.) 作为分隔符。

time

适用于：是SQL Server（所有支持的版本）是Azure SQL 数据库是Azure SQL 托管实例是Azure Synapse Analytics 是并行数据仓库
定义一天中的某个时间。此时间不能感知时区且基于 24 小时制。
备注

使用 Informatica 连接器为 PDW 客户提供 Informatica 信息。

time 说明

属性	值
语法	time [ (fractional second scale) ]
示例	CREATE TABLE Table1 ( Column1 time(7))
fractional seconds scale 括号里的值	为秒的小数部分指定数字的位数。这可以是从 0 到 7 的整数。对于 Informatica，这可以是从 0 到 3 的整数。默认小数位数为 7 (100ns)。
默认的字符串文字格式	对于 Informatica，为 hh:mm:ss[.nnnnnnn]）
范围	00:00:00.0000000 到 23:59:59.9999999（对于 Informatica，为 00:00:00.000 到 23:59:59.999）
各元素的范围	hh 是表示小时的两位数字，范围为 0 到 23。 mm 是表示分钟的两位数字，范围为 0 到 59。 ss 是表示秒的两位数字，范围为 0 到 59。 n* 是 0 到 7 位数字，范围为 0 到 9999999，它表示秒的小数部分。对于 Informatica，n* 是零到三位数字，范围为 0 到 999。
字符长度	最小 8 位 (hh:mm:ss)，最大 16 位 (hh:mm:ss.nnnnnnn)。对于 Informatica，最大值为 12 位 (hh:mm:ss.nnn)。
精度、小数位数	请参阅下表。（用户只能指定小数位数）
存储大小	固定 5 个字节，是使用默认的 100ns 秒的小数部分精度时的默认存储大小。在 Informatica 中，默认为 4 个字节，固定不变，同时秒的小数部分精度默认为 1 毫秒。
精确度	100 纳秒（Informatica 中为 1 毫秒）
默认值	00:00:00 此值用作从 date 隐式转换到datetime2 或 datetimeoffset 时追加的时间部分。
用户定义的秒的小数部分精度	是
时区偏移量感知和保留	否
夏时制感知	否

指定的小数位数	结果 (精度, 小数位数)	列长度（以字节为单位）	小数
time	(16,7)[Informatica 中为 (12,3)]	5（Informatica 中为 4）	7（Informatica 中为 3）
time(0)	(8,0)	3	0-2
time(1)	(10,1)	3	0-2
time(2)	(11,2)	3	0-2
time(3)	(12,3)	4	3-4
time(4)Informatica 中不支持。	(13,4)	4	3-4
time(5)Informatica 中不支持。	(14,5)	5	5-7
time(6)Informatica 中不支持。	(15,6)	5	5-7
time(7)Informatica 中不支持。	(16,7)	5	5-7

datetime

适用于：是SQL Server（所有支持的版本）是Azure SQL 数据库是Azure SQL 托管实例是Azure Synapse Analytics 是并行数据仓库
用于定义一个与采用 24 小时制并带有秒小数部分的一日内时间相组合的日期。

备注:

为新的工作使用 time、date、datetime2 和 datetimeoffset 数据类型。这些类型符合 SQL 标准。它们更易于移植。 time、datetime2 和 datetimeoffset 提供更高精度的秒数。 datetimeoffset 为全局部署的应用程序提供时区支持。

datetime 说明

属性	值
语法	datetime
使用情况	DECLARE @MyDatetime datetime CREATE TABLE Table1 (Column1 datetime)
默认的字符串文字格式（用于下级客户端）	不适用
日期范围	1753 年 1 月 1 日到 9999 年 12 月 31 日
时间范围	00:00:00 到 23:59:59.997
时区偏移量范围	无
各元素的范围	YYYY 是表示年份的四位数字，范围为 1753 到 9999。 MM 是表示指定年份中的月份的两位数字，范围为 01 到 12。 DD 是表示指定月份中的某一天的两位数字，范围为 01 到 31（最高值取决于相应月份）。 hh 是表示小时的两位数字，范围为 00 到 23。 mm 是表示分钟的两位数字，范围为 00 到 59。 ss 是表示秒钟的两位数字，范围为 00 到 59。 n* 为一个 0 到 3 位的数字，范围为 0 到 999，表示秒的小数部分。
字符长度	最低 19 位到最高 23 位
存储大小	8 个字节
精确度	舍入到 .000、.003 或 .007 秒三个增量。
默认值	1900-01-01 00:00:00
日历	公历（包括完整的年份范围。）
用户定义的秒的小数部分精度	否
时区偏移量感知和保留	否
夏时制感知	否

datetime 秒的小数部分精度的舍入

如下表所示，将 datetime 值舍入到 .000、.003、或 .007 秒的增量。

用户指定的值	系统存储的值
01/01/98 23:59:59.999	1998-01-02 00:00:00.000
01/01/98 23:59:59.995 01/01/98 23:59:59.996 01/01/98 23:59:59.997 01/01/98 23:59:59.998	1998-01-01 23:59:59.997
01/01/98 23:59:59.992 01/01/98 23:59:59.993 01/01/98 23:59:59.994	1998-01-01 23:59:59.993
01/01/98 23:59:59.990 01/01/98 23:59:59.991	1998-01-01 23:59:59.990

datetime2

适用于：是SQL Server（所有支持的版本）是Azure SQL 数据库是Azure SQL 托管实例是Azure Synapse Analytics 是并行数据仓库
定义结合了 24 小时制时间的日期。可将 datetime2 视作现有 datetime 类型的扩展，其数据范围更大，默认的小数精度更高，并具有可选的用户定义的精度。

datetime2 说明

属性	值
语法	datetime2 [ (fractional seconds precision) ]
使用情况	DECLARE @MyDatetime2 datetime2(7) CREATE TABLE Table1 ( Column1 datetime2(7))
默认的字符串文字格式（用于下级客户端）	YYYY-MM-DD hh:mm:ss[.fractional seconds] 有关详细信息，请参阅后面的“下级客户端的向后兼容性”部分。
日期范围	0001-01-01 到 31.12.99 公元 1 年 1 月 1 日到公元 9999 年 12 月 31 日
时间范围	00:00:00 到 23:59:59.9999999
时区偏移量范围	无
各元素的范围	YYYY 是一个四位数，范围从 0001 到 9999，表示年份。 MM 是一个两位数，范围从 01 到 12，它表示指定年份中的月份。 DD 是一个两位数，范围为 01 到 31（具体取决于月份），它表示指定月份中的某一天。 hh 是一个两位数，范围从 00 到 23，它表示小时。 mm 是一个两位数，范围从 00 到 59，它表示分钟。 ss 是一个两位数，范围从 00 到 59，它表示秒钟。 n* 代表 0 到 7 位数字，范围从 0 到 9999999，它表示秒小数部分。在 Informatica 中，当 n > 3 时，秒的小数部分会被截断。
字符长度	最低 19 位 (YYYY-MM-DD hh:mm:ss )，最高 27 位 (YYYY-MM-DD hh:mm:ss.0000000)
精度、小数位数	0 至 7 位，准确度为 100ns。默认精度为 7 位数。
存储大小 1	精度小于 3 的 6 个字节。精度为 3 和 4 的 6 个字节。所有其他精度需要 8 个字节。2
精确度	100 纳秒
默认值	1900-01-01 00:00:00
日历	公历
用户定义的秒的小数部分精度	是
时区偏移量感知和保留	否
夏时制感知	否

1 提供的值用于未压缩的行存储。使用数据压缩或列存储可能会改变每个精度的存储大小。此外，磁盘上和内存中的存储大小可能有所不同。例如，采用批处理模式时，datetime2 值始终需要 8 个字节的内存。
2 datetime2 值强制转换为 varbinary 值时，会向 varbinary 值添加一个额外的字节，用于存储精度。

datetime2 支持的字符串文字格式

ISO 8601	说明
YYYY-MM-DDThh:mm:ss[.nnnnnnn] YYYY-MM-DDThh:mm:ss[.nnnnnnn]	此格式不受 SET LANGUAGE 和 SET DATEFORMAT 会话区域设置的影响。包括在字符串内的 T、冒号 ( : ) 和句点 (.)，例如“2007-05-02T19:58:47.1234567”。

datetimeoffset (在 SQL Server 2008 中首次引入。)

适用于：是SQL Server（所有支持的版本）是Azure SQL 数据库是Azure SQL 托管实例是Azure Synapse Analytics 是并行数据仓库
用于定义一个与采用 24 小时制并可识别时区的一日内时间相组合的日期。

datetimeoffset 说明

属性	值
语法	datetimeoffset [ (fractional seconds precision) ]
使用情况	DECLARE @MyDatetimeoffset datetimeoffset(7) CREATE TABLE Table1 (Column1 datetimeoffset(7))
默认字符串文字格式（用于下级客户端）	YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+
日期范围	0001-01-01 到 31.12.99 公元 1 年 1 月 1 日到公元 9999 年 12 月 31 日
时间范围	00:00:00 到 23:59:59.9999999
时区偏移量范围	-14:00 到 +14:00
各元素的范围	YYYY 是表示年份的四位数字，范围为 0001 到 9999。 MM 是表示指定年份中的月份的两位数字，范围为 01 到 12。 DD 是表示指定月份中的某一天的两位数字，范围为 01 到 31（最高值取决于相应月份）。 hh 是表示小时的两位数字，范围为 00 到 23。 mm 是表示分钟的两位数字，范围为 00 到 59。 ss 是表示秒钟的两位数字，范围为 00 到 59。 n* 是 0 到 7 位数字，范围为 0 到 9999999，它表示秒的小数部分。 hh 是两位数，范围为 -14 到 +14。 mm 是两位数，范围为 00 到 59。
字符长度	最低 26 位 (YYYY-MM-DD hh:mm:ss {+
精度、小数位数	请参阅下表。
存储大小	默认值为 10 个字节的固定大小，默认的秒的小数部分精度为 100ns。
精确度	100 纳秒
默认值	1900-01-01 00:00:00 00:00
日历	公历
用户定义的秒的小数部分精度	是
时区偏移量感知和保留	是
夏时制感知	否

指定的小数位数	结果 (精度, 小数位数)	列长度（以字节为单位）	秒的小数部分精度
datetimeoffset	(34,7)	10	7
datetimeoffset(0)	(26,0)	8	0-2
datetimeoffset(1)	(28,1)	8	0-2
datetimeoffset(2)	(29,2)	8	0-2
datetimeoffset(3)	(30,3)	9	3-4
datetimeoffset(4)	(31,4)	9	3-4
datetimeoffset(5)	(32,5)	10	5-7
datetimeoffset(6)	(33,6)	10	5-7
datetimeoffset(7)	(34,7)	10	5-7

datetimeoffset 支持的 ISO-8061 字符串文字格式

ISO 8601	说明
YYYY-MM-DDThh:mm:ss[.nnnnnnn][{+或-}hh:mm]	这两种格式不受 SET LANGUAGE 和 SET DATEFORMAT 会话的区域设置的影响。 datetimeoffset 和 datetime 部分之间不允许有空格。
YYYY-MM-DDThh:mm:ss[.nnnnnnn]Z (UTC)	这种遵从 ISO 定义的格式表明 datetime 部分应采用协调世界时 (UTC) 表示。例如，1999-12-12 12:30:30.12345 -07:00 应表示为 1999-12-12 19:30:30.12345Z。