Mysql中使用sql语句查询前一个天、前一周、前一月时间的方法
https://blog.csdn.net/YickreeSuen/article/details/79980446
前14天
在数据库总存的试varchar的时间例如;2019-11-18 01:42:08
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 14 DAY) <= date(时间字段名)
最近两周:
SELECT * FROM 表名 where date_sub(CURDATE(),INTERVAL (WEEKDAY(CURDATE()) +7) day) <= DATE(时间字段名)
(WEEKDAY(date) 返回 date 的星期索引(0 = Monday, 1 = Tuesday, ... 6 = Sunday):
mysql> SELECT WEEKDAY(’1998-02-03 22:23:00’);
)
时间戳转日期:
FROM_UNIXTIME(updatetime)
https://blog.csdn.net/qq_36614559/article/details/80093045
https://www.cnblogs.com/ltian123/p/11077901.html
日期转时间戳;
UNIX_TIMESTAMP(updatetime)
查毫秒差:
UNIX_TIMESTAMP(updatetime)-UNIX_TIMESTAMP(timestamp)
时间格式化:
date_format( timestamp, '%Y%m' ));
DATE_FORMAT将传来的Date类型转为自己需要的格式,如%Y-%m-%d %H:%i:%s会将传来的startTime和endTime转为"yyyy-MM-dd HH:mm:ss"格式
select * from tableName a
where a.time between DATE_FORMAT(#{startTime},'%Y-%m-%d %H:%i:%s') and DATE_FORMAT(#{endTime},'%Y-%m-%d %H:%i:%s')
https://www.jianshu.com/p/593ace0424ff
注:
SELECT
id,
DATE_FORMAT(create_time, '%Y-%c-%e')
FROM epc_mes_item
WHERE DATE_FORMAT(create_time, '%Y-%c-%d') >= '2018-11-19';
==========================
需求:
写个sql 查询 smslog表 要有手机号 发送内容 插入时间 插入时间和更新时间的时间差以秒为单位 查询最近两星期的
sql:
select mobile,content,msgid,timestamp,updatetime,
UNIX_TIMESTAMP(updatetime)-UNIX_TIMESTAMP(timestamp) time from smslog where date_sub(CURDATE(),INTERVAL (WEEKDAY(CURDATE()) +7) day) <= DATE(timestamp) order by timestamp desc;