mysql常用

获取当前时间

now()

拼接字符串

concat

去重

distinct

格式化时间

自定义格式
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')
今天
select * from 表名 where to_days(时间字段名) = to_days(now());
昨天
SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1
近7天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)
近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 `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());
查询上季度数据
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
查询本年数据
select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());
查询上年数据
select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));
查询当前这周的数据
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());
查询上周的数据
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;
查询上个月的数据
select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')

select * from user where DATE_FORMAT(pudate,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') ; 

select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(now()) 

select * from user where MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now()) 

select * from user where YEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = YEAR(now()) and MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now()) 

select * from user where pudate between  上月最后一天  and 下月第一天 
查询当前月份的数据 
select name,submittime from enterprise   where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')
查询距离当前现在6个月的数据
select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();

查询一小时内的数据

SELECT NOW(),DATE_SUB(NOW(),INTERVAL  1 HOUR) as the_time  
select * from xxx where create_time > DATE_SUB(NOW(),INTERVAL  1 HOUR);

查询一小时前内的数据

DATE_SUB(date,INTERVAL expr type)
SELECT NOW(),DATE_SUB(NOW(),INTERVAL  1 HOUR) as the_time
select * from xxx where create_time > DATE_SUB(NOW(),INTERVAL  1 HOUR);

存在则进行修改 不存在 进行插入

        INSERT INTO t_user_position (id,lng,lat,uptime)
        VALUES (#{id},#{lng},#{lat},NOW())
        ON DUPLICATE KEY UPDATE
        id = #{id},lng = #{lng},lat = #{lat}, uptime = NOW()

时间bug

时间相减有bug用官方函数处理

要到得确正的时光相减秒值，有以下3种方法：
1、time_to_sec(timediff(t2, t1)),
2、timestampdiff(second, t1, t2),
3、unix_timestamp(t2) -unix_timestamp(t1)

两个查询同一个表

SELECT * from (SELECT type_name,'1' as t FROM t_type_dic where type = '路段' and remarks = 'S0041440010') a LEFT JOIN (SELECT type_name, '1' as t FROM t_type_dic where type = '路段' and remarks = 'S0081440010') b on a.t = b.t