Conversion et comparaison entre datetime, date, heure et str dans MySQL

Cet article vous apporte des connaissances pertinentes sur mysql, qui présente principalement les problèmes liés à la conversion et à la comparaison entre datatime, data, time et str. Examinons-le ensemble, j'espère que cela sera utile à tout le monde.

Apprentissage recommandé : Tutoriel vidéo MySQL

Conversion et comparaison entre datetime, date, heure, 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');

Types de données date et heure MySQL (DATE, TIME, DATETIME, TIMESTAMP et YEAR

Cet article est basé sur MySQL version 5.7

Les types de données de date et d'heure représentant les valeurs d'heure dans MySQL sont les suivants :

(1)DATE

(2)TIME

(3)DATETIME

(4)TIMESTAMP

. ( 5) ANNÉE

Syntaxe des types de données date et heure

MySQL permet l'utilisation de fractions de seconde pour TIME, DATETIME et TIMESTAMP, avec une précision jusqu'à la microseconde (6 décimales) pour définir les types de données date et heure contenant des fractions. secondes, utilisez

type_name(fsp),

où type_name est TIME, DATETIME ou TIMESTAMP, et fsp est une précision en fractions de seconde. La valeur de fsp, si elle est donnée, doit être comprise entre 0 et 6. Une valeur de 0 signifie. pas de décimales. En cas d'omission, la précision par défaut est 0.

Par exemple :

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

Date

La plage prise en charge est '1000-01-01' à '9999-12-31', MySQL commence par 'AAAA-MM-JJ' Le format affiche un type Date, mais permet l'utilisation de chaînes ou de nombres pour attribuer des valeurs aux colonnes DATE. Les règles de conversion des constantes littérales sont les suivantes :

(1) Chaînes au format de. 'YYYY-MM-DD' ou 'YY-MM-DD' . N'importe quel caractère de ponctuation peut être utilisé comme séparateur entre les parties de date. Par exemple, '2012-12-31', '2012/12/31' et '2012@12@31' sont. Equivalent.

(2) Une chaîne sans délimiteurs au format 'AAAAMMJJ' ou 'AAMMJJ', tant que la chaîne a une signification de date. ' sont interprétés comme '2007-05-23', mais '071332' est illégal (il comporte des parties de mois et de jour sans signification) et deviendra '0000-00-00' sous la forme d'un nombre en AAAAMMJJ ou AAMMJJ. format, tant que ces nombres ont un sens en tant que dates. Par exemple, 19830905 et 830905 sont interprétés comme "1983-09-05". - 01-01 00:00:00.000000' à '9999-12-31 23:59:59.999999'. MySQL affiche le type DATETIME au format 'YYYY-MM-DD hh:mm:ss[.fraction]'. Cependant, il est permis d'utiliser des chaînes ou des nombres pour attribuer des valeurs aux colonnes DATETIME. Les règles de conversion littérale sont les suivantes :

Toute colonne TIMESTAMP ou DATETIME dans une table peut avoir des propriétés d'initialisation et de mise à jour automatiques.

(1) Le format est la chaîne "AAAA-MM-JJ hh:mm:ss" ou "AA-MM-JJ hh:mm:ss". N'importe quel caractère de ponctuation peut être utilisé comme séparateur entre les parties de date ou les parties d'heure. Par exemple, "2012-12-31 11:30:45", "2012^12^31 11+30+45", "2012/12/31 11*30*45" et "2012@12@31 11^ 30' ^45' est équivalent. (2) Le seul séparateur reconnu entre la partie date et heure et la partie fractions de seconde est le point décimal.

(3) Les parties date et heure peuvent être séparées par T au lieu d'espaces. Par exemple, « 2012-12-31 11:30:45 » « 2012-12-31 t11:30:45 » est équivalent.

(4) Une chaîne au format 'AAAAMMJJhhmmss' ou 'AAMMJJhhmmss' sans délimiteurs, tant que la chaîne a la signification de date. Par exemple, « 20070523091528 » et « 070523091528 » sont interprétés comme « 2007-05-23 09:15:28 », mais « 071122129015 » est illégal (il comporte une partie minutes dénuée de sens) et devient « 0000-00 » -00 00. :00:00'.

(5) Un nombre au format AAAAMMJJhhmmss ou AAMMJJhhmmss, à condition que le nombre ait un sens en tant que date. Par exemple, 19830905132800 et 830905132800 sont interprétés comme « 1983-09-05 13:28:00 ».

TIMESTAMP et TIMESTAMP[(fsp)]

Plage prise en charge de '1970-01-01 00:00:01.000000' UTC à '2038-01-19 03:14:07.999999' UTC (UTC fait référence à la norme mondiale time), la valeur TIMESTAMP est stockée sous forme de nombre de secondes depuis l'époque (« 1970-01-01 00:00:00 » UTC), mais elle ne peut pas représenter « 1970-01-01 00:00:00 » car cela signifie A 0 seconde de l'époque, la valeur 0 est réservée à la valeur TIMESTAMP représentant "0000-00-00 00:00:00", c'est-à-dire "zéro". La constante littérale de TIMESTAMP est la même que DATETIME.

La colonne ts de la table date_time_test ci-dessus est TIMESTAMP. Une fois créée, nous avons constaté que la valeur par défaut de la colonne ts est le TIMESTAMP actuel et peut être automatiquement mise à jour avec l'horodatage actuel, comme le montre la figure :

支持的范围'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值将产生错误。

总结如下：

Aucune Toute ponctuation Convertir en 0000-00-00Apprentissage recommandé :

	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
Précision décimale	0	0~6	0~6	0~6	0
Format d'affichage	AAAA-MM-JJ	hh:mm:ss[. fraction ]	AAAA-MM-JJ hh:mm:ss[.fraction]	AAAA-MM-JJ hh:mm:ss[.fraction]	AAAA
Assignment	permet l'utilisation de caractères conformes au format Affectation de chaîne ou numérique	Permet une affectation de chaîne ou numérique conforme au format	Autorise une affectation de chaîne ou numérique conforme au format	Autorise une affectation de chaîne ou numérique conforme au format	Autorise une affectation de chaîne ou numérique qui est conforme au format Attribution des nombres
Séparateur décimal	Aucun	Seuls les points décimaux sont reconnus	Seuls les points décimaux sont reconnus	Aucun. Séparateurs de dates	Toute ponctuation
Toute ponctuation	Toute ponctuation	Aucune		Séparateur de temps	Aucun
Toute ponctuation	ponctuation	Aucun		Traitement des valeurs invalides (strict mode désactivé)
	Convertir en 0000-00-00 00:00:00	Convertir en 0000-00-00 00h00 : 00	Convertir Convertir 00:00:00	en 0000	Tutoriel vidéo MySQL

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!