MySQLで日付と時刻を照会する方法

序文：

プロジェクト開発では、一部のビジネステーブルフィールドで日付と時刻の型が使用されることが多く、そのようなフィールドに対する後続のクエリも必要になります。日付と時刻のクエリには多くの要件があります。この記事では、日付と時刻のフィールドの標準化されたクエリ方法について簡単に説明します。

1. 日付と時刻の種類の概要

MySQL でサポートされている日付と時刻の型は、DATETIME、TIMESTAMP、DATE、TIME、および YEAR です。これらの型の比較は次のとおりです。

日付と時刻のフィールドタイプを選択する場合は、ストレージ要件に基づいて適切なタイプを選択します。

2. 日付と時刻に関する機能

日付と時刻のフィールドを処理するための関数は多数あり、その一部はクエリでよく使用されます。以下では、関連するいくつかの関数の使用方法を紹介します。

CURDATE 関数と CURRENT_DATE 関数は同じ機能を持ち、現在のシステム日付値を返します。
2 つの関数 CURTIME と CURRENT_TIME は同じ機能を持ち、現在のシステム時刻の値を返します。
NOW() 関数と SYSDATE() 関数は同じで、現在のシステムの日付と時刻の値を返します。
UNIX_TIMESTAMP UNIX タイムスタンプ関数を取得し、UNIX タイムスタンプに基づいて符号なし整数を返します。
FROM_UNIXTIME は UNIX タイムスタンプを時刻形式に変換し、UNIX_TIMESTAMP の逆関数です。
TO_DAYS() は日付値を受け取り、西暦 0 年からの日数を返します。
DAY() 指定された日付または時刻の曜日を取得します。
DATE() 指定された日付または時刻の曜日を取得します。
TIME() 指定された日付または時刻の時刻を取得します。
MONTH 指定された日付の月を取得します。
WEEK 指定された日付の年の週番号を取得します。
YEAR 年を取得します。
QUARTER 日付の四半期の値を取得します。
DATE_ADD 関数と ADDDATE 関数は同じ機能を持ち、どちらも指定された時間間隔を日付に追加します。
DATE_SUB 関数と SUBDATE 関数は同じ機能を持ち、どちらも日付から指定された時間間隔を減算します。
ADDTIME 時間追加操作。元の時間に指定した時間を加算します。
SUBTIME 時間減算演算。元の時間から指定した時間を減算します。
DATEDIFF は 2 つの日付間の間隔を取得し、パラメーター 1 からパラメーター 2 を引いた値を返します。
DATE_FORMAT は指定された日付をフォーマットし、パラメータに基づいて指定された形式の値を返します。

使用例:

mysql> CURRENT_DATE、CURRENT_TIME、NOW() を選択します。
+--------------+--------------+---------------------+
| 現在の日付 | 現在の時刻 | 現在() |
+--------------+--------------+---------------------+
| 2020-06-03 | 15:09:37 | 2020-06-03 15:09:37 |
+--------------+--------------+---------------------+

mysql> TO_DAYS('2020-06-03 15:09:37') を選択します。
TO_DAYS('2020-06-03')-TO_DAYS('2020-06-01');
+--------------------------------+----------------------------------------------+
| TO_DAYS('2020-06-03 15:09:37') | TO_DAYS('2020-06-03')-TO_DAYS('2020-06-01') |
+--------------------------------+----------------------------------------------+
| 737944 | 2 |
+--------------------------------+----------------------------------------------+

mysql> MONTH('2020-06-03'),WEEK('2020-06-03'),YEAR('2020-06-03') を選択します。
+---------------------+--------------------+--------------------+
| 月('2020-06-03') | 週('2020-06-03') | 年('2020-06-03') |
+---------------------+--------------------+--------------------+
| 6 | 22 | 2020 |
+---------------------+--------------------+--------------------+

# DATEDIFF(date1, date2)は開始時刻date1と終了時刻date2の間の日数を返しますmysql> SELECT DATEDIFF('2017-11-30','2017-11-29') AS COL1,
  -> DATEDIFF('2017-11-30','2017-12-15') を col2 として計算します。
+------+------+
| 列1 | 列2 |
+------+------+
| 1 | -15 |
+------+------+

3. 日付と時刻フィールドの標準化されたクエリ

上記の内容はすべて、クエリのニーズに合わせて準備されています。プロジェクトの要件では、日付や時間の条件に基づいてフィルタークエリが実行されることがよくあります。場合によっては、このような要件は異なります。日付と時刻のフィールドでクエリを記述する方法を学びましょう。

