Conversion and comparison between datetime, date, time and str in MySQL

This article brings you relevant knowledge about mysql, which mainly introduces the related issues about the conversion and comparison between datatime, data, time and str. Let’s take a look at it together. ,I hope everyone has to help.

Recommended learning: mysql video tutorial

Conversion and comparison between 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 and time data types (DATE, TIME, DATETIME, TIMESTAMP and YEAR

This article is based on MySQL version 5.7.

The date and time data types representing time values ​​in MySQL are as follows Five:

(1)DATE

(2)TIME

(3)DATETIME

(4)TIMESTAMP

( 5) YEAR

Date and time data type syntax

MySQL allows the use of fractional seconds for TIME, DATETIME and TIMESTAMP, with precision up to microseconds (6 decimal places) .To define columns containing fractional seconds date and time data types, use

type_name(fsp),

where type_name is TIME, DATETIME, or TIMESTAMP, and fsp is fractional seconds precision, fsp's The value, if given, must be between 0 ~ 6. A value of 0 means no decimal part. If omitted, the default precision is 0.

For example:

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

Date

Supported range is '1000-01-01' to '9999-12-31', MySQL displays Date type in 'YYYY-MM-DD' format, but allows the use of strings or numbers to convert the value Assigned to the DATE column. The literal constant conversion rules are as follows:

(1) A string in the format of 'YYYY-MM-DD' or 'YY-MM-DD'. Any punctuation character can be used as the date part Separator between. For example, '2012-12-31', '2012/12/31', '2012^12^31' and '2012@12@31' are equivalent.

(2) A string without delimiters in the format of 'YYYYMMDD' or 'YYMMDD', as long as the string has the meaning of date. For example, '20070523' and '070523' are interpreted as '2007-05-23', but '071332' is illegal (it has meaningless month and day parts) and becomes '0000-00-00'.

(3) As a number in YYYYMMDD or YYMMDD format, as long as the number is Dates are meaningful. For example, 19830905 and 830905 are interpreted as "1983-09-05".

DATETIME and DATETIME(fsp)

DATETIME types are Date and Time The combination. The supported range is from '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'. MySQL displays the DATETIME type in the format 'YYYY-MM-DD hh:mm:ss[.fraction]'. However, it is allowed to use strings or numbers to assign values ​​to DATETIME columns. The literal conversion rules are as follows:

Any TIMESTAMP or DATETIME column in the table can have automatic initialization and update properties.

(1) The format is "YYYY-MM-DD hh:mm:ss" or "YY-MM-DD hh:mm:ss" string. Any punctuation character can be used as a separator between date parts or time parts. For example, '2012-12-31 11:30:45', '2012^12^31 11 30 45', '2012/12/31 11*30*45' and '2012@12@31 11^30^45' ' are equivalent.

(2) The only separator recognized between the date and time part and the fractional seconds part is the decimal point.

(3) The date and time parts can be separated by T instead of spaces. For example, '2012-12-31 11:30:45' '2012-12-31 t11:30:45' is equivalent.

(4) A string in the format of 'YYYYMMDDhhmmss' or 'YYMMDDhhmmss' without delimiters, as long as the string has the meaning of date. For example, '20070523091528' and '070523091528' are interpreted as '2007-05-23 09:15:28', but '071122129015' is illegal (it has a meaningless minutes part) and becomes '0000-00' -00 00:00:00'.

(5) A number in YYYYMMDDhhmmss or YYMMDDhhmmss format, as long as the number makes sense as a date. For example, 19830905132800 and 830905132800 are interpreted as "1983-09-05 13:28:00".

TIMESTAMP and TIMESTAMP[(fsp)]

Supported range '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03 :14:07.999999' UTC (UTC refers to Universal Coordinated Time), TIMESTAMP value is stored as the number of seconds since the epoch ('1970-01-01 00:00:00' UTC), but it cannot represent '1970-01 -01 00:00:00', since this represents 0 seconds from the epoch, the value 0 is reserved for the TIMESTAMP value that represents "0000-00-00 00:00:00", i.e. "zero". The literal constant of TIMESTAMP is the same as DATETIME.

The ts column of the date_time_test table above is TIMESTAMP. Once created, we find that the default value of the ts column is the current TIMESTAMP and can be automatically updated to the current timestamp, as shown in the 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值将产生错误。

总结如下：

is converted to 0000

	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
Decimal precision	0	0~6	0~6	0~6	0
Display format	YYYY-MM-DD	hh:mm:ss[.fraction]	YYYY-MM-DD hh:mm:ss[.fraction]	YYYY-MM-DD hh:mm:ss[.fraction]	YYYY
Assignment	Allows the use of string or numeric assignment that conforms to the format	Allows the use of string or numeric assignment that conforms to the format	Allows the use of characters that conform to the format String or number assignment	Allows the use of string or number assignment that conforms to the format	Allows the use of string or number assignment that conforms to the format
Decimal places Separator	None	Only decimal points are recognized.	Only decimal points are recognized.	Only decimal points are recognized.	None
Date separator	Any punctuation	None	Any punctuation	Any punctuation	None
Time separator	None	Any punctuation	Any punctuation	Any punctuation	None
Invalid value processing (strict mode off)	is converted to 0000-00-00	is converted to 0000-00-00 00:00:00	## is converted to 0000-00-00 00:00:00	is converted to 00:00:00

Recommended learning:

mysql video tutorial

The above is the detailed content of Conversion and comparison between datetime, date, time and str in MySQL. For more information, please follow other related articles on the PHP Chinese website!