mysql datetime 时间比较

select * from temp where mydate > '2009-06-29 04:00:44';
Select * From temp where mydate > '2009-06-29 04:00:44' ORDER BY mydate;

Select * From temp where mydate > STR_TO_DATE('2009-06-29 04:00:44', '%Y-%m-%d %H:%i:%s');

SELECT mydate, DATE_FORMAT(mydate, '%r') FROM temp;

WHERE DATEDIFF(mydata,'2008-11-20') >=0;
The Datediff method works because it compares just the date portion, and ignores the times.

WHERE mydate <= '2008-11-25 23:59:59'

WHERE mydate < '2008-11-26 00:00:00'

SELECT * FROM event WHERE start >= "2011-07-18 00:00:00" AND start <= "2011-07-19 00:00:00";

SELECT * FROM event
WHERE start BETWEEN '2011-07-18 00:00:00' AND '2011-07-19 00:00:00'
AND end BETWEEN '2011-07-18 00:00:00' AND '2011-07-19 00:00:00'

mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY);
mysql> SELECT ADDDATE('1998-01-02', INTERVAL 31 DAY);
mysql> SELECT ADDDATE('1998-01-02', 31);

mysql> SELECT DATE_ADD('2014-10-8 23:59:59',INTERVAL 10 SECOND);
mysql> SELECT DATE_ADD('2014-10-8 23:59:59',INTERVAL -3600 SECOND);

mysql> SELECT '2015-12-06 07:58:06' < now() and '2015-12-06 07:58:06' > DATE_ADD(now(),INTERVAL -1000 SECOND);

类型(type值)	含义	expr表达式的形式
YEAR	年	YY
MONTH	月	MM
DAY	日	DD
HOUR	时	hh
MINUTE	分	mm
SECOND	秒	ss
YEAR_MONTH	年和月	YY和MM之间用任意符号隔开
DAY_HOUR	日和小时	DD和hh之间用任意符号隔开
DAY_MINUTE	日和分钟	DD和mm之间用任意符号隔开
DAY_SECOND	日和秒钟	DD和ss之间用任意符号隔开
HOUR_MINUTE	时和分	hh和mm之间用任意符号隔开
HOUR_SECOND	时和秒	hh和ss之间用任意符号隔开
MINUTE_SECOND	分和秒	mm和ss之间用任意符号隔开

阅读(96) | 评论(0) | 转发(0) |

上一篇： Linux C语言连接MySQL 增删改查操作

下一篇：mysql中datetime到time_t转换