まず、クエリをより正確にするために、仕様に従ってデータを挿入する必要があります。たとえば、年は 4 桁を使用し、日と月は妥当な範囲内でなければなりません。テストの便宜上、テーブルを作成していくつかのデータを挿入します。

テーブル `t_date` を作成します (
`increment_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自動増分主キー',
`year_col` YEAR NOT NULL COMMENT '年',
`date_col` 日付 NOT NULL COMMENT '日付',
`time_col` 時間 NOT NULL コメント 'time',
`dt_col` datetime NOT NULL COMMENT 'datetime time',
`create_time` タイムスタンプ NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '作成時刻',
 主キー (`increment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='time test table';


# 日付と時刻の両方に現在の日付または時刻を選択します INSERT INTO t_date (year_col,date_col,time_col,dt_col,create_time) VALUES 
(年(現在()),日付(現在()),時刻(現在()),現在(),現在());


# 指定した日付または時刻を挿入する INSERT INTO t_date ( `year_col`, `date_col`, `time_col`, `dt_col`, `create_time` )
価値観
  ( 2020, '2020-06-03', '09:00:00', '2020-06-03 10:04:04', '2020-06-03 10:04:04' ),
  ( 2020, '2020-05-10', '18:00:00', '2020-05-10 16:00:00', '2020-05-10 16:00:00' ),
  ( 2019, '2019-10-03', '16:04:04', '2019-10-03 16:00:00', '2019-10-03 16:00:00' ),
  ( 2018, '2018-06-03', '16:04:04', '2018-06-03 16:00:00', '2018-06-03 16:00:00' ),
  ( 2000, '2000-06-03', '16:04:04', '2000-06-03 08:00:00', '2000-06-03 08:00:00' ),
  ( 2008, '2008-06-03', '16:04:04', '2008-06-03 08:00:00', '2008-06-03 08:00:00' ),
  ( 1980, '1980-06-03', '16:04:04', '1980-06-03 08:00:00', '1980-06-03 08:00:00' );

上記のテストテーブルのデータに基づいて、いくつかの一般的なクエリステートメントの記述方法を学習しましょう。

日付や時間などで検索します。

year_col = 2020 である t_date から * を選択します。
date_col = '2020-06-03' の場合、t_date から * を選択します。
dt_col = '2020-06-03 16:04:04' である t_date から * を選択します。

日付または時間範囲で検索

date_col > '2018-01-01' となる t_date から * を選択します。
t_date から * を選択します。ここで、dt_col >= '2020-05-01 00:00:00' かつ dt_col < '2020-05-31 23:59:59' です。
t_date から * を選択し、dt_col が '2020-05-01 00:00:00' から '2020-05-31 23:59:59' の間であるものを選択します。

今月のデータを照会する

# 今月の create_time のデータを照会します。 select * from t_date where DATE_FORMAT(create_time, '%Y-%m' ) = DATE_FORMAT( CURDATE( ) , '%Y-%m' );

過去数日間のデータを照会する

# date_col に基づいて過去 7 日間または 30 日間のデータを照会します。SELECT * FROM t_date where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(date_col);
SELECT * FROM t_date where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(date_col);

その他のクエリ記述の種類

# 今日のデータをクエリする SELECT * FROM t_date WHERE TO_DAYS(create_time) = TO_DAYS(NOW());

# 特定の月のデータを照会する SELECT * FROM t_date WHERE DATE_FORMAT(create_time, '%Y-%m')='2020-06';

# 特定の年のデータを照会する SELECT * FROM t_date WHERE DATE_FORMAT(create_time, '%Y') = 2020;
SELECT * FROM t_date WHERE YEAR(create_time) = 2020;

# 日付範囲に従ってデータをクエリし、並べ替えます SELECT * FROM t_date WHERE DATE_FORMAT(create_time, '%Y') BETWEEN '2018' AND '2020' ORDER BY create_time DESC;

要約:

この記事では、まず日付と時刻のフィールドについて説明し、次に関連する関数の使用方法を説明し、最後によく使用されるクエリメソッドをいくつか示します。このコンテンツがお役に立てば幸いです。実際には、クエリはより複雑になる場合があります。特にデータ量が多い場合、時間フィールドに基づくクエリは遅くなることがよくあります。この場合、インデックスの作成にも注意する必要があります。整数クエリとフィルタリングが高速になるため、時間フィールドをタイムスタンプに変換するのが最適です。また、日付と時刻のフィールドで計算を実行したり、プログラムで実行できる操作をデータベースレベルで実行したりしないように注意することもお勧めします。

上記は、MySQL で日付と時刻を照会する方法の詳細です。MySQL で日付と時刻を照会する方法の詳細については、123WORDPRESS.COM の他の関連記事に注目してください。

以下もご興味があるかもしれません: