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之間用任意符号隔開