MySQL时间戳和unix时间戳区别、MySQL中的时间类型

最新推荐文章于 2024-07-08 21:31:16 发布
最新推荐文章于 2024-07-08 21:31:16 发布
阅读量3.2k 收藏 1
点赞数 1
分类专栏： 数据库 文章标签： mysql unix 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_40177015/article/details/121754155
版权

文章目录

背景：MySQL中的timestamp和我们平常理解的timestamp有不同，借此背景整理了时间戳相关的知识点

1 时间戳

1.1 unix时间戳（unix timestamp）

定义为从GMT时间1970年01月01日00时00分00秒（北京时间1970年01月01日08时00分00秒）起至现在的总秒数

部分操作系统使用32位二进制数字表示时间。此类系统的Unix时间戳最多可以使用到GMT时间2038年01月19日03时14分07秒（二进制：01111111 11111111 11111111 11111111）。其后一秒，二进制数字会变为10000000 00000000 00000000 00000000，发生溢出错误，造成系统将时间误解为1901年12月13日20时45分52秒

使用64位二进制数字表示时间的系统则不会遇到这类溢出问题

不同编程语言的不同精度unix时间戳：（可以使用内置函数设置不同的精度）

  • java的时间默认精度是ms，因此unix时间戳是13位
  • c++，php默认精度是s，因此unix时间戳是10位
  • python默认精度是us，因此unix时间戳是16位
1.2 MySQL时间戳（timestamp）

可以插入两种形式的时间戳，本质上都是“年月日时分秒”字符串

mysql> create table machine.test2(id int,create_time timestamp);
mysql> insert into machine.test2 values(1,'20211110000000');
mysql> insert into machine.test2 values(2,'2021-11-10 00:00:00');
mysql> select * from machine.test2;
+------+---------------------+
| id   | create_time         |
+------+---------------------+
|    1 | 2021-11-10 00:00:00 |
|    2 | 2021-11-10 00:00:00 |
+------+---------------------+

输出的格式为“2021-11-10 00:00:00”

2 MySQL中timestamp和datetime的不同点

timestampdatetime
输入输出把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其转化为客户端当前时区进行返回不做任何改变，基本上是原样输入和输出
存储4字节8字节
MySQL官方文档——时间范围‘1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’‘1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’

验证改变时区对timestamp和datetime的影响

检查当前mysql服务器的时区。其中，CST是中国标准时间（China Standard Time）的缩写，指的是MySQL服务器所在的时区，也就是 UTC+8:00

经过简单测试发现：timestamp类型的字段修改时区后会改变输出时间；datetime则不会

  • timestamp
mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+

mysql> create table machine.test3(id int,create_time timestamp);
mysql> insert into machine.test3 values(1,'20211110000000');
mysql> select * from machine.test3;
+------+---------------------+
| id   | create_time         |
+------+---------------------+
|    1 | 2021-11-10 00:00:00 |
+------+---------------------+

mysql> set time_zone='+0:00';
mysql> select * from machine.test3;
+------+---------------------+
| id   | create_time         |
+------+---------------------+
|    1 | 2021-11-09 16:00:00 |
+------+---------------------+
  • datetime
mysql> create table machine.test4(id int,create_time datetime);
mysql> insert into machine.test4 values(1,'20211110000000');
mysql> select * from machine.test4;
+------+---------------------+
| id   | create_time         |
+------+---------------------+
|    1 | 2021-11-10 00:00:00 |
+------+---------------------+

mysql> set time_zone='+0:00';
mysql> select * from machine.test4;
+------+---------------------+
| id   | create_time         |
+------+---------------------+
|    1 | 2021-11-10 00:00:00 |
+------+---------------------+

3 MySQL中关于时间类型的取值范围

官方文档关于时间类型的取值范围说明：
https://dev.mysql.com/doc/refman/8.0/en/datetime.html

3.1 检查MySQL中datetime的时间取值范围

官方文档上的时间范围是'1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999'，

但是实际可取到的范围是'0000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999’，

不可取'0000-00-00 00:00:00'

