MySQL の datetime、date、time、str 間の変換と比較

この記事では、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 の時刻値を表す日付と時刻のデータ型MySQL は以下の 5 つです:

(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」から「9999-12-31」です。MySQL は日付タイプを「YYYY-MM-DD」形式で表示しますが、使用は許可されます。 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」になります。 YYYYMMDD または YYMMDD 形式 (数値が有効である限り) 日付は意味を持ちます。たとえば、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 は協定世界時を指します)、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，而且可自动更新为当前时间戳，如图：

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

总结如下：

#10 進精度00~60~60~60表示形式YYYY-MM-DDhh:mm:ss[.fraction]YYYY-MM-DD hh:mm:ss[.fraction]YYYY-MM-DD hh:mm:ss[.fraction]YYYY#割り当て小数点区切り文字日付区切り文字# #任意の句読点任意の句読点なし時刻区切り文字なし任意の句読点任意の句読点任意の句読点なし無効な値の処理 (厳密モードはオフ)0000-00-00 00:00:00mysql ビデオ チュートリアル

	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
形式に準拠した文字列または数値の割り当ての使用を許可します	形式に準拠した文字列または数値の割り当ての使用を許可します	許可形式に準拠した文字の使用 文字列または数値の割り当て	形式に準拠した文字列または数値の割り当ての使用を許可します	形式に準拠した文字列または数値の割り当ての使用を許可しますフォーマット
なし	小数点のみが認識されます。	小数点のみが認識されます。	小数点のみが認識されます。	なし
任意の句読点	なし
は	0000-00-00 ## に変換 # は	に変換されます ## は 0000-00-00 00:00:00	に変換されます 00:00:00	は 0000 に変換されます	推奨学習:

以上がMySQL の datetime、date、time、str 間の変換と比較の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。