MySQL中datetime、date、time、str之间如何转换
发布时间：2023-06-15 14:45:22 所属栏目：MySql教程 来源：
导读：这篇文章主要介绍“MySQL中datetime、date、time、str之间如何转化”的相关知识，小编通过实际案例向大家展示操作过程，操作方法简单快捷，实用性强，希望这篇“MySQL中datetime、date、time、str之间
|
这篇文章主要介绍“MySQL中datetime、date、time、str之间如何转化”的相关知识，小编通过实际案例向大家展示操作过程，操作方法简单快捷，实用性强，希望这篇“MySQL中datetime、date、time、str之间如何转化”文章能帮助大家解决问题。 datetime、date、time、str之间的转化与比较 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。 例如: 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，而且可自动更新为当前时间戳，如图： MySQL中datetime、date、time、str之间如何转化 向date_time_test表中插入一条数据，如下： insert date_time_test(t) values('00:00:01'); 登录后复制 查看数据，如图： MySQL中datetime、date、time、str之间如何转化 发现ts列自动更新为插入数据时的时间戳。 更新当前数据，如图： update date_time_test set t='00:00:02' where t='00:00:01'; 登录后复制 再次查看时间戳，如图： MySQL中datetime、date、time、str之间如何转化 ts列已经更新为当前时间戳。这和MySQL的系统变量explicit_defaults_for_timestamp有关， show variables like 'explicit_defaults_for_timestamp'; 登录后复制 explicit_defaults_for_timestamp处于关闭状态，此时，插入数据时，如果ts没有指定值，会被初始化为默认值，更新数据时，ts列同时更新。 explicit_defaults_for_timestamp处于打开状态，不会被初始化为默认，也不具备自动更新为当前时间戳。也可以对TIMESTAMP和DATETIME如下显示定义默认值和自动更新： 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()聚合函数不能处理时间值。要解决这个问题，请将其转换为数字，然后执行聚合操作，最后将其转换回时间值。例子: 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值将产生错误。 （编辑：聊城站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐
