MySQL 时间类型精确到毫秒、微秒及其处理

最新推荐文章于 2024-08-18 03:53:30 发布
最新推荐文章于 2024-08-18 03:53:30 发布
阅读量1.8w 收藏 24
点赞数 8
分类专栏： MySQL 文章标签： 时间戳
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-NC-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42018518/article/details/102629599
版权

一、MySQL 中日期和时间类型

类型大小（字节）范围格式用途默认值设置
DATE31000-01-01/9999-12-31YYYY-MM-DD日期值 
TIME3'-838:59:59'/'838:59:59'HH:MM:SS时间值或持续时间 
YEAR11901/2155YYYY年份值 
DATETIME81000-01-01 00:00:00/9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值 
TIMESTAMP4

1970-01-01 00:00:00/2038

结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07

YYYYMMDD HHMMSS混合日期和时间值，时间戳

设置默认时间：CURRENT_TIMESTAMP

设置更新时间：ON UPDATE CURRENT_TIMESTAMP

官方介绍：

二、MySQL5.6.4 以后时间类型（DATETIME、TIMESTAMP、TIME）支持微秒

精确级别类型范围默认值设置
微秒DATETIME(6) '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999' 
TIMESTAMP(6) '1970-01-01 00:00:01.000000' to'2038-01-19 03:14:07.999999'

设置默认时间：CURRENT_TIMESTAMP(6)

设置更新时间：ON UPDATE CURRENT_TIMESTAMP(6)

TIME(6)  ‘-838:59:59.000000’ to '838:59:59.000000' 
毫秒DATETIME(3) '1000-01-01 00:00:00.000' to '9999-12-31 23:59:59.999' 
TIMESTAMP(3)'1970-01-01 00:00:01.000' to'2038-01-19 03:14:07.999'

设置默认时间：CURRENT_TIMESTAMP(3)

设置更新时间：ON UPDATE CURRENT_TIMESTAMP(3)

TIME(3)‘-838:59:59.000’ to '838:59:59.000' 

三、举个栗子：MySQL 将时间存储为 数值数据类型（以毫秒为例，微秒类似）

1、把问题放在最前，注意在例子中观察(⊙o⊙)哦

  • 1> 正常存入秒级别时间的数值数据类型到 MySQL 时 int 就够用（MySQL 是支持无符号的 int，而 mycat 只支持有符号的 int，但是有符号 int 最大为2147483647，即可保存时间到2038-01-19 11:14:07，所有暂时可以把时间保存为 int 类型，完全够用）。
  • 2> 当存入毫秒级 13 位（微秒级 16 位）时间的数值数据类型时需要用到 BIGINT，业务代码在进行日期转换为毫秒级的时间戳后需要*1000（*1000000）变为13 位（16 位）的数值数据类型再与数据库中的值进行比较筛选。
  • 3> 当存入毫秒级 13 位（微秒级 16 位）时间的数值数据类型时（如毫秒级：1571302437373），后三位（后六位）其实是毫秒（微秒），即原值（去小数点）*1000（*1000000）所得，所以不能直接转换，需要需要把 13 位（16 位）数值（加小数点）先➗1000（➗1000000）再进行转换，或者也可直接截取前十位先转换为正常日期再加上后几位的毫秒或微秒，即转换后时间格式可为 2019-10-17 16:53:57.373，当然你也可以自定义格式。需要注意的一点是，条件筛选时像筛选 2019-10-17 一天的范围就得写为：between 2019-10-17 00:00:00.000 and 2019-10-17 23:59:59.999，当然像 '>'、'<' 等时类似。
  • 4> 当使用数值数据类型存储时间是就不能使用像 TIMESTAMP 类型的设置默认时间和设置默认更新时间，需要代码写入 CURRENT_TIMESTAMP，具体方法下面例子中有演示。
  • 5> 为什么使用数值数据类型存储时间❓因为索引体量更小， B+树会更加矮胖（什么是 B+树❓）；使用数值数据类型存储时间，想转换怎样格式的时间都可以，而且不受时区的约束。

2、建表语句

CREATE TABLE `t_im_message` (
  `msg_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `msg` varchar(10) NOT NULL COMMENT '消息内容',
  `ctime` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT 'TIMESTAMP类型创建时间',
  `ctime_int` bigint(15) NOT NULL COMMENT '数值数据类型创建时间',
  `mtime` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT 'TIMESTAMP类型修改时间',
  `mtime_int` bigint(15) NOT NULL COMMENT '数值数据类型修改时间',
  PRIMARY KEY (`msg_id`),
  KEY `idx_ctime` (`ctime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='测试表';

3、插入测试数据

MySQL [wufei_test]> insert into t_im_message(msg,ctime_int,mtime_int) values('测试数据 01',REPLACE(unix_timestamp(current_timestamp(3)),'.',''),REPLACE(unix_timestamp(current_timestamp(3)),'.',''));
Query OK, 1 row affected (0.01 sec)
 
MySQL [wufei_test]> insert into t_im_message(msg,ctime_int,mtime_int) values('测试数据 02',REPLACE(unix_timestamp(current_timestamp(3)),'.',''),REPLACE(unix_timestamp(current_timestamp(3)),'.',''));
Query OK, 1 row affected (0.00 sec)
 
MySQL [wufei_test]> select * from t_im_message;
+--------+-----------------+-------------------------+---------------+-------------------------+---------------+
| msg_id | msg             | ctime                   | ctime_int     | mtime                   | mtime_int     |
+--------+-----------------+-------------------------+---------------+-------------------------+---------------+
|      1 | 测试数据 01     | 2019-10-17 16:50:32.987 | 1571302232987 | 2019-10-17 16:50:32.987 | 1571302232987 |
|      2 | 测试数据 02     | 2019-10-17 16:50:44.063 | 1571302244063 | 2019-10-17 16:50:44.063 | 1571302244063 |
+--------+-----------------+-------------------------+---------------+-------------------------+---------------+
2 rows in set (0.00 sec)

4、更新数据

MySQL [wufei_test]> update t_im_message set msg='更新测试数据 02',mtime_int=REPLACE(unix_timestamp(current_timestamp(3)),'.','') where msg_id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
MySQL [wufei_test]> select * from t_im_message;
+--------+-----------------------+-------------------------+---------------+-------------------------+---------------+
| msg_id | msg                   | ctime                   | ctime_int     | mtime                   | mtime_int     |
+--------+-----------------------+-------------------------+---------------+-------------------------+---------------+
|      1 | 测试数据 01           | 2019-10-17 16:50:32.987 | 1571302232987 | 2019-10-17 16:50:32.987 | 1571302232987 |
|      2 | 更新测试数据 02       | 2019-10-17 16:50:44.063 | 1571302244063 | 2019-10-17 16:53:57.373 | 1571302437373 |
+--------+-----------------------+-------------------------+---------------+-------------------------+---------------+
2 rows in set (0.00 sec)

5、数据校验

MySQL [wufei_test]> select * from t_im_message;
+--------+-----------------------+-------------------------+---------------+-------------------------+---------------+
| msg_id | msg                   | ctime                   | ctime_int     | mtime                   | mtime_int     |
+--------+-----------------------+-------------------------+---------------+-------------------------+---------------+
|      1 | 测试数据 01           | 2019-10-17 16:50:32.987 | 1571302232987 | 2019-10-17 16:50:32.987 | 1571302232987 |
|      2 | 更新测试数据 02       | 2019-10-17 16:50:44.063 | 1571302244063 | 2019-10-17 16:53:57.373 | 1571302437373 |
+--------+-----------------------+-------------------------+---------------+-------------------------+---------------+
2 rows in set (0.00 sec)
 
MySQL [wufei_test]> select REPLACE(unix_timestamp('2019-10-17 16:53:57.373'),'.','');
+-----------------------------------------------------------+
| REPLACE(unix_timestamp('2019-10-17 16:53:57.373'),'.','') |
+-----------------------------------------------------------+
| 1571302437373                                             |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

 

 

if 0 = -I can
关注
专栏目录
mysql 精确毫秒_mysql 精确到毫秒
weixin_39949413的博客
01-19 902
现在很多团队在做去o的项目，迁移mysql时，为了便于binlog分析，请务必对每张表都增加ts字段。条件：mysql5.6My.cnf中修改参数explicit_defaults_for_timestamp = 0且该字段需要精确到毫秒，而now()函数是不支持毫秒的。且建表时，只设置ts字段为timstamp类型，其他时间字段请用datetime类型，mysql只允许第一个timestamp类...
MySQL 日期时间类型精确到毫秒
m0_55070913的博客
08-02 2575
先自我介绍一下，小编13年上师交大毕业，曾经在小公司待过，去过华为OPPO等大厂，18年进入阿里，直到现在。深知大多数初中级java工程师，想要升技能，往往是需要自己摸索成长或是报班学习，但对于培训机构动则近万元的学费，着实压力不小。意思就是，毫秒部分需要以参数形式传参给数据类型，默认是不保存毫秒的，可以保存1-6位。如果需要保存三位的毫秒值，数据类型可以定义为DATETIME(3)或TIMESTAMP(3)，不需要保存毫秒的话，只需要将类型直接写为DATETIMETIMESTAMP。...
sql将毫秒数字转换为日期
jrn1012的专栏
05-14 2万+
sql将毫秒数字转换为日期 例如某个字段的毫秒数为：1345626941701 转换函数：select dateadd(s, 1345626941701/1000 ,'1970-1-1 08:00:00') 即用 毫秒数/1000 即可。 对于字段：col/1000再用上面的函数即可 对于oracle麻烦点： select to_date('1970-01-01 08:00
datetime毫秒 mysql
最新发布
weixin_40562428的博客
08-18 83
我整理的一些关于【数据】的项目学习资料（附讲解～～）和大家一起分享、学习一下：https://d.51cto.com/eDOcp1MySQL中的毫秒时间戳处理 在现代数据库设计中，时间的管理是一个至关重要的部分。MySQL作为一种流行的数据库管理系统，常常需要处理包括毫秒在内的时间戳数据。在这篇文章中，我们将探讨如何在M...
mysql时间精确到毫秒
u012799495的博客
12-25 7114
mysql时间精确到毫秒 sql建表语句： CREATE TABLE test ( id int(11) NOT NULL AUTO_INCREMENT, created_time datetime(3) DEFAULT CURRENT_TIMESTAMP(3) COMMENT ‘创建时间’, updated_time datetime(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), PRIMARY KEY (`id``) )
mysql的datetime和timestamp精确到毫秒
vicroad2014的专栏
09-11 2689
alter table d_patient add updatedTime1 TIMESTAMP(3) not NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3); alter table d_patient MODIFY  update_time TIMESTAMP(3) not NULL DEFAULT CURR...
如何选择合适的MySQL日期时间类型来存储你的时间
09-08
在MySQL中，选择合适的日期时间类型对于数据存储和查询至关重要。日期时间类型主要有三种：`datetime`、`timestamp`和`UNIX timestamp`，每种都有其特定的应用场景和优缺点。 1. `datetime` 类型： - `datetime` ...
关于MySQL的时间进位问题浅析
09-09
2. **在插入数据前处理时间值**：如果你的数据库设计不支持高精度时间类型，可以在存储数据前将时间值的毫秒或微秒部分截断。例如，你可以选择只保留整秒和毫秒，丢弃微秒部分。 理解MySQL的时间进位机制对于处理...
关于MySQL 时间类型 datetime、bigint、timestamp，你用哪个？
07-27
### MySQL时间类型详解：datetime、bigint、timestamp 在MySQL中，根据不同的应用场景和需求，可以选择多种方式来存储日期和时间信息。本篇文章将详细介绍三种常用的时间类型：`datetime`、`bigint` 和 `timestamp`...
mysql 精确到毫秒
chuiwufen5531的博客
07-14 400
现在很多团队在做去o的项目，迁移mysql时，为了便于binlog分析，请务必对每张表都增加ts字段。 条件：mysql5.6 My.cnf中修改参数 explicit_defaults_for_timestamp = 0 且该字段需要精确到毫秒，而now()函数是不支持毫秒的。...
MySQL：日期&时间字段如何精确到毫秒
netyeaxi的专栏
05-13 1万+
MySQL日期&时间字段如何精确到毫秒
[转]C语言如何获得精确到毫秒的时间
weixin_30412577的博客
05-10 339
转自：http://blog.jeoygin.org/2012/03/c-get-time-millisecond.html 在做测试或性能优化时，经常要知道程序运行的时间，在Linux系统可以使用time命令来计算程序运行运行所消耗的时间，能精确到毫秒，如果要精 确到代码块或某个操作运行时所消耗的时间，time命令就不给力了。如果对时间的精度要求不高的话，可以调用标准C的接口time来得到开始...
mysql 查询当前时间到毫秒_MySQL 关于毫秒和微秒的处理，MySQL获取毫秒！
weixin_39563722的博客
12-21 1187
很多从ORACLE 或者 SQL SERVER 移植到 MySQL 的朋友都遇到一个相同的问题，就是关于毫秒的存储与显示。 由于MySQL的只提供了DATETIME， TIMESTAMP， TIME， DATE， YEAR这几种时间类型，而且DATETIME 以及TIMESTAMP 的最小单位是秒，没有存储毫秒级别的函数。 不过MySQL 却能识别时间中的毫秒部分。而且我们有多种方式可以获得毫秒的...
MYSQL DATETIME类型精确到毫秒
fffjvjxrx的博客
03-18 2057
年年年年-月月-日日 时时:分分:秒秒.毫秒...年年年年月月日日时时分分秒秒毫秒...#设置精确到毫秒三位 设置默认值为函数NOW(3)精确到毫秒三位。
Mysql 处理毫秒级时间戳TimeStamp数据类型
Lipper Z 的专栏
03-30 2377
Mysql 时间戳TimeStamp数据类型默认是不带毫秒部分的，需要的时候首先要在字段的长度上加上毫秒即 timestamp （3）字段长度是3，所以自动截断成保留前3位，当然也可以定义成长度5，这样尽可能保留数据的最高精度！注意：这里时间戳虽然是5位小数，但是。
MySQL获取时间戳、毫秒数
热门推荐
cleancp的博客
09-20 3万+
current_timestamp(3) 2018-09-20 17:56:39.916 unix_timestamp(current_timestamp(3)) 1537437399.934 select REPLACE(unix_timestamp(current_timestamp(3)),'.',''); 1537437399953
python 获取当前时间精确到毫秒
07-27
要获取当前时间精确到毫秒，你可以使用 `datetime` 模块和 `strftime` 方法来实现。下面是一个示例代码： ```python from datetime import datetime now = datetime.now() milliseconds = now.strftime("%Y-%m-%d %H:%M:%S.%f")[:-3] print(milliseconds) ``` 以上代码会输出当前时间的毫秒部分。请注意，这里使用了 `strftime` 方法来格式化时间，`%f` 表示毫秒。`[:-3]` 是为了去掉最后的三位小数，因为 Python 的 `datetime` 对象的毫秒精度只能到微秒级别。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值