mysql查询昨天 一周前 一月前 一年前的数据以及时间函数
mysql查询昨天 一周前 一月前 一年前的数据以及时间函数
郑加威 发表于3个月前
mysql查询昨天 一周前 一月前 一年前的数据以及时间函数
  • 发表于 3个月前
  • 阅读 24
  • 收藏 0
  • 点赞 0
  • 评论 0

腾讯云 学生专属云服务套餐 10元起购>>>   

mysql 昨天 一周前 一月前 一年前的数据 这里主要用到了DATE_SUB,

参考如下

SELECT * FROM yh_content
where inputtime>DATE_SUB(CURDATE(), INTERVAL 1 DAY)
where inputtime>DATE_SUB(CURDATE(), INTERVAL 1 WEEK)
where inputtime>DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
where inputtime>DATE_SUB(CURDATE(), INTERVAL 1 YEAR)

注意:如果数据库中时间以UNIX时间戳的形式存放的话,在时间对比上需要更改为统一格式:

DATE_SUB()返回的是格式化后的时间:2014-05-17

需要用UNIX_TIMESTAMP()转化为UNIX时间戳形式对比:

where inputtime>UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY))
where inputtime>UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 WEEK))
where inputtime>UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))
where inputtime>UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 YEAR))

一、当天或当日插入的数据:

1、传统对比判断:

SELECT * FROM `t` WHERE DATE_FORMAT(addTime,'%Y-%m-%d') = date_format(now(),'%Y-%m-%d')");


2、第一点的简写:

SELECT * FROM `t` WHERE addTime >= date_format(NOW(),'%Y-%m-%d');


3、利用函数判断:

SELECT * FROM `t` WHERE DATEDIFF(addTime,NOW()) =0;//推荐此方法


4、利用时间戳判断:

SELECT * FROM `t` WHERE addTime BETWEEN (UNIX_TIMESTAMP(now()-86440)) AND now();


注:
返回当天时间的mysql函数有CURDATE()、CURRENT_DATE()、CURRENT_DATE、NOW()几种;
其中NOW()获取的日期格式为0000-00-00 00:00:00的时间;
CURDATE()、CURRENT_DATE()、CURRENT_DATE是获取日期格式为0000-00-00的时间,所以返回的时间无时分秒;

二、当月的相关数据写法:

1、今天是当月的第几天:

SELECT DAYOFMONTH( NOW( ) );


2、获取当月的第一天时间:

SELECT DATA_SUB( NOW(), INTERVAL DAYOFMONTH(NOW()) – 1 DAY);

日期运算函数,句型:date_add(date,INTERVAL expr type)和date_sub(date,INTERVAL expr type)
date为一个datetime或date值,可当作起始时间,expr为一个表达式,用来指定增加或减少的时间间隔数目,可为一个字符串–若为负值,则在表达式前加个"-"符号。
type为关键词,它表示了表达式被解释的方式,通常用年(year)、月(month)、日(day)、周(week)等。
INTERVAL的用户说明:

1、当函数使用时,即INTERVAL(),为比较函数,如:INTERVAL(10,1,3,5,7); 结果为4;
原理:10为被比较数,后面1,3,5,7为比较数,将后面四个依次与10比较,看后面数字组有多少个少于10,则返回其个数。前提是后面数字组为从小到大排列,否则返回结果0。
2、当关键词使用时,表示为设置时间间隔,常用在date_add()与date_sub()函数里,如:INTERVAL 1 DAY ,解释为将时间间隔设置为1天。

弄清楚了上面几个重要的日期运算函数,下面再来一个混合的日期运算。
3、上个月的第一天时间:SELECT DATE_SUB( DATE_SUB( NOW( ) , INTERVAL DAYOFMONTH( NOW( ) ) -1 DAY ) , INTERVAL 1 MONTH ); 是不是一目了然了!

三、当周的相关数据写法:

1、获取今天是一周第几天或星期几:

SELECT WEEKDAY(now());//返回的是数字:0为周一,6为周日

2、获取本周的第一天日期:

SELECT DATE_SUB(now(),INTERVAL WEEKDAY(now()) day);

SELECT DATE_ADD(now(),INTERVAL -WEEKDAY(now()) day);

SELECT CURDATE( ) – WEEKDAY( CURDATE( ) );


