SQL：DATETIME和TIMESAMP的对比&&时间戳与日期的转换

朱雀 2022-03-27 07:52 218阅读 0赞

尽量使用timesamp

# DATETIME和TIMESAMP的对比 #

>  *  create table time\_test (tstmp timestamp not null, dttime datetime not null);  
>     ![在这里插入图片描述][20190103141937943.png]  
>     ![在这里插入图片描述][201901031421416.png]
>  *  insert into time\_test(dttime) values(‘20181212001122’);  
>     ![在这里插入图片描述][20190103142636215.png]

相同点  
datetime和timesamp都可以存储相同类型的数据：时间和日期，精确到秒。

不同点：  
1、使用的存储空间不同

*  timesamp使用4个字节的存储空间
 *  datetime使用8个字节的存储空间

2、存储时间范围不同  
timesamp只使用datetime一半的存储空间，因此存储的时间范围更小

*  timestamp所能存储的时间范围为：‘1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’。
 *  datetime所能存储的时间范围为：‘1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’。

3、与时区的关系  
timesamp会根据时区变化，具有特殊的自动更新能力。

mysql> create table test_time(a timestamp, b datetime);
    
    mysql>  insert into test_time values (now(), now());
    
    mysql>  select * from test_time;
    +---------------------+---------------------+
    | a                   | b                   |
    +---------------------+---------------------+
    | 2019-01-12 21:58:54 | 2019-01-12 21:58:54 |
    +---------------------+---------------------+
    
    mysql>  select @@time_zone; 
    +-------------+
    | @@time_zone |
    +-------------+
    | SYSTEM      |
    +-------------+
    
    mysql> set time_zone='+00:00';
    
    mysql>  select @@time_zone;
    +-------------+
    | @@time_zone |
    +-------------+
    | +00:00      |
    +-------------+
    
    mysql> select * from test_time;
    +---------------------+---------------------+
    | a                   | b                   |
    +---------------------+---------------------+
    | 2019-01-13 05:58:54 | 2019-01-12 21:58:54 |
    +---------------------+---------------------+

# unix时间戳与日期的转换 #

1、FROM\_UNIXTIME()  
作用：unix时间戳转换为日期

*  ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70]  
    2、UNIX\_TIMESTAMP  
    作用：日期转换为时间戳  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70 1]  
    参考：https://blog.csdn.net/rocling/article/details/82695068

[20190103141937943.png]: /images/20220327/10d0cc4ebcb14ff3b616c36f6a8ef29e.png
[201901031421416.png]: /images/20220327/0f32b9a2bbbb49acafc71cbe4a60781d.png
[20190103142636215.png]: /images/20220327/2106c663b7be4303bcef2267ca65d577.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70]: /images/20220327/d03fc4734f124c18bfb05359a782901f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70 1]: /images/20220327/61462056bac7414085f22b79c4a69871.png