MySQL中的日历/时间/时间戳

一，日历

MySQL 使用通常所说的 proleptic 阳历。

每个将日历由朱利安改为阳历的国家在改变日历期间都不得不删除至少10天。为了了解其运作，让我们看看1582年10月，这是由朱利安日历转换为阳历的第一次:

周一	周二	周三	周四	周五	周六	周日
1	2	3	4	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30	31

在10月4 日到10月15日之间的日期为空白。这个中断被称为接入。接入前的日期均使用朱利安日历, 而接入后的日期均使用阳历。接入期间的日期是不存在的。

当一个用于日期的日历并为得到实际使用时被称为 proleptic。因此, 若我们假设从来没有接入期的存在，而阳历历法则始终被使用，我们会有一个预期的阳历。这就是MySQL 所使用的，正如标准SQL所要求的。鉴于这个原因，作为MySQL DATE 或 DATETIME值而被储存的接入前的日期必须调整这个差异。众所周知，接入的发生时间在不同的国家有所不同，而接入的时间越晚，遗失的日期越多。例如，在大不列颠, 接入发生在 1752年, 这年9月2日周三后的第二天为9月14日，周二；俄罗斯结束使用朱利安日历的时间为1918年,接入过程中遗失天数为 13天, 根据阳历，其普遍被称为“10月革命”的发生时间实际上是11月。

二，获取日期和时间

1，查询当前日期

SELECT CURDATE();
SELECT CURDATE()+0;

2，查询当前时间

SELECT CURTIME();
SELECT CURTIME() + 0;

3，查询当前日期和时间

SELECT NOW();
SELECT NOW()+0;

SELECT SYSDATE();
SELECT SYSDATE()+0;

三，日期和时间转换

1, 日期/时间转换成字符串 date_format(date,format), time_format(time,format)

select date_format(now(),"%Y-%m-%d");
select time_format(now(),"%h:%i:%s");

2，字符串转换成日期时间 str_to_date(str, format)

select str_to_date('2024-03-27 12:32:24', '%Y-%m-%d %H:%i:%s');
select str_to_date('03.27.2024 12:32:24', '%m.%d.%Y %H:%i:%s');

3，日期/天数转换 to_days(date), from_days(days)

select to_days('2024-03-01'); -- 1(从1970-01-01到2024-03-01中间天数)
select to_days(curdate()); -- 739337(从1970-01-01到当前日期中间天数)
select from_days(798797); -- 2187-01-12(1970-01-01之后798797天的日期)

4，时间/秒转换 time_to_sec(time), sec_to_time(seconds)

select time_to_sec('01:00:05'); -- 3605(从每天0点0分0秒过了多少秒)
select sec_to_time(3605); -- 01:00:05(秒数转换为时间)

5，拼凑日期/时间 makdedate(year,dayofyear), maketime(hour,minute,second)

select makedate(2020,31); -- 2020-01-3(拼凑日期)
select makedate(2020,32); -- 2020-02-01(拼凑日期)
select maketime(12,15,30); -- 12:15:30(拼凑时间)

6，Unix时间戳/日期转换函数

select unix_timestamp(); -- 1711497600
select from_unixtime(1711529796); -- 2024-03-27 08:56:36

select unix_timestamp(curdate()); -- 1711497600
select from_unixtime(1711497698); -- 2024-03-27 00:01:38

四，时间戳

1, 到秒级的，需要乘以1000（毫秒，因为时间戳是从起始时间到现在毫秒的差）

比如日期2024-03-11对应的时间戳范围：1710111600000-1710198000000

一天的时间戳增量：86400000

SELECT current_timestamp;
SELECT current_timestamp();

SELECT current_timestamp+0;
SELECT current_timestamp()+0;

SELECT UNIX_TIMESTAMP();
SELECT UNIX_TIMESTAMP(now());

2，时间戳

SELECT UNIX_TIMESTAMP()*1000;
SELECT UNIX_TIMESTAMP(now())*1000;

五，日期时间计算

1，日期/时间添加 date_add()

set @cdt = now();

