日付と時刻は重要であり、物事の順序を保つのに役立ち、あらゆるソフトウェア操作に不可欠な要素である。

異なるタイムゾーンでの作業、日付の足し算・引き算など、データベースでの効率的な使用は、時に混乱を招くことがあります。

データベース内の日付/時刻を簡単に扱い、管理するための様々なMySQL関数を学びます。

使用タイムゾーン

標準化を維持するために、日付/時刻はUTCタイムゾーンのみを使用する必要があります。MySQLデータベースへの接続を確立するたびに、タイムゾーンをUTCに切り替える必要がありますが、これは次のSQLステートメントで行うことができます。

SET TIME_ZONE = '+0:00'

すべての日付がUTCで保存されるようになったため、常に自分が何を使っているのかがわかり、よりシンプルでわかりやすくなりました。

必要であれば、便利な convertu TZ() MySQL 関数を使用して、任意の datetime/timestamp 値のタイムゾーンを簡単に変換することができます。まずオフセットですが、例えば北米西海岸のPSTはUTC-08:00なので、これを利用します。

SELECT CONVERT_TZ('2021-02-04 21:47:23', '+0:00', '-8:00');

結果は2021-02-04 13:47:23となり、まさに正解。CONVERTu TZ() に渡される 3 つのパラメータは、最初の開始日時/タイムスタンプ (現在時刻は now() を使用)、2 番目のパラメータは常に "+0:00" (データベース内のすべての日付が UTC に強制されるため)、最後のパラメータは、変換する日付です。をオフセットします。

日付の加算・減算

例えば、1週間前のレコードを取得したり、1ヶ月後のスケジュールを作成したりと、日付を足したり引いたりする必要がある場合が非常に多いのです。

ありがたいことに、MySQLには優れたDATEu ADD()とDATEu SUB()関数があり、このタスクを非常に簡単に行うことができます。例えば、SQL文を使って、現在の日付から2週間を引くことができます。

SELECT DATE_SUB(now(), interval 2 week);

既存のタイムスタンプに3日間を追加したい場合、以下のようになります。

SELECT DATE_ADD('2021-02-07 11:52:06', interval 3 day);

どちらの関数も同じように動作し、最初の引数は開始するタイムスタンプ、2番目の引数は加算または減算される間隔です。第2引数の書式は常に同じで、intervalという言葉で始まり、数値と間隔そのものが続き、秒、分、時、日、週、月、四半期、年のいずれかの値になる。

たとえば、過去34分間に発生したすべてのログインを取得するには、次のようなSQL文を使用します。

SELECT * FROM logins WHERE login_date >= DATE_SUB(now(), interval 45 minute);

ご覧のように、これはログインの日付が現在の時刻から45分を引いた時刻よりも大きい、つまり過去45分間のログインのレコードをloginsテーブルからすべて取得します。

日付の違いを取得する

2つの予約の間にどれくらいの時間が経過したかを知る必要がある場合があります。DATEDIFF関数を使うと、例えば次のようなSQL文で、2つの異なる日付の間の日数を簡単に求めることができます。

SELECT DATEDIFF(now(), '2020-12-15');

DATEDIFF関数は、2つの引数（いずれも日付/時刻スタンプ）を取り、それらの間の日数を求めます。上記の例では、2020年12月15日から今日までの日数が表示されます。

2つの日付の間の秒数を取得するには、To_seconds() 関数を使用します（例）。

SELECT TO_SECONDS(now()) - TO_SECONDS('2021-02-05 11:56:41');

その結果、提供された2つの日付の間の秒数が表示されます。

日付からセグメントを抽出する

MySQLには、日付から特定の部分を簡単に抽出するための様々な関数があります。例えば、月や日、特定の時間だけが必要な場合などです。そのような機能の例を以下に紹介します。

SELECT MONTH('2021-02-11 15:27:52'); SELECT HOUR(now()); SELECT DAYOFYEAR('2021-07-15 12:00:00');

上記のSQL文は、現在の時刻である02と、9月15日が1年で196日目であることから196を出力することになります。から抽出された日付に対して、それぞれ1つの引数のみを受け付ける、利用可能なすべての日付抽出関数の一覧を以下に示します。

- SECOND()- MINUTE()- HOUR()- DAY()- WEEK() - Number 0 - 52 defining the week within the year.- MONTH()- QUARTER() - Number 1 - 4 defining the quarter of the year.- YEAR()- DAYOFYEAR() - The day of the year (eg. Sept 15th = 196).- LAST_DAY() - The last day in the given month.- DATE() - The date in YYYY-MM-DD format without the time.- TIME() The time in HH:II:SS format without the date.- TO_DAYS() - The number of days since A.D. 0.- TO_SECONDS() - The number of seconds since A.D. 0.- UNIX_TIMESTAMP() - The number of seconds since the epoch (Jan 1st, 1970)

たとえば、すべてのユーザーが作成された月と年だけを取得したい場合、次のようなSQL文を使用します。

SELECT id, MONTH(created_at), YEAR(created_at) FROM users;

これは、usersテーブルのすべてのレコードを取得し、各ユーザーが作成されたid、月、年を表示します。

日付期間によるレコードのグループ化

日付関数の良い使い方は、groupbyを使ってSQL文の中で日付の期間ごとに行をグループ化する機能です。例えば、2020年の全注文の合計金額を月別にグループ化したい場合などです。というようなSQL文が使える。

SELECT MONTH(created_at), SUM(amount) FROM orders WHERE created_at BETWEEN '2020-01-01 00:00:00' AND '2020-12-31 23:59:59' GROUP BY MONTH(created_at);

これは、2020年に発注されたすべての注文を取得し、作成された月でグループ化し、その年の各月の注文金額の合計を示す12レコードを返します。

インデックス作成のパフォーマンスを上げるには、SQL文のWHERE句でYEAR()などの日付関数を使わず、上の例のようにBETWEEN演算子を使うのがベストであることに注意してください。

またデートと混同しないように

この知識を用いることで、様々なユースケースにおいて、日付と時刻の処理、翻訳、実行を効率的に行うことができるようになりました。

また、簡単な計算を行う場合でも、レポートを日付の期間で簡単にグループ化する場合でも、ソフトウェアで効果的に日付を管理するために、上記のヒントを使用してください。

SQLに少し慣れていない方は、SQLの使い方を改善するために、基本的なSQLコマンドを確認するようにしてください。