#查询本周内
SELECT * from food_orders_detail where week(created_at)=week(create_time);
select * from food_orders_detail where week(create_time) = week(create_time)
#查询当天
select * from food_orders_detail where TO_DAYS(create_time) = TO_DAYS(NOW())
#查询近一天
select * from food_orders_detail where to_days(create_time) = to_days(now());
select * from food_orders_detail where date(create_time) = curdate();
#查询近一个月
select * from food_orders_detail where DATE_SUB(CURDATE(), INTERVAL 1 MONTH) <= date(create_time);
#查询近七天
select * from food_orders_detail where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(create_time);
#查询当前时间
select now();
select now(),sleep(3),now();
select current_timestamp,current_timestamp();
#日期转换
select date_format('2008-08-08 22:23:01','%Y%m%d%H%i%s');
select str_to_date('08/09/2008','%m/%d/%y');
#日期天数转换函数
select to_days('0000-00-00');
select to_days('2008-08-08');
#时间秒数转换
select time_to_sec('01:00:05');
select sec_to_time(3605);
#拼凑日期时间函数
select makedate(2001,31);
select makedate(2001,32);
select maketime(12,15,30);
#Unix时间戳日期转换
unix_timestamp(),
unix_timestamp(date),
from_unixtime(unix_timestamp),
from_unixtime(unix_timestamp,format)
select unix_timestamp();
select unix_timestamp('2008-08-08');
select unix_timestamp('2008-08-08 12:30:00');
select from_unixtime(1218290027);
select from_unixtime(1218124800);
select from_unixtime(1218169800);
select from_unixtime(1218169800,'%Y %D %M %h:%i:%s %x');
#日期时间计算函数
set @dt = now();
select date_add(@dt,interval 1 day);
select date_add(@dt,interval 1 hour);
select date_add(@dt,interval 1 minute);
select date_add(@dt,interval 1 second);
select date_add(@dt, interval 1 microsecond);
select date_add(@dt,interval 1 week);
select date_add(@dt,interval 1 month);
select date_add(@dt,interval 1 quarter);
select date_add(@dt,interval 1 year);
select date_add(@dt,interval -1 day);
select date_add(@dt,interval '01:15:30' hour_second);
#date_sub() 日期时间函数 和 date_add() 用法一致
#日期时间相减函数
#timediff(time1,time2) 函数的两个参数类型必须相同
select datediff('2008-08-08','2008-08-01');
select datediff('2008-08-01','2008-08-08');
select timediff('2008-08-08 08:08:08','2008-08-08 00:00:00');
select timediff('08:08:08','00:00:00');
#时间戳转换、增减函数
timestamp(date)
timestamp(dt,time)
timestampadd(unit,interval,datetime_expr)
timestampdiff(unit,datetime_expr1,datetime_expr2)
select timestamp('2008-08-08');
select timestamp('2008-08-08 08:00:00','01:01:01');
select timestamp('2008-08-08 08:00:00','10 01:01:01');
select timestampadd(day,1,'2008-08-08 08:00:00');
select date_add('2008-08-08 08:00:00',interval 1 day);
select timestampdiff(year,'2002-05-01','2001-01-01');
select timestampdiff(day,'2002-05-01','2001-01-01');
select timestampdiff(hour,'2008-08-08 12:00:00','2008-08-08 00:00:00');
select datediff('2008-08-08 12:00:00','2008-08-01 00:00:00');
#时区转换函数
convert_tz(dt,from_tz,to_tz);
select convert_tz('2008-08-08 12:00:00','+08:00','+00:00');
select date_add('2008-08-08 12:00:00',interval -8 hour);