select @cdt ;
select date_add(@cdt , interval 1 day); -- 加一天
select date_add(@cdt , interval 1 hour); -- 加一小时
select date_add(@cdt , interval 1 minute); -- 加一分钟
select date_add(@cdt , interval 1 second);
select date_add(@cdt , interval 1 microsecond);
select date_add(@cdt , interval 1 week);
select date_add(@cdt , interval 1 month);
select date_add(@cdt , interval 1 quarter);
select date_add(@cdt , interval 1 year);

select date_add(@cdt , interval -1 day);

2，日期/时间减少 date_sub()

select date_sub(now(), interval '1 1:1:1' day_second);

3，日期、时间相减函数 datediff(date1,date2), timediff(time1,time2)

select datediff(curdate(), '2023-05-01'); --两个日期相隔331天
select datediff('2023-05-08', '2023-05-01'); --前面日期和后面日期间隔7天

4，时间戳（timestamp）转换、增、减函数

-- 转换
select timestamp('2024-03-27'); -- 2024-03-27 00:00:00
select timestamp('2024-03-27 19:52:51', '01:01:01'); -- 2024-03-27 20:53:52
select timestamp(current_timestamp(), '10 01:01:01'); -- 2024-03-27 20:54:46

-- 增
select timestampadd(day, 1, current_timestamp()); -- 2024-03-28 09:06:19
select date_add(current_timestamp(), interval 1 day); -- 2024-03-28 09:06:26

-- 减
select timestampdiff(year,current_timestamp(),'2023-01-01'); -- -1
select timestampdiff(day ,'2024-03-27','2023-01-01'); -- -451
select timestampdiff(hour,'2024-03-27 12:00:00','2024-03-27 00:00:00'); -- -12
select datediff('2024-03-27 12:00:00', '2024-03-26 00:00:00'); -- 1

六，时区转换

1，时区转换 convert_tz(dt,from_tz,to_tz)

select convert_tz('2024-05-08 12:00:00', '+08:00', '+00:00'); -- 2024-05-08 04:00:00
select date_add('2024-05-08 12:00:00', interval -8 hour); -- 2024-05-08 04:00:00
select date_sub('2024-05-08 12:00:00', interval 8 hour); -- 2024-05-08 04:00:00
select timestampadd(hour, -8, '2024-05-08 12:00:00'); -- 2024-05-08 04:00:00

七，时间格式说明

说明符	说明
%a	工作日的缩写名称 (Sun..Sat)
%b	月份的缩写名称 (Jan..Dec)
%c	月份，数字形式(0..12)
%D	带有英语后缀的该月日期 (0th, 1st, 2nd, 3rd, ...)
%d	该月日期, 数字形式 (00..31)
%e	该月日期, 数字形式(0..31)
%f	微秒 (000000..999999)
%H	小时(00..23)
%h	小时(01..12)
%I	小时 (01..12)
%i	分钟,数字形式 (00..59)
%j	一年中的天数 (001..366)
%k	小时 (0..23)
%l	小时 (1..12)
%M	月份名称 (January..December)
%m	月份, 数字形式 (00..12)
%p	上午（AM）或下午（ PM）
%r	时间 , 12小时制 (小时hh:分钟mm:秒数ss 后加 AM或PM)
%S	秒 (00..59)
%s	秒 (00..59)
%T	时间 , 24小时制 (小时hh:分钟mm:秒数ss)
%U	周 (00..53), 其中周日为每周的第一天
%u	周 (00..53), 其中周一为每周的第一天
%V	周 (01..53), 其中周日为每周的第一天 ; 和 %X同时使用
%v	周 (01..53), 其中周一为每周的第一天 ; 和 %x同时使用
%W	工作日名称 (周日..周六)
%w	一周中的每日 (0=周日..6=周六)
%X	该周的年份，其中周日为每周的第一天, 数字形式,4位数;和%V同时使用
%x	该周的年份，其中周一为每周的第一天, 数字形式,4位数;和%v同时使用
%Y	年份, 数字形式,4位数
%y	年份, 数字形式 (2位数)
%%	‘%’文字字符