MySQL中时间类型DATETIME、TIMESTAMP、DATE、TIME、YEAR

青旅半醒 2022-04-05 03:59 247阅读 0赞

**1.几个的区别**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0NjU5MzQ_size_16_color_FFFFFF_t_70]

详细可以参考：[https://www.cnblogs.com/Jie-Jack/p/3793304.html][https_www.cnblogs.com_Jie-Jack_p_3793304.html]

**2.针对时间类型的一些操作**

nodejs支持多种格式转换为时间戳：

var str1 = "2017-01-19 13:00:00";
    var str2 = "Jan 19 2017 13:00:00";
    var t1 = new Date(str1).getTime();
    var t2 = new Date(str2).getTime();
    console.log(t1);
    console.log(t2);
    1484802000000
    1484802000000
    
    var t1 = Date.now();
    var t2 = new Date().getTime();
    console.log(t1);
    console.log(t2);
    var myDate = new Date();
    myDate.toLocaleString( ); //获取日期与时间
    console.log(myDate);
    myDate.toLocaleDateString(); //获取当前日期
    console.log(myDate);
    var mytime=myDate.toLocaleTimeString(); //获取当前时间
    console.log(mytime);
    1511598661956
    1511598661956
    2017-11-25T08:31:01.956Z
    2017-11-25T08:31:01.956Z
    16:31:01
    
    var date = new Date();
    var year = date.getFullYear();
    var month = date.getMonth()+1;
    var day = date.getDate();
    var hour = date.getHours();
    var minute = date.getMinutes();
    var second = date.getSeconds();
    console.log(year+'年'+month+'月'+day+'日 '+hour+':'+minute+':'+second);
    console.log(year+''+month+''+day+''+hour+''+minute+''+second);
    2017年11月25日 16:31:1
    2017112516311

选取日期时间的各个部分：日期、时间、年、季度、月、日、小时、分钟、秒、微秒（注意写法 大小写和在SQL语句内自动加引号问题引号要去除）:

选取日期时间的各个部分：日期、时间、年、季度、月、日、小时、分钟、秒、微秒
     
    set @dt = '2008-09-10 07:15:30.123456';
     
    select date(@dt); -- 2008-09-10
    select time(@dt); -- 07:15:30.123456
    select year(@dt); -- 2008
    select quarter(@dt); -- 3
    select month(@dt); -- 9
    select week(@dt); -- 36
    select day(@dt); -- 10
    select hour(@dt); -- 7
    select minute(@dt); -- 15
    select second(@dt); -- 30
    select microsecond(@dt); -- 123456

参考文章：  
[https://blog.csdn.net/yiyanbuhe/article/details/78632704][https_blog.csdn.net_yiyanbuhe_article_details_78632704]  
[https://blog.csdn.net/qq\_33355858/article/details/80576149][https_blog.csdn.net_qq_33355858_article_details_80576149]  
[https://blog.csdn.net/u012081441/article/details/78642570][https_blog.csdn.net_u012081441_article_details_78642570]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0NjU5MzQ_size_16_color_FFFFFF_t_70]: /images/20220405/d3d9cb0e0a604dc09b5941ec56c47d24.png
[https_www.cnblogs.com_Jie-Jack_p_3793304.html]: https://www.cnblogs.com/Jie-Jack/p/3793304.html
[https_blog.csdn.net_yiyanbuhe_article_details_78632704]: https://blog.csdn.net/yiyanbuhe/article/details/78632704
[https_blog.csdn.net_qq_33355858_article_details_80576149]: https://blog.csdn.net/qq_33355858/article/details/80576149
[https_blog.csdn.net_u012081441_article_details_78642570]: https://blog.csdn.net/u012081441/article/details/78642570