MySQL里面的时间与UNIX时间戳,解决2038年问题的思考

当前时间:NOW()

当前时间:NOW()函数,传入参数是一个整数类型,传入参数可以是:空(0)、1~6;代表时间精度(秒后面的精度)。
SELECT NOW(), NOW(0), NOW(1), NOW(2), NOW(3), NOW(4), NOW(5), NOW(6); 如下:

内置函数 单位 值格式示例 是否常用
NOW() second(s) 2025-01-20 09:47:01
NOW(0) second(s) 2025-01-20 09:47:01
NOW(1) decisecond(ds) 2025-01-20 09:47:01.4
NOW(2) centisesond(cs) 2025-01-20 09:47:01.40
NOW(3) millisecond(ms) 2025-01-20 09:47:01.405
NOW(4) - 2025-01-20 09:47:01.4052
NOW(5) - 2025-01-20 09:47:01.40525
NOW(6) microsecond(μs) 2025-01-20 09:47:01.405254

传入参数最大值是6,如果大于6会报错,错误代码: 1426

1 queries executed, 0 success, 1 errors, 0 warnings

查询:SELECT NOW(7)

错误代码: 1426
Too-big precision 7 specified for 'now'. Maximum is 6.

执行耗时   : 0 sec
传送时间   : 0 sec
总耗时      : 0.008 sec

常用的当前时间有3种:

  • NOW() 精确到秒的日期时间
  • NOW(3) 精确到毫秒(千分之一秒)的日期时间
  • NOW(6) 精确到微秒(千分之一毫秒,10的6次方分之一秒)的日期时间

时间函数比较:

函数名称 返回值类型 包含时间部分 标准SQL 主要数据库系统
CURRENT_DATE DATE 多数数据库系统
CURRENT_DATE() DATE 支持部分数据库系统
CURDATE() DATE MySQL
NOW() DATETIME 多数数据库系统
SYSDATE() DATETIME MySQL、Oracle等

选择使用:

  • 如果只需要日期部分,推荐使用 CURRENT_DATE 或 CURRENT_DATE()。
  • 如果需要日期和时间,使用 NOW() 或 SYSDATE()。
  • 在 MySQL 中,CURDATE() 与 CURRENT_DATE 功能相同,但为了跨数据库兼容性,推荐使用标准的 CURRENT_DATE。

UNIX时间戳

首先要知道当初UNIX时间戳是UNIX系统内核在里面使用的,因为其简单高效的特点逐渐被广泛使用。

unix时间戳:从1970年1月1日开始所经过的秒数。

Unix 时间戳是从1970年1月1日(UTC/GMT的午夜)开始所经过的秒数,不考虑闰秒。
Unix 时间戳(英文为 Unix epoch, Unix time, POSIX time 或 Unix timestamp)
UNIX时间戳的 0 按照 ISO 8601 规范为 :1970-01-01T00:00:00Z.
注意:UNIX时间戳是包含时区的。咱们中国是“东八区”要在时间原点基础上+8小时。所以咱们中国地区的UNIX时间戳是从1970年1月1日 08:00:00开始所经过的秒数。

一个小时表示为UNIX时间戳格式为:3600秒;一天表示为UNIX时间戳为86400秒,闰秒不计算。

时间
1 分钟 60
1 小时 3600
1 天 86400
1 周 604800
1 月 (30.44 天) 2629743
1年 (365.24 天) 31556736
2038年问题

在大多数的 Unix 系统中 Unix 时间戳存储为 32 位,这样会引发 2038 年问题或 Y2038。

MySQL内置函数 UNIX_TIMESTAMP() 与 FROM_UNIXTIME()
  • UNIX_TIMESTAMP(日期时间格式) 用于将 日期时间格式 的参数转换为 UNIX时间戳。
  • FROM_UNIXTIME(UNIX时间戳) 用于将 UNIX时间戳 转换为 日期时间格式。

秒级时间戳:

