1.关于mysql时间戳的限制
目前timestamp所能表示的范围在1970 - 2038之间。超过这个范围得到的时间将会溢出得到的时间是null.眼看2038已不远,在设计数据库的时候,也会考虑把timestamp换成其他类型来定义。
mysql> select from_unixtime(0);
+---------------------+
| from_unixtime(0) |
+---------------------+
| 1970-01-01 08:00:00 |
+---------------------+
mysql> select from_unixtime(2147483650);
+---------------------------+
| from_unixtime(2147483650) |
+---------------------------+
| 2038-01-19 11:14:10 |
+---------------------------+
1 row in set (0.00 sec)
2.unix_timestamp
将时间转化为时间戳。(date类型数据转换成timestamp形式整数)
没传时间参数则取当前时间的时间戳
mysql>select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
| 1443577432 |
+------------------+
1 row in set (0.01 sec)
mysql>select unix_timestamp('2015-10-01 10:10:10');
+---------------------------------------+
| unix_timestamp('2015-10-01 10:10:10')
+---------------------------------------+
| 1443665410 |
+---------------------------------------+
1 row in set (0.00 sec)
3.from_unixtime
将timestamp形式整数转化为date类型
mysql> select from_unixtime(1439072360);
+---------------------------+
| from_unixtime(1439072360) |
+---------------------------+
| 2015-08-09 06:19:20 |
+---------------------------+
1 row in set (0.00 sec)
当然也可以指定输出的时间格式:
mysql> select from_unixtime(1439072360,'%Y%m%d');
+------------------------------------+
| from_unixtime(1439072360,'%Y%m%d') |
+------------------------------------+
| 20150809 |
+------------------------------------+