mysql> create table machine.test(id int,recover_time datetime);
mysql> insert into machine.test values(1, '1000-01-01 00:00:00');
mysql> insert into machine.test values(2, '0000-01-01 00:00:00');
mysql> insert into machine.test values(3, '0999-01-01 00:00:00');
mysql> insert into machine.test values(4, '9999-12-31 23:59:59');
mysql> select * from machine.test;
+------+---------------------+
| id   | recover_time        |
+------+---------------------+
|    1 | 1000-01-01 00:00:00 |
|    2 | 0000-01-01 00:00:00 |
|    3 | 0999-01-01 00:00:00 |
|    4 | 9999-12-31 23:59:59 |
+------+---------------------+


mysql> insert into machine.test values(5, '0000-00-00 00:00:00');
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'recover_time' at row 1
3.2 检查MySQL中timestamp的时间取值范围

官方文档上的时间范围是'1970-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.999999'

但因为我们所在的时区是CST东八区，因此起始时间都比官方文档上的要早8个小时，

也就是'1970-01-01 08:00:01.000000' 到 '2038-01-19 11:14:07.999999'

不可取临界值'1970-01-01 08:00:00'和'2038-01-19 11:14:08'

mysql> create table machine.test1(id int,create_time timestamp);
mysql> insert into machine.test1 values(1, '1970-01-01 08:00:01');
mysql> insert into machine.test1 values(2, '2038-01-19 11:14:07');
mysql> select * from machine.test1;
+------+---------------------+
| id   | create_time         |
+------+---------------------+
|    1 | 1970-01-01 08:00:01 |
|    2 | 2038-01-19 11:14:07 |
+------+---------------------+


mysql> insert into machine.test1 values(3, '1970-01-01 08:00:00');
ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 08:00:00' for column 'create_time' at row 1
mysql> insert into machine.test1 values(4, '2038-01-19 11:14:08');
ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 11:14:08' for column 'create_time' at row 1

4 NULL值和空串问题

NULL也就是在字段中存储NULL值，空值也就是字段中存储空字符(’’)

4.1 占用空间大小
mysql>  select length(NULL), length('');
+--------------+------------+
| length(NULL) | length('') |
+--------------+------------+
|         NULL |          0 |
+--------------+------------+

空值(’’)的长度是0，是不占用空间的；而NULL的长度是NULL，它是占用空间的，NULL需要额外空间来记录它们的值是否为NULL

官方文档关于NULL值的说明：
https://dev.mysql.com/doc/refman/8.0/en/static-format.html

4.2 MySQL中的时间类型是否可以插入NULL值/空串

datetime可以插入NULL值，不可以插入空串(’’)

mysql> insert into machine.test values(6, NULL);
mysql> select * from machine.test;
+------+---------------------+
| id   | recover_time        |
+------+---------------------+
|    1 | 1000-01-01 00:00:00 |
|    2 | 0000-01-01 00:00:00 |
|    3 | 0999-01-01 00:00:00 |
|    4 | 9999-12-31 23:59:59 |
|    6 | NULL                |
+------+---------------------+


mysql> insert into machine.test values(7, '');
ERROR 1292 (22007): Incorrect datetime value: '' for column 'recover_time' at row 1

timestamp可以插入NULL值，不可以插入空串(’’)

mysql> insert into machine.test1 values(5, NULL);
mysql> select * from machine.test1;
+------+---------------------+
| id   | create_time         |
+------+---------------------+
|    1 | 1970-01-01 08:00:01 |
|    2 | 2038-01-19 11:14:07 |
|    5 | NULL                |
+------+---------------------+


mysql> insert into machine.test1 values(6, '');
ERROR 1292 (22007): Incorrect datetime value: '' for column 'create_time' at row 1

MySQL的配置文件sql_mode中，配置了NO_ZERO_DATE / NO_ZERO_IN_DATE的时候，往datetime/timestamp里写空串会报错。检查当前MySQL的配置

mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+

如果需要插入空串(’’)，需要删除NO_ZERO_DATE、NO_ZERO_IN_DATE，并重启MySQL

