mysql与时间有关的查询

2024-10-21 13:10:09

date(str)函数可以返回str中形如"1997-05-26"格式的日期，str要是合法的日期的表达式，如2008-08-08 22:20:46
时间是可以比较大小的，例如：

# 14 两个时间要写成year-month-day形式，例"1997-05-26"

select * from TASK where date(deadline) >' ' and  date(create_time)<' '

# 16 [date]用year-month-day替换，month要两位，不够补0

select * from TASK where date(deadline) between date_sub('[date]', INTERVAL 6 day) and '[date]'

字符串转为日期STR_TO_DATE

# 18 [date]严格按照"2015-03-06 12:00:00"这种格式替换

update TASK set finish_time=STR_TO_DATE('[date]', '%Y-%m-%d %H:%i:%s') where task_id=' '

日期的加减date_sub和date_add

# 16 [date]用year-month-day替换，month要两位，不够补0

select * from TASK where date(deadline) between date_sub('[date]', INTERVAL 6 day) and '[date]'

# 17 [date]用year-month-day替换，month要两位，不够补0

select * from TASK where date(create_time) between date_sub('[date]', INTERVAL 6 day) and '[date]'

# INTERVAL后面也可以跟month、year

模糊查询%

# 匹配含有[date]的字段

select * from TASK where deadline like '%[date]%'

最新文章

热门文章