MySQL时间戳与日期时间转换

全栈程序员-站长 • 2022年6月21日上午11:36 • 未分类 • 阅读 85

大家好，又见面了，我是你们的朋友全栈君。

时间戳与日期时间转换

参考：mysql 将时间戳直接转换成日期时间 – snn1410

-- 1、将时间戳转换为日期时间
SELECT FROM_UNIXTIME( 1585108245.567);
运行结果：2020-03-25 11:50:45.567

-- 也可以使用修饰符自定义格式
SELECT FROM_UNIXTIME( 1585108245.567,'%Y年%m月%d日 %H时%i分%S秒 %f');
运行结果： 2020年03月25日 11时50分45秒 567000  --详解下翻

-- 2、若时间戳为负数，该方法的结果为null值
select FROM_UNIXTIME(-2);
运行结果：null

-- 解决方法：使用函数 DATE_ADD(date,INTERVAL expr type);
select DATE_ADD(FROM_UNIXTIME(0),INTERVAL -2 SECOND);
运行结果：1970-01-01 07:59:58  --详解下翻

-- 将日期转换为时间戳
select UNIX_TIMESTAMP('2020-03-25 11:11:45');
运行结果： 1585105905

-- 其无参方法将当前系统时间转换为时间戳
select UNIX_TIMESTAMP();
运行结果： 1585108852

详细：
FROM_UNIXTIME()函数，具体如下：

FROM_UNIXTIME(unix_timestamp,format) 返回表示 Unix 时间标记的一个字符串，根据format字符串格式化。
下列修饰符可以被用在format字符串中（粗体为常用）：

修饰符	含义	值例举
%Y	年，4 位数字	2019
%y	年，后 2 位数字	19
%M	月，英文名	January……December
%m	月，数字，不足两位数补0	01……12
%b	月，英文缩写	Jan……Dec
%c	月，数字	1……12
%D	日，英文序数	1st、2nd、3rd、
%d	日，数字，不足两位数补0	00……31
%e	日，数字	0……31
%j	一年中的第n天，不足三位数补0	001……366
%W	星期，英文名	Sunday……Saturday
%w	星期，数字	0=Sunday ……6=Saturday
%a	星期，英文缩写	Sun……Sat
%U	一年中的第n星期	0……52，这里星期天是星期的第一天
%u	一年中的第n星期	0……52，这里星期一是星期的第一天
%H	时，24时制，不足两位数补0	00……23
%h	时，12时制，不足两位数补0	01……12
%k	时，24时制	0……23
%I (大写的 i)	时，12时制，不足两位数补0	01……12
%l (小写的 L)	时，12时制	1……12
%i	分，数字，不足两位数补0	00……59
%S	秒，不足两位数补0	00……59
%f	毫秒，不足6位补0	0.000000 …… 0.999999
%s	秒，不足两位数补0	00……59
%p	AM或PM
%r	时间，12小时制	hh:mm:ss [AP]M
%T	时间，24小时制	hh:mm:ss
%%	字符“%”

DATE_ADD()函数，具体如下：

DATE_ADD(date,INTERVAL expr type)，该函数可以得到在原有的日期时间上增加一段时间后的日期时间。
date参数为日期时间格式的数据；
INTERVAL为关键字；
type为时间单位。
在上面的SQL例子中，即为在基准时刻 1970-01-01 08:00:00 的基础上加上 -2 秒，得到了时刻 1970-01-01 07:59:58
关于基准时刻需要注意，它是根据不同时区来设定的。我们国内是使用的东八区的时区，所以基准时刻是08:00:00，若是在0时区，则基准时刻为00:00:00。至于MySQL数据库是根据哪里判断的时区，还不清楚，以后补充。

参考：
Mysql中处理1970年前的日期（unixtime为负数的情况）负数时间戳格式化 – 与f

type值	预期的expr格式
MICROSECOND	整数
SECOND	整数
MINUTE	整数
HOUR	整数
DAY	整数
WEEK	整数
MONTH	整数
QUARTER	整数（季节）
YEAR	整数
SECOND_MICROSECOND	‘秒.毫秒’
MINUTE_MICROSECOND	‘分钟数.毫秒’
MINUTE_SECOND	‘分:秒’
HOUR_MICROSECOND	‘小时数.毫秒’
HOUR_SECOND	‘时:分:秒’
HOUR_MINUTE	‘时:分’
DAY_MICROSECOND	‘日期号数.毫秒’
DAY_SECOND	‘日时:分:秒’
DAY_MINUTE	‘日时:分’
DAY_HOUR	‘日小时’
YEAR_MONTH	‘年-月’