土豆面包
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
关于MySQL 的时间相加天数和时间作差的SQL
yang_best的专栏
01-14 1421
今天写了一条相对比较应用比较丰富的Sql语句，特别在这里总结一下关于时间作差和相加、case when等开发常用的用法。 SELECT mri.packId,mri.mailId,0,0,0,1, (CASE WHEN mi.productId=2 THEN IF(TIMESTAMPDIFF(SE
MySQL 时间戳类型
阿星君
06-11 4862
日期类型是我们在数据库操作中一个较为常见的数据类型，TIMESTAMP 类型相信使用的朋友也不少，但是你真的了解它吗？ 本文介绍在 MySQL 中使用 TIMESTAMP 类型遇到的一些潜在问题，最大时间限制相当于埋在未来的坑、因为系统的一些默认规则触发日期自动更新、默认系统时区的性能问题，发现问题的同时，后面也推荐了一些在日期上个人认为不错的方案，供参考。 安装 MySQL 推荐 Docker 的方式本机安装一个 MySQL，步骤也很简单，如下所示，对于学习还是很方便的，已安装的可忽略。 $ d
mysql 日期时间函数
一路向前
03-30 1058
日期时间函数中，获取当前时间的NOW()是首当其冲；其次把时间格式化，变成字符串DATE_FORMAT()函数和把字符串时间变成时间的函数STR_TO_DATE()这两个是高频使用。
MySQL中的时间比较日期转换函数
dangjunqiang的博客
02-20 1309
1  无参数调用：UNIX_TIMESTAMP() 、DATE() 返回值：自'1970-01-01 00:00:00'的到当前时间的秒数差 例子：SELECT UNIX_TIMESTAMP()  - 1339123415 2  有参数调用：UNIX_TIMESTAMP(date) 、DATE(date) 其中date可以是一个DATE字符串，一个DATETIME字符串，一
MySQL时间函数 | 时间戳和日期之间得转换
jonssonyan
12-22 1520
十位时间戳转为固定格式(yyyy-MM-dd HH:mm:ss)日期 格式规定 %M 月名字(January……December) %W 星期名字(Sunday……Saturday) %D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。） %Y 年, 数字, 4 位 %y 年, 数字, 2 位 %a 缩写的星期名字(Sun……Sat) %d 月份中的天数, 数字(00……31) %e 月份中的天数, 数字(0……31) %m 月, 数字(01……12) %c 月, 数字(1……12) %b 缩
Mysql UNIX_TIMESTAMP()
七七的博客
07-16 1453
1、UNIX_TIMESTAMP() 返回值：当前时间的UNIX格式数字串，或者说是 UNIX 时间戳（从 UTC 时间’1970-01-01 00:00:00’开始的秒数），通常为十位。 SELECT UNIX_TIMESTAMP(); 2、UNIX_TIMESTAMP( date ) 参数：date 可能是个 DATE 字符串，DATETIME 字符串，TIMESTAPE 字符串，或者是一个类似于 YYMMDD 或者 YYYYMMDD 的数字串。 返回：从 UTC 时间’1970-01-01 00:
关于MySQL UNIX socket连接
m0_60477996的博客
06-23 1460
即 mysql -u root -P 5508 -h localhost -p 等效 mysql -uroot -ptest，其中socket是走的缺省值/tmp/mysql.sock，因此，如果你修改了MySQL监听的socket的目录或者是手动将/tmp/mysql.sock文件删除，那么此时采用此种方式连接将会失败。从上面的实践可知，登录MySQL时，如果明确指定的是ip：port登录那么就会走网络，如果指定localhost或者不指定host或者socket，那么就通过socket连接。
FROM_UNIXTIME 格式化MYSQL时间戳函数
12-15
函数：FROM_UNIXTIME作用：将MYSQL中以INT(11)存储的时间以”YYYY-MM-DD”格式来显示。语法：FROM_UNIXTIME(unix_timestamp,format) 返回表示 Unix 时间标记的一个字符串，根据format字符串格式化。format可以包含与...
MySQL中日期和时间戳互相转换的函数和方法
01-19
① 时间戳转换成日期 代码如下: ...② 把日期转换为时间戳，和 FROM_UNIXTIME 正好相反 代码如下: UNIX_TIMESTAMP 例如： 代码如下: SELECT UNIX_TIMESTAMP(‘2015-04-29’) 执行结果： PS：这里
python正常时间和unix时间戳相互转换的方法
01-21
本文实例讲述了python正常时间和unix时间戳相互转换的方法。分享给大家供大家参考。具体分析如下： 这段代码可以用来转换常规时间格式为unix时间戳，也可以将unix时间戳转换回来， 例如：1332888820 格式转换成 ...
PHP+Mysql日期时间如何转换(UNIX时间戳和格式化日期)
10-28
在IT行业中，尤其是在Web开发领域，PHP和MySQL是常见的编程语言和数据库系统，它们在处理日期和时间数据时，经常需要将日期时间转换为UNIX时间戳或者从UNIX时间戳转换回日期时间。UNIX时间戳是从1970年1月1日（UTC/...
mysql unix时间戳_mysql-关于Unix时间戳（unix_timestamp）
weixin_39969060的博客
01-18 1760
unix_timestamp时间戳是自 1970 年 1 月 1 日(00:00:00 GMT)以来的秒数。它也被称为 Unix 时间戳(Unix Timestamp)。Unix时间戳(Unix timestamp)，或称Unix时间(Unix time)、POSIX时间(POSIX time)，是一种时间表示方式，定义为从格林威治时间1970年01月01日00时00分00秒起至现在的总秒数。Un...
MySQL UNIX_TIMESTAMP 函数：获取 UNIX 时间戳
04-26 1301
UNIX_TIMESTAMP(date) 若无参数调用，返回一个无符号整数类型的 UNIX 时间戳（'1970-01-01 00:00:00'GMT之后的秒数）。若用 date 来调用 UNIX_TIMESTAMP()，它会将参数值以'1970-01-01 00:00:00'GMT后的秒数的形式返回。【实例】使用 UNIX_TIMESTAMP() 函数返回 UNIX 格式的时间戳，输入 SQL 语句和执行结果如下。
Mysql日期和时间函数大全
放飞我心
01-08 1004
原文 对于每个类型拥有的值范围以及并且指定日期何时间值的有效格式的描述见7.3.6 日期和时间类型。  这里是一个使用日期函数的例子。下面的查询选择了所有记录，其date_col的值是在最后30天以内：  mysql> SELECT something FROM table  WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) select DAY
mysql unix格式_mysql将unix时间戳转为日期格式,MySQL查询当前时间
weixin_39662594的博客
02-07 735
介绍两个小常识，mysql如何将时间戳转成常用时间格式和MySql如何查询数据库当前时间。(1)MySql查询数据库当前时间可使用命令：select now();//返回格式为：2012-06-23 11:44:42(2)mysql将unix时间戳转成常用时间格式时间字段存放在数据库中一般的存储类型是int类型，当我们要看看当前数据的具体时间的时候该怎么办呢？视觉上我们只能看到一堆堆的数字，如果需...
【MySQL】MySQL在Linux/UNIX和 Windows上的安装，验证安装和登录 MySQL的详细讲解
2201_75366661的博客
06-30 4603
【MySQL】MySQL在Linux/UNIX和 Windows上的安装，验证安装和登录 MySQL的详细讲解
MySQL使用FROM_UNIXTIME将时间戳格式化输出显示
龚清林的博客
06-13 8669
MySQL使用FROM_UNIXTIME将时间戳格式化输出显示
MySQL Buffer Pool
Mazai-Liu的博客
07-08 1097
LRU链表分为冷、热数据区域，前63%为热数据区域，后37%为冷数据区域，加载缓存页先放到冷数据区域头部。冷数据区域的缓存页第一次访问超过1秒后，再次访问时才会被移动到热数据区域头部。热数据区域中，只有后3/4的缓存页被访问才会移到头部，前1/4被访问到不会移动。淘汰数据优先淘汰冷数据区域尾部的缓存页。
mysql之比较两个表的数据
最新发布
hebtu666
07-08 503
要比较两个结构相同的表中的某条数据是否完全一样，您可以使用JOIN语句或者EXISTS关键字来对比这两个表中相应的行。
mysql 时间戳转时间
09-09
### 回答1： MySQL 中的时间戳是指从1970年1月1日00:00:00 UTC（协调世界时）开始经过的秒数，通常也被称为 Unix 时间戳。如果你想将时间戳转换为 MySQL 中的日期时间格式，可以使用 FROM_UNIXTIME() 函数。这个函数接受一个时间戳参数，并将其转换为 MySQL 中的日期时间格式。 例如，假设你有一个名为 `mytable` 的表，其中包含一个名为 `timestamp` 的时间戳列，你可以使用以下 SQL 语句将时间戳转换为日期时间格式： ``` SELECT FROM_UNIXTIME(timestamp) FROM mytable; ``` 这将返回一个包含时间戳转换后日期时间值的结果集。你也可以使用 DATE_FORMAT() 函数将日期时间值格式化为你想要的日期时间字符串格式。例如，以下 SQL 语句将时间戳转换为格式为 "YYYY-MM-DD HH:MI:SS" 的日期时间字符串： ``` SELECT DATE_FORMAT(FROM_UNIXTIME(timestamp), '%Y-%m-%d %H:%i:%s') FROM mytable; ``` 希望这能够帮助你！ ### 回答2： 在MySQL中，可以使用FROM_UNIXTIME函数将UNIX时间戳转换为对应的日期时间。 UNIX时间戳是从1970年1月1日00:00:00以来经过的秒数。要将时间戳转换为日期时间，可以使用以下语法： ```sql SELECT FROM_UNIXTIME(timestamp) ``` 其中，timestamp是要转换的时间戳。FROM_UNIXTIME函数将返回一个格式为YYYY-MM-DD HH:MM:SS的日期时间字符串。 以下是一个例子： 假设有一个名为orders的表，其中包含一个名为created_time的列，它存储了订单创建的UNIX时间戳。 表结构如下： ```sql CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, created_time INT ); ``` 现在，我们要将created_time列的时间戳转换为日期时间并进行查询，可以使用以下语句： ```sql SELECT id, FROM_UNIXTIME(created_time) AS creation_date FROM orders; ``` 运行该查询后，将以以下格式显示结果： ``` id | creation_date ------+------------------- 1 | 2022-01-01 09:30:00 2 | 2022-01-02 14:45:30 3 | 2022-01-03 18:20:15 ``` 通过使用FROM_UNIXTIME函数，我们可以轻松地将UNIX时间戳转换为易于理解的日期时间格式。 ### 回答3： 在MySQL中，时间戳转换为时间可以使用FROM_UNIXTIME()函数。该函数将Unix时间戳（以秒为单位）转换为MySQL的日期和时间格式。 语法如下： FROM_UNIXTIME(unix_timestamp) 其中，unix_timestamp为Unix时间戳。 举例说明： 假设存在一个名为timestamp_table的表，其中有一个名为timestamp_column的列存储了时间戳数据。 要将时间戳转换为时间，可以使用以下SQL查询语句： SELECT FROM_UNIXTIME(timestamp_column) FROM timestamp_table; 这将返回一个包含转换后时间的结果集。如果还需要指定日期和时间的格式，则可以使用DATE_FORMAT()函数对结果进行格式化。 例如，如果要将时间格式化为YYYY-MM-DD HH:MM:SS的形式，可以使用以下查询： SELECT DATE_FORMAT(FROM_UNIXTIME(timestamp_column), '%Y-%m-%d %H:%i:%s') FROM timestamp_table; 这样会返回按给定格式格式化后的时间。 总之，在MySQL中，使用FROM_UNIXTIME()函数可以将时间戳转换为日期和时间格式，并可以选择进行格式化以满足具体需求。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值