MySQL の日付フォーマットと複雑な日付範囲クエリ

1 現在の日付 + 時刻 (日付 + 時刻) を取得する関数: now()
2 現在の日付 + 時刻 (日付 + 時刻) を取得する関数: sysdate()
3 現在のタイムスタンプを取得する関数: current_timestamp、current_timestamp()
4 現在の日付（date）を取得する関数: curdate()

mysql 日付時刻変換関数

1 日付時刻変換文字列形式
2 文字列を日付時刻に変換する
3 (日付、曜日) 変換関数
4 (時間、秒) 変換機能
5. 日付と時刻の関数を組み合わせる
6 Unix タイムスタンプと日付変換関数

mysql 日付と時刻の計算関数

1 日付に時間間隔を追加する: date_add()
2 日付から時間間隔を減算する: date_sub()
3 日付と時刻の減算関数: datediff(date1,date2), timediff(time1,time2)
4 タイムスタンプの変換、増加、減少機能:

タイムゾーン変換関数

convert_tz(dt,from_tz,to_tz) 関数
タイムゾーンの変換は、date_add、date_sub、timestampadd を通じても実行できます。

序文

最近、私は MySQL で複雑な日付クエリを伴うプロジェクトに取り組んでいます。日付クエリは、開始日から終了日までの間隔情報のクエリ、日付が有効な日付より小さい情報のクエリ、今日の日付、明日の日付のクエリ、比較の実行など、データベースクエリでは実際に非常に一般的です。

クエリの使用シナリオ例

時間間隔クエリ

クエリ、2021年6月1日から2021年8月25日までのデータ

選択
    *
から
    `dateTest` 
DATE_FORMAT(date,'%Y%m%d') は '20210601' から '20210825' までです

開始時間を含み、終了時間は含まない

ただし、DATE_FORMAT(date,'%Y%m') の記述方法ではインデックスを使用できないため、データ量が多い場合はクエリが非常に遅くなります。

クエリ日付と今日の時間の比較データ

t.CREATE_TIME>=curdate() の場合、t_user t から * を選択します。

DATEを使用して日付と時刻を時刻形式に変換します。

t_user t から * を選択 where DATE (t.CREATE_TIME)>=DATE (now())

一般的なサイクルタイムクエリ

-- 今日、t_user から fullName、addedTime を選択します。ここで、to_days(addedTime) <= to_days(now());
-- 昨日、t_user から fullName、addedTime を選択します。ここで、to_days(NOW()) - TO_DAYS(addedTime) <= 1;
-- 過去 7 日間で、t_user から fullName、addedTime を選択します。この場合、date_sub(CURDATE()、INTERVAL 7 DAY) <= DATE(addedTime) となります。
-- 過去 30 日間 SELECT fullName,addedTime FROM t_user where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(addedTime);
-- 今月 SELECT fullName,addedTime FROM t_user WHERE DATE_FORMAT( addedTime, '%Y%m' ) = DATE_FORMAT( CURDATE() , '%Y%m' );
-- 先月 SELECT fullName,addedTime FROM t_user WHERE PERIOD_DIFF( date_format( now() , '%Y%m' ) , date_format( addedTime, '%Y%m' ) ) =1;
-- 今四半期のデータを照会します。 select fullName,addedTime FROM t_user where QUARTER(addedTime)=QUARTER(now());
-- 前四半期のデータを照会します。 select fullName,addedTime FROM t_user where QUARTER(addedTime)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
-- 今年のデータを照会します。 select fullName,addedTime FROM t_user where YEAR(addedTime)=YEAR(NOW());
-- 前年のデータを照会します。 select fullName,addedTime FROM t_user where year(addedTime)=year(date_sub(now(),interval 1 year));
-- 今から 6 か月後のデータを照会します。select fullName,addedTime FROM t_user where addedTime between date_sub(now(),interval 6 month) and now();

