Mysql中时间相关的sql语句

by 沧海之树 In 工作

*TIMESTAMP列类型*
TIMESTAMP值可以从1970的某时的开始一直到2037年，精度为一秒，其值作为
数字显示。

SELECT UNIX_TIMESTAMP() ;
当前时间的TIMESTAMP；
mysql> select unix_timestamp();

+------------------+
| unix_timestamp() |
+------------------+
| 1420025182 |
+------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
| 1420025184 |
+------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
| 1420025186 |
+------------------+
1 row in set (0.00 sec)

可以从上面的执行情况清楚看到timestamp是以秒为单位不停递增的。
SELECT UNIX_TIMESTAMP('2009-08-06');

mysql> SELECT UNIX_TIMESTAMP('2009-08-06') ;
+------------------------------+
| UNIX_TIMESTAMP('2009-08-06') |
+------------------------------+
| 1249488000 |
+------------------------------+
1 row in set (0.00 sec)

日期为2009-08-06的TIMESTAMP；

select unix_timestamp('2014-12-31 19:08:00');

mysql> select unix_timestamp('2014-12-31 19:08:00');
+---------------------------------------+
| unix_timestamp('2014-12-31 19:08:00') |
+---------------------------------------+
| 1420024080 |
+---------------------------------------+
1 row in set (0.00 sec)
FROM_UNIXTIME是MySQL里的时间函数，设置时间从TIMESTAMP转化为DATETIME字符串；
DATEDIFF() 函数返回两个日期之间的天数。
select unix_timestamp();

SELECT FROM_UNIXTIME( 1249488000, '%Y%m%d' );

以上函数的混用结果：

select * from xxx_ecms_news where isgood=1 and DATEDIFF(NOW(),FROM_UNIXTIME(newstime))>1 order by id desc;