运维开发网

时间戳用法的详细介绍

运维开发网 https://www.qedev.com 2022-05-08 16:02 出处:网络
这篇文章主要介绍了mysql之TIMESTAMP(时间戳)用法,需要的朋友可以参考下 时间戳是指从1970年1月1日格林尼治时间00: 00: 00(北京时间1970年1月1日08: 00: 00)到现在的总秒数。MySQL的各种版本部署在生产环境中,包括

这篇文章主要介绍了mysql之TIMESTAMP(时间戳)用法,需要的朋友可以参考下

时间戳是指从1970年1月1日格林尼治时间00: 00: 00(北京时间1970年1月1日08: 00: 00)到现在的总秒数。
MySQL的各种版本部署在生产环境中,包括MySQL 5.5/5.6/5.7的三个主要版本和n个次要版本。由于MySQL向上兼容性差,同一条SQL在不同版本中表现不同。下面从几个方面详细介绍时间戳数据类型。


时间戳数据存取

在MySQL的上述三大版本中,默认时间戳类型的取值范围为' 1970-01-01 00:00:01' UTC到' 2038-01-19 03:14:07' UTC,数据精确到秒级。这个值范围包含了大约22亿个值,所以在MySQL中使用了4.0%。

1.存储时间戳数据时,先将当地时区时间转换为UTC时区时间,再将UTC时区时间转换为INT格式的毫秒值(使用UNIX_TIMESTAMP函数),然后存储在数据库中。
2。读取时间戳数据时,先将INT格式的毫秒值转换为UTC时区(使用FROM_UNIXTIME函数),再转换为本地时区,最后返回给客户端。