-- 現在の週のデータを照会します SELECT fullName, addedTime FROM t_user WHERE YEARWEEK(date_format(addedTime,'%Y-%m-%d')) = YEARWEEK(now());
-- 先週のデータを照会します SELECT fullName, addedTime FROM t_user WHERE YEARWEEK(date_format(addedTime,'%Y-%m-%d')) = YEARWEEK(now())-1;
-- 先月のデータを照会します。 select fullName,addedTime FROM t_user where date_format(addedTime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m');
-- 現在の月のデータを照会します。 select fullName, addedTime FROM t_user where DATE_FORMAT(addedTime,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m');
date_format(addedTime,'%Y-%m')=date_format(now(),'%Y-%m') の場合、t_user から fullName、addedTime を選択します。

-- 指定された期間のデータを照会します。select fullName,addedTime FROM t_user where addedTime between '2017-1-1 00:00:00' and '2018-1-1 00:00:00';
fullName、addedTime を t_user から選択します。この場合、addedTime >='2017-1-1 00:00:00' かつ addedTime < '2018-1-1 00:00:00' です。

mysql 日付時刻関数

1 現在の日付 + 時刻 (日付 + 時刻) を取得する関数: now()

今すぐ選択();

+---------------------+
| 今() |
+---------------------+
| 2021-08-31 16:16:32 |
+---------------------+

2 現在の日付 + 時刻 (日付 + 時刻) を取得する関数: sysdate()

sysdate() 日付と時刻関数は now() と似ていますが、now() は実行の開始時に値を取得するのに対し、sysdate() は関数の実行時に動的に値を取得する点が異なります。理解するには次の例を見てください。

now()、sleep(3)、now() を選択します。
+---------------------+----------+---------------------+
| 今() | スリープ(3) | 今() |
+---------------------+----------+---------------------+
| 2021-08-31 16:20:12 | 0 | 2021-08-31 16:20:12 |
+---------------------+----------+---------------------+

前後の時間は変わっていないので、実行開始前に値が取得されていることがわかります。

sysdate() 日付と時刻の関数。一般的にはあまり使用されません。

3 現在のタイムスタンプを取得する関数: current_timestamp、current_timestamp()

current_timestamp、current_timestamp() を選択します。

+---------------------+---------------------+
| 現在のタイムスタンプ | 現在のタイムスタンプ() |
+---------------------+---------------------+
| 2021-08-31 16:27:26 | 2021-08-31 16:27:26 |
+---------------------+---------------------+

4 現在の日付（date）を取得する関数: curdate()

-- 2021-08-31
curdate() を選択します。

mysql 日付時刻変換関数

1 日付時刻変換文字列形式

日付/時刻を Str (日付/時刻を文字列に変換) 関数: date_format(date,format)、time_format(time,format)

date_format('2021-08-31 22:23:01', '%Y%m%d%H%i%s') を選択します。
+----------------------------------------------------+
| date_format('2021-08-31 22:23:01', '%Y%m%d%H%i%s') |
+----------------------------------------------------+
| 20210831222301 |
+----------------------------------------------------+

MySQL の日付と時刻の変換関数: date_format(date,format)、time_format(time,format) は、日付/時刻をさまざまな文字列形式に変換できます。これは、str_to_date(str,format) 関数の逆変換です。

2 文字列を日付時刻に変換する

MySQL Str to Date (文字列から日付) 関数: str_to_date(str, format)

str_to_date('08/31/2021', '%m/%d/%Y') を選択します -- 2021-08-31
str_to_date('08/31/2021' , '%m/%d/%y') を選択します -- 2021-08-31
str_to_date('08.31.2021', '%m.%d.%Y') を選択します -- 2021-08-31
str_to_date('08:09:30', '%h:%i:%s') を選択します -- 08:09:30
str_to_date('08.09.2021 08:31:30', '%m.%d.%Y %h:%i:%s') を選択します -- 2021-08-31 08:09:30

ご覧のとおり、str_to_date(str,format) 変換関数は、乱雑な文字列を日付形式に変換できます。あるいは、時間に変換することもできます。「フォーマット」については、MySQL のマニュアルを参照してください。

3 (日付、曜日) 変換関数

MySQL (日付、日) 変換関数: to_days(日付)、from_days(日)

to_days('0000-00-00'); を選択します -- 0
to_days('2021-08-31'); を選択します -- 738398

4 (時間、秒) 変換機能

MySQL (時間、秒) 変換関数: time_to_sec(時間)、sec_to_time(秒)

time_to_sec('01:00:05'); を選択します -- 3605
sec_to_time(3605); を選択します -- '01:00:05'

5. 日付と時刻の関数を組み合わせる

MySQL makedate(年、年曜日)、maketime(時、分、秒)

makedate(2021,31); を選択します -- '2021-01-31'
makedate(2021,32); を選択します --'2021-02-01'
maketime(12,15,30); を選択します -- '12:15:30'

makedate(2021,31) 年が前、日が後ろになります。デフォルトでは1月から始まり、遅れている日数が一定数を超えると新しい月に移動します。

6 Unix タイムスタンプ、日付変換関数

unix_timestamp()、

unix_timestamp(日付)、

from_unixtime(unix_timestamp)、

from_unixtime(unix_timestamp,フォーマット)

unix_timestamp() を選択; -- 1218290027
unix_timestamp('2008-08-08'); を選択します -- 1218124800
unix_timestamp('2008-08-08 12:30:00'); を選択します -- 1218169800

from_unixtime(1218290027); を選択します --'2008-08-09 21:53:47'
from_unixtime(1218124800); を選択します --'2008-08-08 00:00:00'
from_unixtime(1218169800); を選択します --'2008-08-08 12:30:00'

from_unixtime(1218169800, '%Y %D %M %h:%i:%s %x') を選択します -- '2008 8月8日 12:30:00 2008'

mysql 日付と時刻の計算関数

1 日付に時間間隔を追加する: date_add()

set @dt = now(); -- 変数を定義します select date_add(@dt, interval 1 day); -- 1 日を追加します
select date_add(@dt, interval 1 hour); -- 1時間追加します
date_add(@dt, 間隔 1 分) を選択します -- ...
date_add(@dt、間隔1秒)を選択します。
date_add(@dt、間隔1マイクロ秒)を選択します。
date_add(@dt、間隔1週間)を選択します。
date_add(@dt、間隔1か月)を選択します。
date_add(@dt、間隔1四半期)を選択します。
date_add(@dt、間隔1年)を選択します。

select date_add(@dt, interval -1 day); -- 1日を減算し、-1日を加えるとマイナス1日になります

MySQL の adddate()、addtime() 関数は date_add() に置き換えることができます。以下は、addtime() 関数を実装する date_add() の例です。

@dt = '2021-08-31 12:12:33' を設定します。
date_add(@dt、間隔 '01:15:30' 時間_秒) を選択します -- 2021-08-31 13:28:03
date_add(@dt、間隔 '1 01:15:30' day_second) を選択します -- 2021-09-01 13:28:03

2 日付から時間間隔を減算する: date_sub()

-- 2021-08-29 22:58:59
date_sub('2021-08-31 00:00:00'、間隔 '1 1:1:1' day_second) を選択します。

date_sub() 日付と時刻関数は date_add() と同じ使い方をするため、ここでは詳細には説明しません。

3 日付と時刻の減算関数: datediff(date1,date2)、timediff(time1,time2)

-- 2 つの日付から date1 - date2 を減算し、日数を返します。
datediff('2021-08-31', '2021-08-30'); を選択します -- 1
datediff('2021-08-31', '2021-08-20'); を選択します -- -11
-- 2 つの日付から time1 - time2 を減算し、時間差を返します。
timediff('2021-08-31 08:08:08', '2021-08-30 00:00:00'); を選択します -- 32:08:08
timediff('08:08:08', '00:00:00'); を選択します -- -08:08:08

timediff(time1, time2) 関数の 2 つのパラメータは同じ型である必要があります。

4 タイムスタンプの変換、増加、減少機能:

timestamp(date) -- 日付をタイムスタンプにする

timestamp(dt,time) -- dt + 時間

timestampadd(単位、間隔、日時式) --

timestampdiff(単位,datetime_expr1,datetime_expr2) --

タイムスタンプを選択('2021-08-31'); -- 2021-08-31 00:00:00
タイムスタンプを選択('2021-08-31 08:00:00', '01:01:01'); -- 2021-08-31 09:01:01
タイムスタンプを選択('2021-08-31 08:00:00', '10 01:01:01'); -- 2021-09-10 09:01:01

timestampadd(day, 1, '2021-08-31 08:00:00'); を選択します -- 2021-09-01 08:00:00
date_add('2021-08-31 08:00:00'、間隔1日)を選択します。 -- 2021-09-01 08:00:00

-- MySQL timestampadd() 関数は date_add() に似ています。
timestampdiff(year,'2021-08-31','2001-01-01') を選択します -- -20
timestampdiff(day,'2021-08-31','2001-01-01') を選択します -- --7547
タイムスタンプ差分(時間、'2021-08-31 12:00:00'、'2021-08-31 00:00:00')を選択します。 -- -12

datediff('2021-08-31 12:00:00', '2021-08-31 00:00:00'); を選択します -- 0

MySQL timestampdiff() 関数は、2 つの日付間の日数しか計算できない datediff() よりもはるかに強力です。

タイムゾーン変換関数

convert_tz(dt,from_tz,to_tz) 関数

-- 2021-08-31 04:00:00
convert_tz('2021-08-31 12:00:00', '+08:00', '+00:00') を選択します。

タイムゾーンの変換は、date_add、date_sub、timestampadd を通じても実行できます。

date_add('2021-08-31 12:00:00'、間隔 -8 時間) を選択します -- 2021-08-31 04:00:00
date_sub('2021-08-31 12:00:00'、間隔8時間)を選択します。 -- 2021-08-31 04:00:00
timestampadd(hour, -8, '2021-08-31 12:00:00'); を選択します -- 2021-08-31 04:00:00

これで、MySQL の日付フォーマットと複雑な日付範囲クエリに関するこの記事は終了です。MySQL の日付フォーマットと日付範囲クエリの詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

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