内置函数 值格式示例 内置函数 值格式示例
UNIX_TIMESTAMP('2025-01-20 09:23:51') 1737336231 FROM_UNIXTIME(1737336231) 2025-01-20 09:23:51
UNIX_TIMESTAMP(NOW()) 1737336231 FROM_UNIXTIME(1737336231) 2025-01-20 09:23:51
(UNIX_TIMESTAMP(NOW())* 1000) 1737336231000 FROM_UNIXTIME(1737336231000 / 1000) 2025-01-20 09:23:51.0000

毫秒级时间戳:

内置函数 值格式示例 内置函数 值格式示例
(UNIX_TIMESTAMP(NOW(3))* 1000) 1737340830990.000 FROM_UNIXTIME(1737340830990 / 1000) 2025-01-20 10:40:30.9900
- - FROM_UNIXTIME(1737340830990.000 / 1000) 2025-01-20 10:40:30.990000
(UNIX_TIMESTAMP(NOW(6))* 1000000) 1737341362745981.000000 FROM_UNIXTIME(1737341362745981 / 1000000) 2025-01-20 10:49:22.7459
- - FROM_UNIXTIME(1737341362745981.000000 / 1000000) 2025-01-20 10:49:22.745981
使用MySQL的BIGINT类型存储毫秒级时间戳

在很多应用中,我们需要存储时间戳来记录事件发生的时间。通常情况下,我们使用int整数类型来存储时间戳,比如UNIX时间戳。但是,在某些应用场景中,需要存储毫秒级的时间戳,此时使用BIGINT类型是一个不错的选择。在MySQL中,BIGINT类型可以存储很大范围的整数,范围从-9223372036854775808到9223372036854775807。这个范围完全可以覆盖毫秒级时间戳的取值范围。
示例:
首先,我们需要创建一个表格来存储时间戳。我们可以使用以下SQL语句创建一个名为 timestamp_table 的表格:

CREATE TABLE timestamp_table(
  id INT PRIMARY KEY AUTO_INCREMENT,
  timestamp_column BIGINT DEFAULT UNIX_TIMESTAMP(NOW(3))* 1000
);

在上面的SQL语句中,我们定义了一个名为 timestamp_column 的BIGINT类型的字段,它的默认值为当前时间的毫秒级时间戳。 UNIX_TIMESTAMP(NOW(3))* 1000 这段代码可以获取当前时间的毫秒级时间戳。

接下来,我们来插入一条数据到 timestamp_table 表格中:

INSERT INTO timestamp_table (timestamp_column) VALUES (UNIX_TIMESTAMP(NOW(3)) * 1000);

这样就可以将当前时间的毫秒级时间戳插入到 timestamp_table 表格中的 timestamp_column 字段中。
如果我们想查看所有的时间戳数据,可以使用以下SQL语句查询:

SELECT * FROM timestamp_table;
SELECT FROM_UNIXTIME(timestamp_column / 1000) AS TIME FROM timestamp_table;
解决“2038年问题”的一种方法

解决“2038年问题”的一种方法是将秒级时间戳也用bigint类型存储。
仍然以上面示例中的timestamp_table 的表格里面timestamp_column 字段为例。我们稍微修改一下创建表格的语句里面的默认值:

CREATE TABLE timestamp_table(
  id INT PRIMARY KEY AUTO_INCREMENT,
  timestamp_column BIGINT DEFAULT UNIX_TIMESTAMP(NOW())
);

在上面的SQL语句中,我们定义了一个名为 timestamp_column 的BIGINT类型的字段,它的默认值为当前时间的秒级时间戳。 UNIX_TIMESTAMP(NOW()) 这段代码可以获取当前时间的秒级时间戳。

来插入和读取秒级UNIX时间戳:

INSERT INTO timestamp_table (timestamp_column) VALUES (UNIX_TIMESTAMP(NOW())); # 插入秒级UNIX时间戳

