Penukaran dan perbandingan antara tarikh, tarikh, masa dan str dalam MySQL

Artikel ini membawakan anda pengetahuan yang berkaitan tentang mysql terutamanya isu yang berkaitan tentang penukaran dan perbandingan antara masa data, data, masa dan str. Mari kita lihat bersama-sama semua orang.

Pembelajaran yang disyorkan: tutorial video mysql

Penukaran dan perbandingan antara datetime, tarikh, masa dan 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');

Jenis data tarikh dan masa MySQL (TARIKH, MASA, DATETIME, TIMESTAMP dan TAHUN

Artikel ini adalah berdasarkan MySQL versi 5.7.

Jenis data tarikh dan masa yang mewakili nilai masa ​​dalam MySQL Terdapat lima yang berikut:

(1) DATE

(2) TIME

(3) DATETIME

(4) TIMESTAMP

(5) TAHUN

Sintaks jenis data tarikh dan masa

MySQL membenarkan penggunaan pecahan saat untuk TIME, DATETIME dan TIMESTAMP, dengan ketepatan sehingga mikrosaat (6 digit) Perpuluhan). Untuk menentukan lajur yang mengandungi pecahan saat jenis data tarikh dan masa, gunakan

type_name(fsp),

dengan type_name ialah TIME, DATETIME atau TIMESTAMP, dan fsp ialah ketepatan saat pecahan, Nilai fsp, jika diberi, mestilah antara 0 dan 6. Nilai 0 bermakna tiada bahagian perpuluhan Jika diabaikan, ketepatan lalai ialah 0. Contohnya:

<.>Tarikh

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

Julat yang disokong ialah '1000-01-01' hingga '9999-12-31', MySQL memaparkan jenis Tarikh dalam format 'YYYY-MM-DD', tetapi rentetan dibenarkan Atau nombor untuk menetapkan nilai pada lajur DATE Peraturan penukaran malar literal adalah seperti berikut:

(1) Rentetan dalam format 'YYYY-MM-DD' atau 'YY-. MM-DD' Sebarang aksara tanda baca boleh digunakan sebagai pemisah antara bahagian tarikh Contohnya, '2012-12-31', '2012@12@31' adalah bersamaan 🎜>(2) Rentetan tanpa pembatas dalam format 'YYYYMMDD' atau 'YYMMDD', asalkan rentetan itu mempunyai maksud tarikh Contohnya, '20070523' dan '070523' ditafsirkan sebagai '2007-05-. 23', tetapi '071332' adalah haram (ia mempunyai bahagian bulan dan hari yang tidak bermakna) dan akan menjadi '0000-00-00' (3) sebagai nombor dalam format YYYYMMDD atau YYMMDD, Selagi nombor itu masuk akal sebagai tarikh . Contohnya, 19830905 dan 830905 ditafsirkan sebagai "1983-09-05". Julat yang disokong ialah daripada '1000-01-01 00:00:00.000000' hingga '9999-12-31 23:59:59.999999'. MySQL memaparkan jenis DATETIME dalam format 'YYYY-MM-DD hh:mm:ss[.fraction]'. Walau bagaimanapun, ia dibenarkan menggunakan rentetan atau nombor untuk memberikan nilai kepada lajur DATETIME. Peraturan penukaran literal adalah seperti berikut:

Mana-mana lajur TIMESTAMP atau DATETIME dalam jadual boleh mempunyai ciri permulaan automatik dan kemas kini.

(1) Formatnya ialah rentetan "YYYY-MM-DD hh:mm:ss" atau "YY-MM-DD hh:mm:ss". Sebarang aksara tanda baca boleh digunakan sebagai pemisah antara bahagian tarikh atau bahagian masa. Contohnya, '2012-12-31 11:30:45', '2012^12^31 11 30 45', '2012/12/31 11*30*45' dan '2012@12@31 11^30^ 45' ' adalah bersamaan.

(2) Satu-satunya pemisah yang dikenali antara bahagian tarikh dan masa dan bahagian saat pecahan ialah titik perpuluhan.

(3) Bahagian tarikh dan masa boleh dipisahkan oleh T dan bukannya ruang. Contohnya, '2012-12-31 11:30:45' '2012-12-31 t11:30:45' adalah bersamaan.

(4) Rentetan dalam format 'YYYYMMDDhhmmss' atau 'YYMMDDhhmmss' tanpa pembatas, asalkan rentetan itu mempunyai maksud tarikh. Contohnya, '20070523091528' dan '070523091528' ditafsirkan sebagai '2007-05-23 09:15:28', tetapi '071122129015' adalah haram (ia mempunyai bahagian minit yang tidak bermakna) dan menjadi '0000-00 :00:00'.

(5) Nombor dalam format YYYYMMDDhhmmss atau YYMMDDhhmmss, asalkan nombor itu masuk akal sebagai tarikh. Contohnya, 19830905132800 dan 830905132800 ditafsirkan sebagai "1983-09-05 13:28:00".

TIMESTAMP dan TIMESTAMP[(fsp)]

Julat yang disokong '1970-01-01 00:00:01.000000' UTC hingga '2038-01-19 03 : 14:07.999999' UTC (UTC merujuk kepada Universal Coordinated Time), nilai TIMESTAMP disimpan sebagai bilangan saat sejak zaman ('1970-01-01 00:00:00' UTC), tetapi ia tidak boleh mewakili '1970-01 -01 00:00:00', memandangkan ini mewakili 0 saat dari zaman, nilai 0 dikhaskan untuk nilai TIMESTAMP yang mewakili "0000-00-00 00:00:00", iaitu "sifar". Pemalar literal TIMESTAMP adalah sama dengan DATETIME.

Lajur ts bagi jadual date_time_test di atas ialah TIMESTAMP Setelah dibuat, kami mendapati bahawa nilai lalai lajur ts ialah TIMESTAMP semasa dan boleh dikemas kini secara automatik kepada cap masa semasa, seperti yang ditunjukkan dalam rajah:

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

总结如下：

Sebarang tanda bacaTiadaPemprosesan nilai tidak sah (mod ketat dimatikan) ditukar kepada 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
Ketepatan perpuluhan	0	0~6	0~6	0~6	0
Format paparan	YYYY-MM-DD	hh:mm:ss[.fraction]	YYYY-MM-DD hh:mm:ss[.fraction]	YYYY-MM-DD hh:mm:ss[.fraction]	YYYY
Tugasan	Membenarkan penggunaan rentetan atau tugasan berangka yang mematuhi format	Membenarkan penggunaan rentetan atau tugasan berangka yang mematuhi format	Membenarkan penggunaan aksara yang mematuhi format String atau tugasan angka	Membenarkan tugasan rentetan atau angka yang mematuhi format	Membenarkan tugasan rentetan atau angka yang mematuhi format
Pemisah tempat perpuluhan	Tiada	Hanya titik perpuluhan yang dikenali.	Hanya titik perpuluhan yang dikenali.	Hanya titik perpuluhan diiktiraf.	Tiada
Pemisah tarikh	Sebarang tanda baca	Tiada	Sebarang tanda baca	Sebarang tanda baca	Tiada
Pemisah masa	Tiada	Sebarang tanda baca	Sebarang tanda baca 🎜>
	ditukar kepada 0000-00-00	ditukar kepada 0000-00-00 00:00:00	ditukar kepada 0000-00-00 00:00:00	ditukar kepada 00:00:00

Pembelajaran yang disyorkan:

tutorial video mysql

Atas ialah kandungan terperinci Penukaran dan perbandingan antara tarikh, tarikh, masa dan str dalam MySQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!