在MySQL 5 . 6 . 4及以后版本中,时间戳类型的数据最高精度可以是微秒(百万分之一秒),数据类型定义为timestamp(N),N),取值范围为0-6,默认值为0。如果需要精确到毫秒,则设置为时间戳(3),如果需要精确到微秒,则设置为时间戳(6)。提高数据精度的代价是其内部存储/[


时间戳字段定义

时间戳字段定义主要影响两种类型的操作:

插入记录时,时间戳字段包含DEFAULT CURRENT_TIMESTAMP,如插入记录时未指定具体时间数据则将该时间戳字段值设置为当前时间 更新记录时,时间戳字段包含ON UPDATE CURRENT_TIMESTAMP,如更新记录时未指定具体时间数据则将该时间戳字段值设置为当前时间

PS1: CURRENT_TIMESTAMP意味着使用CURRENT_TIMESTAMP()函数获取当前时间,类似于NOW()函数。

根据以上两种类型的操作,时间戳列可以有四种组合定义,它们的含义是:

当字段定义为timestamp,表示该字段在插入和更新时都不会自动设置为当前时间。 当字段定义为timestamp DEFAULT CURRENT_TIMESTAMP,表示该字段仅在插入且未指定值时被赋予当前时间,再更新时且未指定值时不做修改。 当字段定义为timestamp ON UPDATE CURRENT_TIMESTAMP,表示该字段在插入且未指定值时被赋值为"0000-00-00 00:00:00",在更新且未指定值时更新为当前时间。 当字段定义为timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,表示该字段在插入或更新时未指定值,则被赋值为当前时间。

PS1:在MySQL中执行的表创建语句和最终的表创建语句是有区别的。建议使用SHOW CREATE TABLE TB_XXX获取所创建表的表创建语句。


时间戳字段在MySQL各版本的使用差异

在MySQL 5.5和更早版本中,只能将一个时间戳字段定义为discount current _ timestamp或ON UPDATE CURRENT_TIMESTAMP,但在MySQL 5.6和MySQL 5.7版本中取消了这一限制。

在MySQL 5.6中,explicit _ defaults _ for _ timestamp的默认值为1;在MySQL 5.7中,explicit _ defaults _ for _ timestamp的默认值为0;

在MySQL 5.5和MySQL 5.7中,时间戳类型默认不为空;在MySQL 5.6中,时间戳类型默认为NULL

当在建表语句中设置c1时间戳时,

相当于MySQL 5.5中更新current _ timestamp时C1时间戳不为null默认current _ timestamp;

相当于MySQL 5.6中的c1时间戳NULL默认NULL;

相当于MySQL 5.7中更新current _ timestamp时C1时间戳不为null默认current _ timestamp;

当C1时间戳在建表语句中默认为0时,

相当于MySQL 5.5中的C1时间戳not null默认' 0000-00-00 00:00:00 ';
相当于MySQL 5.6中C1时间戳null默认' 0000-00-00 00:00:00 ';
在MySQL 5.7中,相当于C1时间戳不为空默认' 0000-00-00 00:00:00 ';
PS1:MySQL 5.6和MySQL 5.7的主要区别受参数explicit _ defaults _ for _ timestamp的默认值影响。

PS2:当时间戳列的默认值为“0000-00-00 00:00:00”时,使用此默认值“超出时间戳值的范围”不会生成警告。


时间戳类型引发的异常

当MySQL参数time_zone=system时,查询时间戳字段会调用系统时区进行时区转换。但由于系统时区的全局锁问题,在发生多个并发大数据访问时,会设置频繁的线程上下文切换、CPU利用率飙升、系统响应缓慢、假死等问题。


时间戳类型和时间类型选择

在一些“数据库指南”文档中,建议使用时间戳类型而不是日期时间字段。原因是时间戳类型使用4个字节,而datetime字段使用8个字节。但是,随着磁盘性能的提高和内存成本的降低,在实际生产环境中,时间戳类型的使用并不会带来太大的性能提升,但是时间戳类型的定义和值范围可能会限制和影响业务使用。

在MySQL 5 . 6 . 4及以后版本中,对于时间戳数据可以获得最高精度微秒,对于datetime数据也可以获得最高精度微秒,对于datetime数据也可以获得同样的效果,比如将字段定义为dt1 datetime(3)not null default now(3)on update now(3);datetime的访问范围是' 1000-01-01 00: 00: 00.00000 '到' 9999-12-31 23: 59: 59.99999 ',可以更好的存储各个时间段的数据。


时间戳类型使用建议 在只关心数据最后更新时间的情况下,建议将时间戳列定义为TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; 在关心创建时间和更新时间的情况下,建议将更新时间设置为时间戳字段,将创建时间定义为DAETIME 或 TIMESTAMP DEFAULT ‘0000-00-00 00:00:00',并在插入记录时显式指定创建时间; 建议在表中只定义单个时间戳列,并显式定义DEFAULT 和 ON UPDATE属性; 虽然在MySQL中可以对时间戳字段赋值或更新,但建议仅在必要的情况下对时间戳列进行显式插入和更新; 建议将time_zone参数设置为system外的值,如中国地区服务器设置为'+8:00'; 建议将MySQL线下测试版本和线上生产版本保持一致。


Timestamp和datetime的异同

相同点:

1.可以自动更新初始化,默认显示格式相同YYYY-MM-dd HH:mm:ss
。区别:
2。时间戳的时间范围是:' 1970-01-01 00: 01' utc到' 2038-01-19 01' 4字节存储
3。datetime的时间范围:“1000-01-01 00: 00: 00”到“9999-12-31 23: 59: 59”,不支持时区和8字节存储。


设置timestamp和date的自动更新时间

当更新一条数据或插入一条新数据而没有给date和mydate赋值时,date和mydate字段将自动默认为当前时间

038问题
当时间戳存储时间超过' 2038-01-19 03:14:07' UTC时,mysql会报错,因为这是mysql本身的问题,也就是说时间戳有上限。如果超过了,自然会报错。具体原因见官方文件:https://dev.mysql.com/doc/refman/8.0/en/datetime.html。部分截图如下:[/br


解决办法

虽然时间戳有上限,但是保存了时间戳,不用考虑时区。如果需要处理时区相关的需求,解决2038的限制,建议将时间戳改为整数类型保存时间戳,然后在程序中进行转换。(这个方案还没有实施,只是个建议。慎用!!)
如果不需要考虑时区,只需要将时间戳替换为datatime类型即可,因为datatime的取值范围要大得多,所以可以看到上图;

替换的想法:

1.修改原始字段的名称;

ALTER TABLE `student` CHANGE `entry_date` `temp_entry_date` timestamp NOT NULL default '0000-00-00 00:00:00';

创建一个datatime类型的新字段(创建一个新列来替换原来的列);

ALTER TABLE `student` ADD `entry_date` DATETIME NOT NULL default '0000-00-00 00:00:00';

将原始字段列的数据复制到新字段列;

UPDATE `student` SET `entry_date` = `temp_entry_date`;

删除原列;

ALTER TABLE `student` DROP `temp_entry_date`;

Sql完成如下:(注意原始时间戳的默认值,这个也需要添加)

ALTER TABLE `student` CHANGE `entry_date` `temp_entry_date` timestamp NOT NULL default '0000-00-00 00:00:00';ALTER TABLE `student` ADD `entry_date` DATETIME NOT NULL default '0000-00-00 00:00:00';UPDATE `student` SET `entry_date` = `temp_entry_date`;ALTER TABLE `student` DROP `temp_entry_date`;


mysql之TIMESTAMP(时间戳)用法


一、TIMESTAMP的变体

时间戳时间戳在创建时可以有多种不同的特征,例如:

1.创建新记录和修改现有记录时刷新此数据列:

TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

2.创建新记录时将此字段设置为当前时间,但以后修改时不要刷新:

TIMESTAMP DEFAULT CURRENT_TIMESTAMP

3.创建新记录时将此字段设置为0,并在以后修改时刷新它:

TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

4.创建新记录时将此字段设置为给定值,以后修改时刷新:

TIMESTAMP DEFAULT ‘yyyy-mm-dd hh:mm:ss' ON UPDATE CURRENT_TIMESTAMP

MySQL目前不支持将列默认的形式作为函数。例如,如果其中一列的默认值是当前更新日期和时间,则可以使用TIMESTAMP列类型。下面详细介绍了时间戳列类型。


二、TIMESTAMP列类型

时间戳值可以是从1970年到2037年的某个时间,精度为一秒,其值显示为数字。
时间戳值的显示大小格式如下表所示:
:

+---------------+----------------+| 列类型    | 显示格式    || TIMESTAMP(14) | YYYYMMDDHHMMSS | | TIMESTAMP(12) | YYMMDDHHMMSS  || TIMESTAMP(10) | YYMMDDHHMM   || TIMESTAMP(8) | YYYYMMDD    || TIMESTAMP(6) | YYMMDD     || TIMESTAMP(4) | YYMM      || TIMESTAMP(2) | YY       |+---------------+----------------+

“完整”时间戳格式是14位,但是时间戳列也可以使用较短的显示大小。最常见的显示尺寸是6、8、12和14。
在创建表时,您可以指定任意的显示大小,但是定义0或大于14的列长度将被强制定义为14的列长度。
列长度从1到13的奇数值被强制为下一个更大的偶数。

列如下:

定义字段长度   强制字段长度TIMESTAMP(0) -gt; TIMESTAMP(14)TIMESTAMP(15)-gt; TIMESTAMP(14)TIMESTAMP(1) -gt; TIMESTAMP(2)TIMESTAMP(5) -gt; TIMESTAMP(6)

的所有时间戳列具有相同的存储大小,并且指定周期时间值的全精度(14位)用于存储合法值,而与显示大小无关。非法日期将被强制存储为0。

有几个含义:

1.虽然在构建表时定义了TIMESTAMP(8)列,但是在插入和更新数据时,TIMESTAMP列实际上保存了14位数据(包括年、月、日、分、秒),但是在查询时,MySQL会返回8位年、月、日数据给你。如果使用ALTER TABLE来加宽窄的时间戳列,将会显示以前“隐藏”的信息。

2.类似地,收缩时间戳列也不会导致信息丢失,只是在显示前一个值时显示的信息较少。

3.虽然时间戳值存储为完全精度,但是唯一直接操作存储值的函数是UNIX _ TIMESTAMP();由于MySQL返回的时间戳列的列值是格式化的检索值,这意味着您可能无法使用某些函数来操作时间戳列(如HOUR()或SECOND()),除非时间戳值的相关部分包含在格式化的值中。
例如,只有当TIMESTAMP列被定义为TIMESTAMP(10)或更大时,才会显示TIMESTAMP列的HH部分,因此在较短的TIMESTAMP值上使用HOUR()会产生不可预知的结果。

4.非法时间戳值被转换为适当类型的“零”值(0000000000000)。(日期时间,日期相同)

例如,您可以使用以下语句来验证:

CREATE TABLE test ('id' INT (3) UNSIGNED AUTO_INCREMENT, 'date1'TIMESTAMP (8) PRIMARY KEY('id'));INSERT INTO test SET id = 1;SELECT * FROM test;+----+----------------+| id | date1     |+----+----------------+| 1 | 20021114    |+----+----------------+ALTER TABLE test CHANGE 'date1' 'date1' TIMESTAMP(14);SELECT * FROM test;+----+----------------+| id | date1     |+----+----------------+| 1 | 20021114093723 |+----+----------------+

您可以使用TIMESTAMP列类型用当前日期和时间自动标记插入或更新操作。
如果您有多个时间戳列,则只有第一个列会自动更新。在下列任何情况下,都会自动更新第一个时间戳列:

1.在INSERT或LOAD DATA INFILE语句中没有显式指定列值。
2。列值没有在UPDATE语句中显式指定,其他列会更改值。(注意,UPDATE将一列设置为其现有值,这不会导致TIMESTAMP列被更新,因为如果您将一列设置为其当前值,MySQL会忽略这一更改以提高效率。)
3。您显式地将时间戳列设置为NULL。
4。第一列以外的时间戳列也可以设置为当前日期和时间,只要该列设置为NULL,或者NOW()。

CREATE TABLE test ( 'id' INT (3) UNSIGNED AUTO_INCREMENT,'date1' TIMESTAMP (14),'date2' TIMESTAMP (14),PRIMARY KEY('id'));INSERT INTO test (id, date1, date2) VALUES (1, NULL, NULL);INSERT INTO test SET id= 2;+----+----------------+----------------+| id | date1     | date2     |+----+----------------+----------------+| 1 | 20021114093723 | 20021114093723 || 2 | 20021114093724 | 00000000000000 |+----+----------------+----------------+

在第一条指令中,因为date1和date2设置为NULL,所以date1和date2的值是当前时间。在第二条指令中,第一个时间戳列date1更新为当前时间,而两个时间戳列date2由于日期不合法而更改为“00000000000000”。

UPDATE test SET id= 3 WHERE id=1;+----+----------------+----------------+| id | date1     | date2     |+----+----------------+----------------+| 3 | 20021114094009 | 20021114093723 || 2 | 20021114093724 | 00000000000000 |+----+----------------+----------------+

该指令没有显式设置列值date2,因此第一个时间戳列date1将被更新为当前时间

UPDATE test SET id= 1,date1=date1,date2=NOW() WHERE id=3; +----+----------------+----------------+| id | date1     | date2     |+----+----------------+----------------+| 1 | 20021114094009 | 20021114094320 || 2 | 20021114093724 | 00000000000000 |+----+----------------+----------------+

在此指令中,因为设置了date1=date1,所以在更新数据时,date1的列值不会更改,因为设置了date2=NOW(),所以在更新数据时,date2的列值将更新为当前时间。该指令相当于:

UPDATE test SET id= 1,date1=date1,date2=NULL WHERE id=3;

由于MySQL返回的时间戳列显示为数字,所以可以使用DATE_FROMAT()函数格式化时间戳列,如下所示:

SELECT id,DATE_FORMAT(date1,'%Y-%m-%d %H:%i:%s') As date1,DATE_FORMAT(date2,'%Y-%m-%d %H:%i:%s') As date2 FROM test;+----+---------------------+---------------------+| id | date1        | date2        |+----+---------------------+---------------------+| 1 | 2002-11-14 09:40:09 | 2002-11-14 09:43:20 || 2 | 2002-11-14 09:37:24 | 0000-00-00 00:00:00 |+----+---------------------+---------------------+SELECT id,DATE_FORMAT(date1,'%Y-%m-%d') As date1,DATE_FORMAT(date2,'%Y-%m-%d') As date2 FROM test;+----+-------------+-------------+| id | date1    | date2    |+----+-------------+-------------+| 1 | 2002-11-14 | 2002-11-14 || 2 | 2002-11-14 | 0000-00-00 |+----+-------------+-------------+

在某种程度上,您可以将一种日期类型的值分配给另一种日期类型的对象。
但是,尤其需要注意的是,值可能会发生一些变化或信息丢失:

1.如果将日期值分配给DATETIME或TIMESTAMP对象,结果值的时间部分将设置为“00:00:00 ”,因为日期值不包含时间信息。
2。如果将日期时间或时间戳值赋给DATE对象,结果值的时间部分将被删除,因为日期类型不存储时间信息。
3。尽管日期时间、日期和时间戳值都可以用相同的格式集指定,但所有类型的值范围并不相同。

例如,时间戳值不能早于1970年或晚于2037年,这意味着日期(如“1968-01-01”)用作日期时间或日期值是合法的,但它不是正确的时间戳值!如果将这样的对象赋给时间戳列,它将被转换为0。


三、当指定日期值时,当心某些缺陷:

1.允许值被指定为字符串的松散格式可能会被欺骗。例如,由于使用了“:”分隔符,值“10:11:12”可能看起来像一个时间值,但如果在日期中使用,则上下文会将“2010-11-12”解释为年份。值“10:45:15”将被转换为“0000-00-00”,因为“45”不是合法的月份。

2.用两位数指定的年份值不明确,因为世纪未知。MySQL使用以下规则来解释2位数的年值:
将00-69范围内的年值转换为2000-2069。70-99范围内的年度值被更改为1970-1999。

PS:再给大家介绍一个Unix时间戳转换工具,自带各种语言时间戳的操作方法,包括PHP、MySQL、SQL Server、java等中时间戳的获取和转换技术。:

0

精彩评论

暂无评论...
验证码 换一张
取 消