select fullName,addedTime
from t_user
where
to_days(addedTime) <=
to_days(
now());
select fullName,addedTime
from t_user
where
to_days(
NOW()) -
TO_DAYS(addedTime) <=
1;
select fullName,addedTime
from t_user
where
date_sub(
CURDATE(),
INTERVAL
7
DAY) <=
DATE(addedTime);
SELECT fullName,addedTime
FROM t_user
where
DATE_SUB(
CURDATE(),
INTERVAL
30
DAY) <=
date(addedTime);
SELECT fullName,addedTime
FROM t_user
WHERE
DATE_FORMAT( addedTime,
'%Y%m' ) =
DATE_FORMAT(
CURDATE() ,
'%Y%m' );
SELECT fullName,addedTime
FROM t_user
WHERE
PERIOD_DIFF(
date_format(
now( ) ,
'%Y%m' ) ,
date_format( addedTime,
'%Y%m' ) ) =
1;
select fullName,addedTime
FROM t_user
where
QUARTER(addedTime)=
QUARTER(
now());
select fullName,addedTime
FROM t_user
where
QUARTER(addedTime)=
QUARTER(
DATE_SUB(
now(),
interval
1
QUARTER));
select fullName,addedTime
FROM t_user
where
YEAR(addedTime)=
YEAR(
NOW());
select fullName,addedTime
FROM t_user
where
year(addedTime)=
year(
date_sub(
now(),
interval
1
year));
select fullName,addedTime
FROM t_user
where addedTime
between
date_sub(
now(),
interval
6
month)
and
now();
SELECT fullName,addedTime
FROM t_user
WHERE
YEARWEEK(
date_format(addedTime,
'%Y-%m-%d')) =
YEARWEEK(
now());
SELECT fullName,addedTime
FROM t_user
WHERE
YEARWEEK(
date_format(addedTime,
'%Y-%m-%d')) =
YEARWEEK(
now())
-1;
select fullName,addedTime
FROM t_user
where
date_format(addedTime,
'%Y-%m')=
date_format(
DATE_SUB(
curdate(),
INTERVAL
1
MONTH),
'%Y-%m');
select fullName,addedTime
FROM t_user
where
DATE_FORMAT(addedTime,
'%Y%m') =
DATE_FORMAT(
CURDATE(),
'%Y%m');
select fullName,addedTime
FROM t_user
where
date_format(addedTime,
'%Y-%m')=
date_format(
now(),
'%Y-%m');
select fullName,addedTime
FROM t_user
where addedTime
between
'2017-1-1 00:00:00'
and
'2018-1-1 00:00:00';
select fullName,addedTime
FROM t_user
where addedTime >=
'2017-1-1 00:00:00'
and addedTime <
'2018-1-1 00:00:00';
归纳一下:
1、查询时间段内的数据,一般可以用between and 或 <> 来指定时间段。
2、mysql的时间字段类型有:datetime,timestamp,date,time,year。
3、 获取系统当前时间的函数:
select CURDATE();
select NOW();
4、获取时间差的函数:
period_diff() datediff(date1,date2) timediff(time1,time2)
5、日期加减函数:
date_sub()
date_add() adddate() addtime()
period_add(P,N)
--------以上参考文章(mysql日期加减)
6、时间格式转化函数:
date_format(date, format) ,MySQL日期格式化函数date_format()
unix_timestamp()
str_to_date(str, format)
from_unixtime(unix_timestamp, format) ,MySQL时间戳格式化函数from_unixtime
--------以上参考文章(MYSQL日期 字符串 时间戳互转)
顺带写一下oracle的查询语句:
select * from Oracle.alarmLog where alarmtime between to_date('2007-03-03 18:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2007-09-04 18:00:00','yyyy-mm-dd hh24:mi:ss')