MySQL에서 날짜/시간, 날짜, 시간 및 문자열 간의 변환 및 비교

이 기사는 mysql에 대한 관련 지식을 제공하며, datatime, data, time 및 str 간의 변환 및 비교에 대한 관련 문제를 주로 소개합니다. 모두에게 도움이 되기를 바랍니다.

추천 학습: mysql 비디오 튜토리얼

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에는 시간 값을 나타내는 5가지 날짜 및 시간 데이터 유형이 있습니다:

(1) DATE

(2)TIME

(3)DATETIME

(4) TIMESTAMP

( 5) YEAR

날짜 및 시간 데이터 유형 구문

MySQL에서는 소수 초를 포함하는 날짜 및 시간 데이터 유형을 정의하기 위해 최대 마이크로초(소수점 6자리)의 정밀도로 TIME, DATETIME 및 TIMESTAMP에 소수 초를 사용할 수 있습니다. . 열에서는

type_name(fsp)을 사용합니다.

여기서 type_name은 TIME, DATETIME 또는 TIMESTAMP이고 fsp는 소수 초 정밀도입니다. fsp 값은 0~6 사이여야 합니다. 0 값은 no를 의미합니다. 생략할 경우 기본 정밀도는 0입니다.

예:

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

Date

지원되는 범위는 '1000-01-01' ~ '9999-12-31'이며 MySQL은 ' YYYY-MM-DD' 형식은 날짜 유형을 표시하지만 문자열이나 숫자를 사용하여 DATE 열에 값을 할당할 수 있습니다. 리터럴 상수 변환 규칙은 다음과 같습니다.

(1) ' 형식의 문자열 YYYY-MM-DD' 또는 'YY-MM-DD'. 모든 구두점 문자를 날짜 부분 사이에 구분 기호로 사용할 수 있습니다(예: '2012-12-31', '2012/12/31'). 2012@12@31'은 동일합니다.

(2) 'YYYYMMDD' 또는 'YYMMDD' 형식의 문자열(예: '20070523' 및 '070523') '2007-05-23'으로 해석되지만 '071332'는 불법이며(의미 없는 월 및 일 부분이 있음) YYYYMMDD 또는 YYMMDD 형식의 숫자로 '0000-00-00'이 됩니다. , 예를 들어 19830905 및 830905는 "1983-09-05"로 해석됩니다.

DATETIME 및 DATETIME(fsp)

DATETIME 유형은 '1000- 01-01 00:00:00.000000' ~ '9999-12-31 23:59:59.999999' MySQL은 DATETIME 유형을 'YYYY-MM-DD hh:mm:ss[.fraction]' 형식으로 표시합니다. 그러나 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 ~ '2038-01-19 03:14:07.999999' UTC (UTC는 세계 표준을 의미함) time), TIMESTAMP 값은 epoch('1970-01-01 00:00:00' UTC) 이후의 초 수로 저장되지만 '1970-01-01 00:00:00'을 나타낼 수 없습니다. 에포크로부터 0초에 값 0은 "0000-00-00 00:00:00", 즉 "0"을 나타내는 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表中插入一条数据，如下： 

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

查看数据，如图：

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

更新当前数据，如图：

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

再次查看时间戳，如图：

 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值将产生错误。

总结如下：

날짜 구분 기호 시간 구분 기호잘못된 값 처리(엄격 모드 꺼짐)으로 변환 변환 to Convert 변환 추천 학습:

	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[. 분수 ]	YYYY-MM-DD hh:mm:ss[.fraction]	YYYY-MM-DD hh:mm:ss[.fraction]	YYYY
Assignment	을 사용하면 다음을 준수하는 문자를 사용할 수 있습니다. 형식에 맞게 문자열 또는 숫자 할당을 허용합니다	형식에 맞는 문자열 또는 숫자 할당을 허용합니다	형식에 맞는 문자열이나 숫자 할당을 허용합니다	형식에 맞는 문자열이나 숫자 할당을 허용합니다. the format	형식에 맞는 문자열 또는 숫자 할당을 허용합니다. 숫자 할당
소수점 구분 기호	없음	소수점만 인식됩니다.	소수점만 인식됩니다.	없음
모든 구두점	없음	모든 구두점	모든 구두점	없음
없음	모든 구두점	모든 구두점	모든 구두점	None
	0000-00-00	0000-00-00 00:00:00 으로 변환	0000-00-00 00:00:00	00:00:00 to 0000

mysql 비디오

위 내용은 MySQL에서 날짜/시간, 날짜, 시간 및 문자열 간의 변환 및 비교의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!