MySQL中关于datetime、date、time、str之间的转化与比较

吾爱主题 阅读:134 2022-11-23 14:25:00 评论:0

datetime、date、time、str之间的转化与比较

?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 SELECT NOW(),CURDATE(),CURTIME(),    -- datetime 转 date time    NOW(), DATE (NOW()), TIME (NOW()), CONCAT( DATE (NOW()), ' ' , TIME (NOW())),    -- str 转 datetime date time    str_to_date( '2019-04-25 08:50:00' , '%Y-%m-%d %H:%i:%s' ), DATE ( '2019-04-25 08:50:00' ), TIME ( '2019-04-25 08:50:00' ),    str_to_date( '2019-04-25 08:50:00' , '%Y-%m-%d %T' ),    -- 比较    NOW() = CONCAT( DATE (NOW()), ' ' , TIME (NOW())),    DATE (NOW()) = '2019-04-25' ,    NOW() = DATE (NOW()),    NOW() = TIME (NOW());   -- 获取当前时间的函数 select CURRENT_TIMESTAMP , CURRENT_DATE , CURRENT_TIME ;   -- 当前时间 select CURRENT_TIMESTAMP (), CURRENT_DATE (), CURRENT_TIME (), CURDATE(), CURTIME();   -- 当前时间 select now(), sysdate(), SLEEP(3), now(), sysdate();    -- 当前时间 select UTC_TIMESTAMP(), UTC_DATE(), UTC_TIME(); -- UTC时间 select UNIX_TIMESTAMP(), UNIX_TIMESTAMP(now()); -- 函数返回连接当前时区内的值(时间戳) select FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y-%m-%d %H:%i:%s' ); -- 时间戳格式化 select DATE_FORMAT(now(), '%Y-%m-%d %H:%i:%s' ); -- 时间格式化   -- ----------------- 补充日期操作 /* 对日期进行 加减(两个函数记一个即可) DATE_ADD( date ,INTERVAL expr type); DATE_SUB( date ,INTERVAL expr type); type取值: YEAR MONTH DAY HOUR MINUTE SECOND 、YEAR_MONTH、DAY_SECOND 等等 */ -- 加1天 select DATE_ADD(now(),INTERVAL 1 DAY ), DATE_SUB(now(),INTERVAL -1 DAY ); -- 复合型:加1年1月 select DATE_ADD(now(),INTERVAL '1 1' YEAR_MONTH), DATE_SUB(now(),INTERVAL '-1 -1' YEAR_MONTH); -- 加1天2时3分4秒 select DATE_ADD( '2020-12-23 00:00:00' ,INTERVAL '1 2:3:4' DAY_SECOND),DATE_SUB( '2020-12-23 00:00:00' ,INTERVAL '-1 -2:-3:-4' DAY_SECOND);   /* 获取两个日期之间的差值(datetime_expr2-datetime_expr1的值) TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) unit:差值的单位,取值:FRAC_SECOND(低版本不支持)、 SECOND MINUTE HOUR DAY 、WEEK、 MONTH 、QUARTER或 YEAR */ -- 查询两个日期间的差值(单位秒) select TIMESTAMPDIFF( SECOND , '2020-12-01 00:00:00' , '2020-12-01 01:01:01' );

MySQL日期和时间数据类型(DATE、TIME、 DATETIME、 TIMESTAMP和YEAR

本文基于MySQL 5.7版本。

MySQL中表示时间值的日期和时间数据类型有以下五个:

(1)DATE

(2)TIME

(3)DATETIME

(4)TIMESTAMP

(5)YEAR

日期和时间数据类型语法

MySQL允许对TIME、DATETIME和TIMESTAMP使用小数秒,精度最高可达微秒(6位小数)。要定义包含小数秒日期和时间数据类型的列,使用

type_name(fsp),

其中type_name是TIME、DATETIME或TIMESTAMP,而fsp是小数秒精度,fsp的值,如果给定,必须在0 ~ 6之间。值为0表示没有小数部分。如果省略,默认精度为0。

例如:

?
1 CREATE TABLE date_time_test (t TIME (3), dt DATETIME(6), ts TIMESTAMP (0));

Date

支持的范围 '1000-01-01' to '9999-12-31',MySQL以'YYYY-MM-DD'格式显示Date类型,但允许使用字符串或数字将值赋给DATE列。字面常量转换规则如下:

(1)格式为'YYYY-MM-DD'或'YY-MM-DD'的字符串。任何标点字符都可以用作日期部分之间的分隔符。例如,'2012-12-31'、'2012/12/31'、'2012^12^31'和'2012@12@31'是等价的。

(2)格式为'YYYYMMDD'或'YYMMDD'的没有分隔符的字符串,只要该字符串具有日期的意义。例如,'20070523'和'070523'被解释为'2007-05-23',但'071332'是非法的(它有无意义的月和日部分),会变成'0000-00-00'。

(3)作为YYYYMMDD或YYMMDD格式的数字,只要该数字作为日期有意义。例如,19830905和830905被解释为“1983-09-05”。

DATETIME和DATETIME(fsp)

DATETIME类型是Date和Time的组合。支持的范围从'1000-01-01 00:00:00.000000' 到'9999-12-31 23:59:59.999999'。MySQL以'YYYY-MM-DD hh:mm:ss[.fraction]'的格式显示DATETIME类型。但允许使用字符串或数字向DATETIME列赋值。字面常量转换规则如下:

表中的任何TIMESTAMP或DATETIME列都可以具有自动初始化和更新属性。

(1)格式为“YYYY-MM-DD hh:mm:ss”或“YY-MM-DD hh:mm:ss”字符串。任何标点字符都可以用作日期部分或时间部分之间的分隔符。例如,'2012-12-31 11:30:45'、'2012^12^31 11+30+45'、'2012/12/31 11*30*45'和'2012@12@31 11^30^45'是等价的。

(2)在日期和时间部分和小数秒部分之间识别的唯一分隔符是小数点。

(3)日期和时间部分可以用T而不是空格分隔。例如,'2012-12-31 11:30:45' '2012-12-31 t11:30:45 '是等价的。

(4)格式为'YYYYMMDDhhmmss'或'YYMMDDhhmmss'无分隔符的字符串,只要该字符串具有日期的意义。例如,'20070523091528'和'070523091528'被解释为'2007-05-23 09:15:28',但'071122129015'是非法的(它有一个无意义的分钟部分),会变成'0000-00-00 00:00:00'。

(5)YYYYMMDDhhmmss或YYMMDDhhmmss格式的数字,只要该数字作为日期有意义。例如,19830905132800和830905132800被解释为“1983-09-05 13:28:00”。

TIMESTAMP和TIMESTAMP[(fsp)]

支持的范围'1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' UTC(UTC指的是世界标准时间),TIMESTAMP值存储为自纪元('1970-01-01 00:00:00' UTC)以来的秒数,但是它不能表示'1970-01-01 00:00:00',因为这表示从纪元开始的0秒,值0保留用于表示“0000-00-00 00:00:00”,即“零”的TIMESTAMP值。TIMESTAMP的字面常量同DATETIME。

上面date_time_test表ts列是TIMESTAMP,一旦创建后,我们发现ts列默认值为当前TIMESTAMP,而且可自动更新为当前时间戳,如图: 

支持的范围'1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' UTC(UTC指的是世界标准时间),TIMESTAMP值存储为自纪元('1970-01-01 00:00:00' UTC)以来的秒数,但是它不能表示'1970-01-01 00:00:00',因为这表示从纪元开始的0秒,值0保留用于表示“0000-00-00 00:00:00”,即“零”的TIMESTAMP值。TIMESTAMP的字面常量同DATETIME。

上面date_time_test表ts列是TIMESTAMP,一旦创建后,我们发现ts列默认值为当前TIMESTAMP,而且可自动更新为当前时间戳,如图:

向date_time_test表中插入一条数据,如下: 

?
1 insert date_time_test(t) values ( '00:00:01' );

查看数据,如图:

发现ts列自动更新为插入数据时的时间戳。

更新当前数据,如图:

?
1 update date_time_test set t= '00:00:02' where t= '00:00:01' ;

再次查看时间戳,如图:

 ts列已经更新为当前时间戳。这和MySQL的系统变量explicit_defaults_for_timestamp有关,

?
1 show variables like 'explicit_defaults_for_timestamp' ;

如图:

explicit_defaults_for_timestamp处于关闭状态,此时,插入数据时,如果ts没有指定值,会被初始化为默认值,更新数据时,ts列同时更新。 explicit_defaults_for_timestamp处于打开状态,不会被初始化为默认,也不具备自动更新为当前时间戳。也可以对TIMESTAMP和DATETIME如下显示定义默认值和自动更新:

?
1 2 3 4 CREATE TABLE t1 (    ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,    dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );

TIME和TIME(fsp)

支持的范围'-838:59:59.000000' to '838:59:59.000000',MySQL以'hh:mm:ss[.fraction]'格式显示TIME,但允许使用字符串或数字向TIME列赋值。TIME类型这个时间范围表明它不仅可以表示一天的时间,也可以表示经过的时间或两个事件之间的时间间隔。字面常量转换规则如下:

(1)MySQL将带有冒号的TIME值缩写为一天中的时间,例如:'11:12'表示'11:12:00',而不是 '00:11:12';

(2)MySQL将没有冒号的TIME缩写值最右边的两个数字看成秒,例如:'1112'和1112都表示'00:11:12'

(3)在时间部分和小数秒部分之间识别的唯一分隔符是小数点;

(4)默认情况下,位于TIME范围之外但在其他方面有效的值将被裁剪到该范围中最近的端点。例如,'-850:00:00'和'850:00:00'会转换为'-838:59:59'和'838:59:59'。无效的TIME值将被转换为“00:00:00”。注意,由于'00:00:00'本身是一个有效的TIME值,因此无法从存储在表中的'00:00:00'值判断原始值是'00:00:00'还是无效的。

YEAR和YEAR(4)

支持的范围1901到2155,或者0000。MySQL以YYYY格式显示YEAR。但允许使用字符串或数字向YEAR列赋值。

SUM()和AVG()聚合函数不能处理时间值。要解决这个问题,请将其转换为数字,然后执行聚合操作,最后将其转换回时间值。例子:

?
1 2 SELECT SEC_TO_TIME( SUM (TIME_TO_SEC(time_col))) FROM tbl_name; SELECT FROM_DAYS( SUM (TO_DAYS(date_col))) FROM tbl_name;

YEAR接受如下格式的输入:

(1)4位字符串'1901' 到 '2155';

(2)4位数字1901 到 2155;

(3)1或者2位字符串'0' 到 '99',MySQL将'0'到'69'转化为2000到2069,将'70'到'99'转化为1970到1999;

(4)1或者2位数字0到 99,MySQL将1到69转换为YEAR值2001到2069,将70到99转化为YEAR值1970到1999,0转化为YEAR值0000。

注:

如果没有启用strict SQL模式,MySQL会将无效的YEAR值转换为0000。在严格SQL模式下,试图插入无效的YEAR值将产生错误。

总结如下:

  DATE TIME DATETIME TIMESTAMP YEAR
取值范围

'1000-01-01'

'9999-12-31'

'-838:59:59.000000'

'838:59:59.000000'

'1000-01-01 00:00:00.000000' 到

'9999-12-31 23:59:59.999999'

'1970-01-01 00:00:01.000000' UTC to

'2038-01-19 03:14:07.999999' UTC

1901

2155

或者0000

小数位精度 0 0~6 0~6 0~6 0
显示格式 YYYY-MM-DD hh:mm:ss[.fraction] YYYY-MM-DD hh:mm:ss[.fraction] YYYY-MM-DD hh:mm:ss[.fraction] YYYY
赋值 允许使用符合格式的字符串或数字赋值 允许使用符合格式的字符串或数字赋值 允许使用符合格式的字符串或数字赋值 允许使用符合格式的字符串或数字赋值 允许使用符合格式的字符串或数字赋值
小数位分隔符 只识别小数点. 只识别小数点. 只识别小数点.
日期分隔符 任何标点 任何标点 任何标点
时间分隔符 任何标点 任何标点 任何标点
无效值处理(strict mode关闭状态)

转化为

0000-00-00

转化为

0000-00-00 00:00:00

转化为

0000-00-00 00:00:00

转换为

00:00:00

转换为0000

以上为个人经验,希望能给大家一个参考,也希望大家多多支持服务器之家。

原文链接:https://blog.csdn.net/qq_31975227/article/details/89509078

可以去百度分享获取分享代码输入这里。
声明

1.本站遵循行业规范,任何转载的稿件都会明确标注作者和来源;2.本站的原创文章,请转载时务必注明文章作者和来源,不尊重原创的行为我们将追究责任;3.作者投稿可能会经我们编辑修改或补充。

【腾讯云】云服务器产品特惠热卖中
搜索
标签列表
    关注我们

    了解等多精彩内容