3、再写一个上周的第一天至现在的数据:(以表t为数据表)

SELECT * FROM `t` WHERE addTime >= date_format(date_sub(date_sub(now(), INTERVAL WEEKDAY(NOW()) DAY), INTERVAL 1 WEEK), ‘%Y-%m-%d');

是不是有些感觉了!

注:若你是以时间戳保存的时间字段,那么请用from_unixtime()函数转换为时间格式,
如:from_unixtime($timestamp)

四、Mysql中将日期转为时间戳

前三点的方法,讲的都是将日期转为相应时间常量,并没有提到时间戳与时间的转换,这里把它写出来,

1、时间戳转日期,方法是

select from_unixtime(1336542121);

2、日期转时间戳,方法是:

select unix_timestamp('2013-04-08′);

结合前面3点,对于时间戳就更能灵活运用了!

 

mysql自带的时间函数功能

获取当前时间格式串:

#获取当前时间戳
current_timestamp() yyyy-mm-dd hh:ii:ss 
now() yyyy-mm-dd hh:ii:ss
curdate() yyyy-mm-dd
current_date()
curtime() hh:ii:ss
current_time()

提取date各个字段

#提取date各个字段
date('yyyy-mm-dd hh:ii:ss') yyyy-mm-dd
year('yyyy-mm-dd hh:ii:ss') yyyy
month('yyyy-mm-dd hh:ii:ss') mm
day('yyyy-mm-dd hh:ii:ss') dd

提取time各个字段

#提取time各个字段
time('yyyy-mm-dd hh:ii:ss') hh:ii:ss
hour('yyyy-mm-dd hh:ii:ss') hh
minute('yyyy-mm-dd hh:ii:ss') ii
second('yyyy-mm-dd hh:ii:ss') ss

获取当前或者某一时间的unix时间戳

#unix时间戳1970-01-01以来的秒数
unix_timestamp()
#同时还可以将某一时间格式串的秒数转化出来
unix_timestamp('yyyy-mm-dd hh:ii:ss')

格式化时间串和格式化时间戳

#格式化时间串 date_format 与 time_format 为同一函数 输入为 
date_format(now(), '%Y-%m-%d %T');
time_format(now(), '%H:%i:%s');
#格式化时间戳 将时间戳转化为时间格式串
from_unixtime(unix_timestamp(), "%Y-%m-%d %T")

datetime 类型 与 timestamp 类型的比较

timestamp类型的列可以设置 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 等特殊属性

而 datetime 作为时间格式无法设置当前时间为其默认值,因为mysql不允许函数作为默认值定义,即 default now()是无效的 ,其他的时间格式还有 YEAR DATE TIME

datetime YYYY-mm-dd HH:ii:ss '1000-01-01 00:00:00'到'9999-12-31 23:59:59' 8字节
timestamp YYYY-mm-dd HH:ii:ss '1970-01-01 00:00:00'到'2037-12-31 23:59:59' 4字节

date_add() date_sub() interval

对于时间戳直接加值就可以进行时间平移,但对于时间格式串的加减我们可以使用 INTERVAL expr TYPE

TYPE是指跨度单位:

MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER【四分之一年】 YEAR

还有

SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
#"5:20:15" 5小时20分15秒的步进量
HOUR_SECOND
#"5:20" 5小时20分的步进量
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
#"0001-02"1年零2个月的步进量
YEAR_MONTH
#+- 参照点 间隔度
#当前时间加5天
date_add(now(), interval 5 DAY)
#当前时间减5天
date_sub(now(), interval 5 DAY)
#加5天2小时10分钟20秒
date_add(now(), interval "05 02:10:20" DAY_SECOND);
#加1年2个月
date_add(now(), interval "0001-02" YEAR_MONTH);
#当然也可以直接用运算符
select now() + interval 5 DAY, now() - interval 5 DAY
#换成时间戳的运算是
select unix_timestamp() + 5*24*60*60, unix_timestamp() - 5*24*60*60

extract()从时间格式串中提取时间

#提取天
select extract(DAY from now());
#提取天 小时 分钟 秒 
select extract(DAY_SECOND from now());

datediff()返回两个时间点相差的天数

#-5天 只会计算天数
select datediff(now(), now() + interval 5 day);
共有 人打赏支持
粉丝 20
博文 560
码字总数 717972
×
郑加威
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: