在使用new Date生成时间戳时，发现数据库中的时间总是多出一秒钟。

2024-10-26 12:38:52

项目汇报的时候，进一步研究问题

insert into t_tax_file(task_id, task_no, business_type, file_name, file_url, creator_id, created_time, modifier_id,
                       modified_time)
    value (10, 'taskNo测试', 1, '文件名称', '文件地址', 1, '2024-10-21 10:25:21.889', 1, '2024-10-21 10:25:21.449');
-- 方法二：使用 DATE_FORMAT 和 CONCAT 函数
SELECT  created_time,
       modified_time
FROM t_tax_file where task_no='taskNo测试';

差个一秒钟

表结构

create table t_tax_file
(
    id            bigint unsigned auto_increment comment '主键'
        primary key,
    task_id       bigint                                    null comment '任务id',
    task_no       varchar(64)                               null comment '任务编号',
    business_type tinyint(1)                                null comment '业务类型',
    file_name     varchar(256)                              null comment '文件名称',
    file_url      varchar(500)                              null comment '文件url',
    creator_id    bigint unsigned default '0'               null comment '创建者id.对应员工id.',
    created_time  timestamp       default CURRENT_TIMESTAMP null comment '创建时间',
    modifier_id   bigint unsigned default '0'               null comment '修改id.对应员工id.',
    modified_time timestamp       default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP comment '最后修改时间'
)
    comment '纳税申报-附件表';

mysql 5.6 之后的版本，timestamp 支持了微秒数。timestamp 和datetime区别

	TIMESTAMP	datetime
两者的存储方式不一样	对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。	而对于DATETIME，不做任何改变，基本上是原样输入和输出。
存储范围	timestamp，使用4个字节，所能存储的时间范围为：‘1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’。	datetime使用8个字节，所能存储的时间范围为：‘1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’。
注意点	MySQL中的`TIMESTAMP`数据类型用于存储日期和时间，支持小数秒精度，可以精确到微秒（即小数点后6位）。如果你在插入或更新`TIMESTAMP`列时没有指定小数部分，则默认精度是秒级，也就是不包含小数部分。

总结：TIMESTAMP和DATETIME除了存储范围和存储方式不一样，没有太大区别。当然，对于跨时区的业务，TIMESTAMP更为合适。

当指定了小数位数之后。

数据就完全正确了

测试其他数据类型

-- 创建表
-- 测试datetime的精度
CREATE TABLE test(
    -- 默认精度为0
    -- Maximum is 6.
                     datetime1 DATETIME,
                     datetime2 DATETIME(3),
                     datetime3 DATETIME(5)
);
INSERT INTO test VALUES("2020-11-22 12:01:01.59999", "2020-11-22 12:01:01.59999", "2020-11-22 12:01:01.59999");
INSERT INTO test VALUES("2020-11-22 12:01:01.5", "2020-11-22 12:01:01.599", "2020-11-22 12:01:01.59999");
INSERT INTO test VALUES("2020-11-22 12:01:01.4", "2020-11-22 12:01:01.594", "2020-11-22 12:01:01.59994");
INSERT INTO test VALUES("2020-11-22 12:01:01.5", "2020-11-22 12:01:01.5995", "2020-11-22 12:01:01.599995");
INSERT INTO test VALUES("2020-11-22 12:01:01.5", "2020-11-22 12:01:01.5994", "2020-11-22 12:01:01.599994");

-- 创建表
-- 测试timestamp的精度
CREATE TABLE test11(
    -- 默认精度为0
    -- Maximum is 6.
    datetime1 TIMESTAMP,
    datetime2 TIMESTAMP(3),
    datetime3 TIMESTAMP(5)
);
INSERT INTO test11 VALUES("2020-11-22 12:01:01.59999", "2020-11-22 12:01:01.59999", "2020-11-22 12:01:01.59999");
INSERT INTO test11 VALUES("2020-11-22 12:01:01.5", "2020-11-22 12:01:01.599", "2020-11-22 12:01:01.59999");
INSERT INTO test11 VALUES("2020-11-22 12:01:01.4", "2020-11-22 12:01:01.594", "2020-11-22 12:01:01.59994");
INSERT INTO test11 VALUES("2020-11-22 12:01:01.5", "2020-11-22 12:01:01.5995", "2020-11-22 12:01:01.599995");
INSERT INTO test11 VALUES("2020-11-22 12:01:01.5", "2020-11-22 12:01:01.5994", "2020-11-22 12:01:01.599994");

总结

类型	所占字节	格式	范围
TIMESTAMP	4字节	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01utc到2038-01-19 03:14:07utc
DATETIME	5字节	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00到9999-12-31 23:59:59
DATE	3字节	YYYY-MM-DD	1000-01-01到9999-12-31
TIME	3字节	HH:MM:SS	-838:59:59到838:59:59
YEAR	1字节	YYYY	1901到2155

注：MySQL 5.6.4 之前，占 8 个字节，之后版本，占 5 个字节。

其他特性

TIMESTAMP是以utc格式存储，会自动检索当前时区对时间进行转换，而DATETIME不会。
存入null时，TIMESTAMP会自动存储当前时间，而DATETIME存储null值。
时间计算：

DATETIME翻译为汉语即"时间戳"，它是当前时间到 Unix元年(1970 年 1 月 1 日 0 时 0 分 0 秒)的秒数。对于某些时间的计算，如果是以 DATETIME 的形式会比较困难，假如我是 1994-1-20 06:06:06 出生，现在的时间是 2016-10-1 20:04:50 ，那么要计算我活了多少秒钟， DATETIME还需要函数进行转换，但是 TIMESTAMP 直接相减就行。

场景选择

1.需要跨时区计算时间用或者需要自动更新时间的TIMESTAMP

计算一架从北京飞往纽约的飞机的飞行时间。这个场景中，如果使用 TIMESTAMP 来存时间，起飞和降落时间的值，都会被转换成 UTC 时间，所以它们直接相减即可获得结果。但如果使用 DATATIME 格式存时间，还需要进行转换，才可以完成，容易出错。

2.记录创建修改时间或者时间范围大于2038 用DATETIME

DATATIME作为记录时间，现在都已经2022年了，很快就到2038年啦，使用DATATIME不需要担心超过范围。

当然在两者都满足使用的情况下，所占字节越小越好，TIMESTAMP比DATATIME好。

bigint使用(8个字节)

还有一种情况，即不用TIMESTAMP也不用DATATIME，而是用BIGINT。存储自纪元以来的毫秒数（如果使用的是 Java，则用 System.currentTimeMillis() 获取当前时间）

这样有几个优点：

可以在迁移数据库时避免因为数据类型差异。比如MySQL的DATETIME类型和Oracle的DATETIME类型之间可能存在差异，timestamp类型的精度可能也存在差异，MySQL的timestamp精度不是一开始就支持毫秒精度的。
没有时区问题。无论是哪个时区，因为开始计算的时间不同，无论当前时间如何，跨度是一致的。也没有timestamp和datatime的范围问题。是对timestamp的补充。
InnoDB存储引擎下，通过时间范围查找，性能bigint > datetime > timestamp，通过时间排序，性能bigint > timestamp > datetime。综合来讲，bigint性能最好。

思考：

当发现问题，要把原因搞懂，然后进一步挖掘可以用的东西。不能浮于表面。

码农公寓

相关文章