SELECT FROM_UNIXTIME(timestamp_column) AS TIME FROM timestamp_table; # 读取秒级UNIX时间戳

可以直接用这种方法来验证:SELECT NOW(), UNIX_TIMESTAMP(NOW());

时间计算加、减、格式化等

DATEDIFF(expr1, expr2): 返回两个日期之间的天数。

SELECT DATEDIFF('2023-12-31', '2023-01-01');
返回值:364

DATE_ADD(date, INTERVAL expr unit) 或 ADDDATE(date, expr): 对日期增加时间间隔。

SELECT DATE_ADD(NOW(), INTERVAL 10 YEAR);
返回值:在当前日期时间基础上:加10SELECT DATE_ADD(NOW(), INTERVAL 10 QUARTER);
返回值:在当前日期时间基础上:加10季度
SELECT DATE_ADD(NOW(), INTERVAL 10 MONTH);
返回值:在当前日期时间基础上:加10个月
SELECT DATE_ADD(NOW(), INTERVAL 10 WEEK);
返回值:在当前日期时间基础上:加10个周
SELECT DATE_ADD(NOW(), INTERVAL 10 DAY);
返回值:在当前日期时间基础上:加10SELECT DATE_ADD(NOW(), INTERVAL 10 HOUR);
返回值:在当前日期时间基础上:加10小时
SELECT DATE_ADD(NOW(), INTERVAL 10 MINUTE);
返回值:在当前日期时间基础上:加10分钟
SELECT DATE_ADD(NOW(), INTERVAL 10 SECOND);
返回值:在当前日期时间基础上:加10

DATE_SUB(date, INTERVAL expr unit) 或 SUBDATE(date, expr): 对日期减少时间间隔。

SELECT DATE_SUB(NOW(), INTERVAL 5 YEAR);
返回值:在当前日期时间基础上:减5SELECT DATE_SUB(NOW(), INTERVAL 5 QUARTER);
返回值:在当前日期时间基础上:减5个季度
SELECT DATE_SUB(NOW(), INTERVAL 5 MONTH);
返回值:在当前日期时间基础上:减5个月
SELECT DATE_SUB(NOW(), INTERVAL 5 WEEK);
返回值:在当前日期时间基础上:减5个周
SELECT DATE_SUB(NOW(), INTERVAL 5 DAY);
返回值:在当前日期时间基础上:减5SELECT DATE_SUB(NOW(), INTERVAL 5 HOUR);
返回值:在当前日期时间基础上:减5小时
SELECT DATE_SUB(NOW(), INTERVAL 5 MINUTE);
返回值:在当前日期时间基础上:减5分钟
SELECT DATE_SUB(NOW(), INTERVAL 5 SECOND);
返回值:在当前日期时间基础上:减5

格式化日期和时间
在MySQL中,您可以使用DATE_FORMAT函数来格式化日期和时间。该函数接受两个参数:日期/时间列和您希望如何显示的格式字符串。
下面是一些常用的格式化代码示例:

-- 标准格式化日期
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
 
-- 只显示日期
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');
 
-- 只显示时间
SELECT DATE_FORMAT(NOW(), '%H:%i:%s');
 
-- 自定义格式化日期和时间
SELECT DATE_FORMAT(NOW(), '%m/%d/%Y %h:%i %p');

格式化字符可以是:

  • %Y 四位数的年份
  • %y 两位数的年份
  • %m 月份(01-12)
  • %d 月中的天数(00-31)
  • %H 小时(00-23)
  • %h 小时(01-12)
  • %i 分钟(00-59)
  • %s 秒(00-59)
  • %p 上午/下午(AM/PM)

您可以根据需要组合这些格式化字符来创建自定义的日期和时间格式。

【完】

posted @   熊仔其人  阅读(73)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· 上周热点回顾(2.17-2.23)
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 如何使用 Uni-app 实现视频聊天(源码,支持安卓、iOS)
点击右上角即可分享
微信分享提示