Mysql日期，时间相关

一、日期，时间戳格式转换

在数据库中，时间的存储格式一般为时间戳，但这样对于使用人员不太方便，故在查询时可以先转换格式

1.1、FROM_UNIXTIME()

将时间戳转换为日期格式

> select from_unixtime(1654162618,'%Y-%m-%d %H:%m:%s');
+-----------------------------------------------+
| from_unixtime(1654162618,'%Y-%m-%d %H:%m:%s') |
+-----------------------------------------------+
| 2022-06-02 17:06:58                           |
+-----------------------------------------------+
1 row in set (0.00 sec)

1.2、UNIX_TIMESTAMP ()

将日期转换为时间戳格式

> select unix_timestamp('2022-06-06 17:45:20');
+---------------------------------------+
| unix_timestamp('2022-06-06 17:45:20') |
+---------------------------------------+
|                            1654508720 |
+---------------------------------------+
1 row in set (0.00 sec)

1.3、示例

查询5月25号到6月2号之间的数据

> select * from test where start_time >= UNIX_TIMESTAMP('2022-05-25 00:00:00') and start_time <= UNIX_TIMESTAMP('2022-06-02 23:59:59');

1.4、参数

%M 月名字(January……December)
%W 星期名字(Sunday……Saturday)
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）
%Y 年, 数字, 4 位
%y 年, 数字, 2 位
%a 缩写的星期名字(Sun……Sat)
%d 月份中的天数, 数字(00……31)
%e 月份中的天数, 数字(0……31)
%m 月, 数字(01……12)
%c 月, 数字(1……12)
%b 缩写的月份名字(Jan……Dec)
%j 一年中的天数(001……366)
%H 小时(00……23)
%k 小时(0……23)
%h 小时(01……12)
%I 小时(01……12)
%l 小时(1……12)
%i 分钟, 数字(00……59)
%r 时间,12 小时(hh:mm:ss [AP]M)
%T 时间,24 小时(hh:mm:ss)
%S 秒(00……59)
%s 秒(00……59)
%p AM或PM
%w 一个星期中的天数(0=Sunday ……6=Saturday ）
%U 星期(0……52), 这里星期天是星期的第一天
%u 星期(0……52), 这里星期一是星期的第一天
%% 一个文字“%”。

二、获取当前时间，日期

now()可换成时间表达式，截取时间字段里需要的数据

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2022-06-15 10:46:47 |
+---------------------+
1 row in set (0.00 sec)

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2022-06-15 |
+------------+
1 row in set (0.00 sec)

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 10:46:59  |
+-----------+
1 row in set (0.00 sec)

mysql> select week(now());
+-------------+
| week(now()) |
+-------------+
|          24 |
+-------------+
1 row in set (0.00 sec)

mysql> select month(now());
+--------------+
| month(now()) |
+--------------+
|            6 |
+--------------+
1 row in set (0.00 sec)

mysql> select quarter(now());
+----------------+
| quarter(now()) |
+----------------+
|              2 |
+----------------+
1 row in set (0.00 sec)

mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
|        2022 |
+-------------+
1 row in set (0.00 sec)

mysql>

三、时间日期函数

3.1 DATE_ADD()

作用：用于向日期添加指定的时间间隔

语法： DATE_ADD(date, INTERVAL num type)

date：时间格式，时间表达式，存储时间的字段
num：时间间隔值，正数为加，负数为减
type：时间间隔的单位：

- day：　　天
- hour：时
- minute：分钟
- second：秒
- microsecond：毫秒
- week：周
- month：月
- quarter：季度
- year：年

3.1.1 示例：

简单运算

mysql> select date_add(now(), interval 1 hour);
+----------------------------------+
| date_add(now(), interval 1 hour) |
+----------------------------------+
| 2022-06-14 17:09:28              |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select date_add('2022-06-07 23:54:12', interval -1 day);
+--------------------------------------------------+
| date_add('2022-06-07 23:54:12', interval -1 day) |
+--------------------------------------------------+
| 2022-06-06 23:54:12                              |
+--------------------------------------------------+
1 row in set (0.00 sec)

mysql>

作为执行时间的判断条件，获取前一天的数据

select
  from_unixtime(notify_time, '%Y-%m-%d %H:%m:%s') as 告警通知时间,
  date_add(from_unixtime(notify_time, '%Y-%m-%d %H:%m:%s'), interval -1 day) as 前一天
from
  alarm_lists
where
  notify_time >= UNIX_TIMESTAMP(date_add(now(), interval -2 day)) and notify_time <= UNIX_TIMESTAMP(date_add(now(), interval -1 day));

3.2 DATE_SUB()

作用：从日期减去指定的时间间隔

此函数与本文3.1 的DATE_ADD() 可以看到互为相反函数，作用语法，支持的type值基本相同，唯一是DATE_ADD() num 为正数时是相加，DATE_SUB() num 为正数时是相减

可参考3.1，在此不做过多叙述

示例：

mysql> select date_sub(now(),interval 1 day);
+--------------------------------+
| date_sub(now(),interval 1 day) |
+--------------------------------+
| 2022-06-14 10:25:42            |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select date_sub(now(),interval -1 day);
+---------------------------------+
| date_sub(now(),interval -1 day) |
+---------------------------------+
| 2022-06-16 10:25:46             |
+---------------------------------+
1 row in set (0.00 sec)

3.3 DATEDIFF()

作用：返回两个日期之间的天数，day1 - day2

注：只有值的日期部分参与计算

示例：

mysql> select datediff('2022-06-05 15:00:00','2022-06-04 20:00:00');
+-------------------------------------------------------+
| datediff('2022-06-05 15:00:00','2022-06-04 20:00:00') |
+-------------------------------------------------------+
|                                                     1 |
+-------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

3.4 PERIOD_DIFF()

作用：返回两个日期之间的月数，day1 -day2

注：只有值的月数部分参与计算

示例：

mysql> SELECT PERIOD_DIFF(201903, 201803);
+-----------------------------+
| PERIOD_DIFF(201903, 201803) |
+-----------------------------+
|                          12 |
+-----------------------------+
1 row in set (0.00 sec)

mysql>

3.5 TIMEDIFF()

作用：返回两个时间之间的差值，date1 -date2

注：两值必须为相同类型的参数，否则返回NULL

示例：

mysql> select timediff('2022-06-05 15:00:00','2022-06-04 20:00:00');
+-------------------------------------------------------+
| timediff('2022-06-05 15:00:00','2022-06-04 20:00:00') |
+-------------------------------------------------------+
| 19:00:00                                              |
+-------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select timediff('15:00:00','14:55:30');
+---------------------------------+
| timediff('15:00:00','14:55:30') |
+---------------------------------+
| 00:04:30                        |
+---------------------------------+
1 row in set (0.00 sec)

mysql>

3.6 TIMESTAMPDIFF()

作用：返回两个时间表达式之间的差值，datetime_expr2 - datetime_expr1

语法： TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

注：datetime_expr1,datetime_expr2 可以是不同的数据类型，如date或datetime

　　unit：结果的单位，整数型

- day：　　天
- hour：时
- minute：分钟
- second：秒
- microsecond：毫秒
- week：周
- month：月
- quarter：季度
- year：年

示例：

mysql> select timestampdiff(minute,'2022-06-05 12:00:00','2022-06-05 12:30:31') as t1,timestampdiff(minute,'2022-06-05 12:00:00','2022-06-05') as t2,timestampdiff(minute,'2022-06-05 12:00:00','2022-06-05 00:00:00') as t3;
+------+------+------+
| t1   | t2   | t3   |
+------+------+------+
|   30 | -720 | -720 |
+------+------+------+
1 row in set (0.00 sec)

mysql>

相差时间：

mysql> SELECT NOW() 当前日期,DATE_ADD(NOW(),INTERVAL - 800 DAY) 历史日期,TIMESTAMPDIFF(DAY,DATE_ADD(NOW(), INTERVAL - 800 DAY),NOW()) AS 相差天数;
+---------------------+---------------------+--------------+
| 当前日期            | 历史日期            | 相差天数     |
+---------------------+---------------------+--------------+
| 2022-06-15 09:54:02 | 2020-04-06 09:54:02 |          800 |
+---------------------+---------------------+--------------+
1 row in set (0.00 sec)

mysql>

两值相减作为判断条件：

if(TIMESTAMPDIFF(minute,from_unixtime(start_time, '%Y-%m-%d %H:%m:%s'),from_unixtime(end_time, '%Y-%m-%d %H:%m:%s')) > 5 
     and TIMESTAMPDIFF(minute,from_unixtime(start_time, '%Y-%m-%d %H:%m:%s'),from_unixtime(end_time, '%Y-%m-%d %H:%m:%s')) <= 10,1,0) as '5~10min',

3.7 TO_DAYS()

作用：返回一个从0年开始到指定日期之间相隔的天数，常用作于查询指定时间段内的所有数据

注：TO_DAYS()不适用于公历日历（1582）出现之前的值，因为它不考虑日历更改时丢失的日期。在1582年之前的日期（可能在其他地区的其他年份），此功能的结果不可靠。

　　MySQL将日期中的两位数年份值转换为四位数形式。例如， '2017-10-09'、 '17-10-09'和‘171009’被看作是相同的日期

示例：

mysql> select to_days(now());
+----------------+
| to_days(now()) |
+----------------+
|         738686 |
+----------------+
1 row in set (0.00 sec)

mysql>

查询今天的数据(从今天到当前执行时间)：

select
  from_unixtime(notify_time, '%Y-%m-%d %H:%m:%s')
from
  alarm_lists
where
  to_days(from_unixtime(notify_time, '%Y-%m-%d %H:%m:%s')) = to_days(now())

昨天：

SELECT
  from_unixtime(notify_time, '%Y-%m-%d %H:%m:%s') as 告警通知时间
FROM
  alarm_lists
WHERE
  TO_DAYS(NOW()) - TO_DAYS(from_unixtime(notify_time, '%Y-%m-%d %H:%m:%s')) <= 1

3.8 DATE_FORMAT()

作用：以不同的格式显示日期/时间数据

语法： DATE_FORMAT(date,format)

支持的格式：参考本文1.4

示例：

mysql> select date_format(now(),'%b %d %Y %h:%i %p');
+----------------------------------------+
| date_format(now(),'%b %d %Y %h:%i %p') |
+----------------------------------------+
| Jun 15 2022 10:56 AM                   |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> select date_format(now(),'%m-%d-%Y');
+-------------------------------+
| date_format(now(),'%m-%d-%Y') |
+-------------------------------+
| 06-15-2022                    |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select date_format(now(),'%d %b %y');
+-------------------------------+
| date_format(now(),'%d %b %y') |
+-------------------------------+
| 15 Jun 22                     |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select date_format(now(),'%d %b %Y %T:%f');
+-------------------------------------+
| date_format(now(),'%d %b %Y %T:%f') |
+-------------------------------------+
| 15 Jun 2022 10:56:46:000000         |
+-------------------------------------+
1 row in set (0.00 sec)

mysql>

四、查询指定时间段的数据

mysql 时间日期函数很多，多结合函数来判断时间范围，查询自己想要的数据，多学多练，熟能生巧

以下都是截取到当前时间作为时间范围，实际运用中，可定时执行，例每天晚上 00:05:00 执行，查询从几天前的 00:05:00 到今天的数据

今天：

select * from 表名 where to_days(时间字段名) = to_days(now());

昨天：

SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1

本周：

select * FROM 表名 WHERE YEARWEEK(date_format(start_time,'%Y-%m-%d')) = YEARWEEK(now());

上周：

SELECT * FROM 表名 WHERE YEARWEEK(date_format(start_time,'%Y-%m-%d')) = YEARWEEK(now())-1;

近30天：

SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)

本月：

SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )

上月：

SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1

本季度：

select * from 表名 where QUARTER(create_date)=QUARTER(now())

上季度：

select * from 表名 where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

本年：

select * from 表名 where YEAR(create_date)=YEAR(NOW());

去年：

select * from 表名 where year(create_date)=year(date_sub(now(),interval 1 year));

相关阅读:
大型网站架构系列：20本技术书籍推荐
 程序员进阶顺序
 乐观锁与悲观锁——解决并发问题
 Redis的事务功能详解
 驱动开发（8）处理设备I/O控制函数DeviceIoControl
钱币兑换问题（杭电1284）（母函数）
jqm文件上传,上传图片,jqm的表单操作,jqm的ajax的使用,jqm文件操作大全,文件操作demo
问：简述一下内部类的实质是什么？
Android 删除新版安卓fragment_main.xml
保温饭盒毕业设计程序
原文地址：https://www.cnblogs.com/Xinenhui/p/16348570.html