MySQL 时间戳（Timestamp）函数

妖狐艹你老母 2022-06-18 10:36 189阅读 0赞

1. MySQL 获得当前时间戳函数：current\_timestamp, current\_timestamp()  
mysql> select current\_timestamp, current\_timestamp();  
\+---------------------+---------------------+  
| current\_timestamp | current\_timestamp() |  
\+---------------------+---------------------+  
| 2008-08-09 23:22:24 | 2008-08-09 23:22:24 |

\+---------------------+---------------------+

2. MySQL （Unix 时间戳、日期）转换函数：  
unix\_timestamp(),  
unix\_timestamp(date),  
from\_unixtime(unix\_timestamp),  
from\_unixtime(unix\_timestamp,format)  
下面是示例：  
select unix\_timestamp(); -- 1218290027  
select unix\_timestamp('2008-08-08'); -- 1218124800  
select unix\_timestamp('2008-08-08 12:30:00'); -- 1218169800

select from\_unixtime(1218290027); -- '2008-08-09 21:53:47'  
select from\_unixtime(1218124800); -- '2008-08-08 00:00:00'  
selectfrom\_unixtime(1218169800); -- '2008-08-08 12:30:00'

selectfrom\_unixtime(1218169800, '%Y %D %M %h:%i:%s %x'); -- '2008 8th August12:30:00 2008'

3. MySQL 时间戳（timestamp）转换、增、减函数：  
timestamp(date) -- date to timestamp  
timestamp(dt,time) -- dt + time  
timestampadd(unit,interval,datetime\_expr) --  
timestampdiff(unit,datetime\_expr1,datetime\_expr2) --  
请看示例部分：  
select timestamp('2008-08-08'); -- 2008-08-08 00:00:00  
select timestamp('2008-08-08 08:00:00', '01:01:01'); -- 2008-08-08 09:01:01  
select timestamp('2008-08-08 08:00:00', '10 01:01:01'); -- 2008-08-1809:01:01

select timestampadd(day, 1, '2008-08-08 08:00:00'); --2008-08-09 08:00:00  
select date\_add('2008-08-08 08:00:00', interval 1 day); -- 2008-08-09 08:00:00  
MySQL timestampadd() 函数类似于 date\_add()。  
select timestampdiff(year,'2002-05-01','2001-01-01'); -- -1  
select timestampdiff(day ,'2002-05-01','2001-01-01'); -- -485  
select timestampdiff(hour,'2008-08-08 12:00:00','2008-08-08 00:00:00'); -- -12  
select datediff('2008-08-08 12:00:00', '2008-08-01 00:00:00'); -- 7  
MySQL timestampdiff() 函数就比 datediff() 功能强多了，datediff() 只能计算两个日期（date）之间相差的天数。