SQLite 日時の比較

1. DateTime データ型を使用して 2つの日付を比較する
2. strftime() 関数を使用して SQLite で 2つの日付を比較する

SQLite データベースは、整数、文字列、一部の日時形式など、さまざまな種類のデータを使用して保存します。 日時の格納には、date、smalldatetime、DateTime、datetime2 など、さまざまなタイプがあります。

通常、SQLite データベースでの比較には WHERE 句を使用し、必要なデータを取得します。

問題は、SQL データベース内でさまざまな種類の日時形式を比較する方法です。 日付と時刻を直接比較する方法はないことに注意してください。

DateTime データ型を使用して 2つの日付を比較する

SQLite では、日付を直接比較する方法はありません。 ただし、SQLite には日付と時刻の組み込み関数があり、日付と時刻をテキスト、整数、または実数値に格納できます。

それらはアプリケーションに依存し、組み込みの時刻と日付関数の形式を選択できます。

1. テキストは、YYYY-MM-DD HH:MM:SS.SSS 形式の ISO8601 文字列を取ります。
2. 整数は Unix 時間で、1970-01-01 00:00:00 UTC 形式です。

したがって、比較するために、通常、日付と時刻を特定の形式の文字列に変換して、一貫性を保ちます。 たとえば、日付は YYYY-MM-DD hh:mm:ss 形式で保存されます。

日付と時刻をこの形式で一貫して保存すると、日付と時刻を簡単に比較できます。 以下のステートメントに従って、日付との簡単な比較例を示すことができます。

SELECT * FROM name_of_the_table WHERE comparsion_date < '2020-04-05 00:00:00';

比較は数値ではないことがわかりますが、アルファベットの比較があります。 格納された日付形式と比較日付の形式が類似している場合、これは正しいです。

ここで、2つの重要な点を覚えておく必要があります。

1. 時間を比較しやすいように、24 時間形式で時間を保存することをお勧めします。
2. 日付と時刻を 1つの列に格納する必要はありませんが、重要な要素は形式の一貫性です。 日付と時刻が別々の列にある場合、日付列の形式は YYYY-MM-DD で、時刻列の形式は hh:mm:ss である必要があるためです。

したがって、日付と時刻が別々の列にある場合、次のように SQL クエリを記述します。 重要な点は、データベース全体で日付と時刻の形式を統一することです。

SELECT * FROM name_of_the_table WHERE comparsion_date = '2020-05-03' AND comparison_time = '08:03:11';

YYYY-MM-DD hh:mm:ss という形式が好まれるもう 1つの理由は、最高のインデックスから最低のインデックスへ、つまり時間から秒へと並べ替えると、比較が非常に簡単になり、パフォーマンスが優先されるためです。 劣化しない。

strftime() 関数を使用して SQLite で 2つの日付を比較する

SQLite では、strftime() 関数を使用して、引数に指定された形式で日付を返します。 2 番目の引数は、指定された形式で時間文字列を返します。 残りは、異なる結果のデータを取得するための修飾子です。

strftime() 関数の構文は次のとおりです。

strftime(date_format, time_string, remaining_modifiers, ...)

strftime() 関数を使用して日付と時刻を比較するには、SQL ステートメントで BETWEEN および AND 演算子を使用して、特定の期間の特定の日付を見つけます。

SELECT * FROM name_of_the_table WHERE
    strftime('%s', c_date) BETWEEN strftime('%s', start_date) AND strftime('%s', end_date)

strftime() 関数を使用して、現在時刻から指定された日時までの 2つの日時の差を出力できます。

SELECT strftime('%s','now') - strftime('%s','2019-09-05 03:52:35');

出力：

Output of strftime('%s','now') - strftime('%s','2019-09-05 03:52:35')
-----------------------------------------------------------
2010032

DateTime を時、分、秒、ミリ秒に変換する

DateTime 単位を時、分、秒、ミリ秒に変換するには、strftime() 関数を使用します。 次の結果を得るには、strftime() 関数を使用する必要があります。

SELECT strftime('%H %M %S %s','now');

出力：

Output of strftime('%H %M %S %s','now')
-----------------------------
16 54 04 2541572981

指定した時刻から現在時刻までの秒数を計算する (現在)

また、strftime() 関数を使用して DateTime データ型を秒に変換し、指定された引数から現在の秒数までの残りの合計秒数を計算します。

SELECT strftime('%s','now') - strftime('%s','2019-01-04 01:25:47');

出力：

Output of strftime('%s','now') - strftime('%s','2019-01-04 01:25:47')
-----------------------------------------------------------
3221184

年、月、日で時刻歴を計算する

ジョブの開始日から終了日までのテーブルがあり、ユーザーが年、月、日の観点から合計時間履歴を計算したいジョブ保守履歴の簡単な例を考えてみましょう。

以下は、emp_id、start_job_date、および end_job_date の 3つの列を持つテーブル形式です。 emp_id のデータ型は整数ですが、残りの列 start_job_date と end_job_date は DateTime です。

SQL クエリを使用して上記の表から start_job_date と end_job_date の差を計算するには、strftime() 関数を使用して年、月、日の差を直接取得します。

SELECT strftime('%Y',end_job_date - start_job_date) as "Difference in years",
strftime('%m',end_job_date - start_job_date) as "Difference in months",
strftime('%d',end_date - start_job_date) as "Difference in days"
FROM job_history;

出力：