SQLの日付・時間関数（DATE、TIME、DATETIME、JULIANDAY、STRFTIME）を使ってみよう！

2024.08.22 株式会社システムインテグレータ

今回の初心者訓練所#18は、日付と時間を扱う主な関数について説明していきます。それぞれの関数には様々なパラメータが存在しますが、いつもの「SQLite」を使用してその場で確認しながら進めていきます。是非、「SQLite」をインストールしてから読み進めてください。ただし、日付、時間の関数もDBMSにより使用方法が異なる場合がありますので、詳細については、各DBMSのマニュアルを参照してください。

DATE・TIME・DATETIME

DATE関数、TIME関数、DATETIME関数はそれぞれ、指定された日時要素を含む日時文字列を返します。これにより、日時に対してさまざまな操作（例えば、特定の日時を取得したり、日時を修正したり）が可能です。それぞれ同様の構文となりますので、まとめて説明していきます。
・DATE関数の構文は以下となります。

DATE ( 基本となる日付 , 修飾子A , [修飾子B], ... )
基本となる日付：この引数には、'YYYY-MM-DD' 形式の日付、’now’ (現在の日付)、
　　　　　　　　またはISO8601形式の文字列を使用できます。
修飾子：日付の加減算、日付の要素の設定などが含まれます。複数の修飾子を連続して
　　　　使用できます。
※[ ]内は省略可能です。

・TIME関数の構文は以下となります。

TIME ( 基本となる時間 , 修飾子A , [修飾子B], ... )
基本となる時間：この引数には、'HH:MM:SS' 形式の時間、'now' (現在の時刻)、
　　　　　　　　またはISO8601形式の文字列を使用できます。
修飾子：時間の加減算、時間の要素の設定などが含まれます。複数の修飾子を連続して
　　　　使用できます。
※[ ]内は省略可能です。

・DATETIME関数の構文は以下となります。

DATETIME ( 基本となる日時 , 修飾子A , [修飾子B], ... )
基本となる日時：この引数には、'YYYY-MM-DD HH:MM:SS' 形式の日時、
　　　　　　　　'now' (現在の日時)、またはISO8601形式の文字列を使用できます。
修飾子：日時の加減算、要素の設定などが含まれます。複数の修飾子を連続して
　　　　使用できます。
※[ ]内は省略可能です。

それぞれの関数で利用できる主な修飾子は、以下のように沢山あります。
　・NNN days
　・NNN hours
　・NNN minutes
　・NNN seconds
　・NNN months
　・NNN years
　・±HH:MM
　・±HH:MM:SS
　・±HH:MM:SS.SSS
　・±YYYY-MM-DD
　・±YYYY-MM-DD HH:MM
　・±YYYY-MM-DD HH:MM:SS
　・±YYYY-MM-DD HH:MM:SS.SSS
　・start of month
　・start of year
　・start of day
　・weekday N
　・unixepoch
　・julianday
　・localtime
　・utc

それでは、実際に確認してみましょう。今回は確認のためのテーブルは必要ありませんので、SELECT文を記述するだけで、確認できます。
以下のコマンドで出力フォーマットを調整して、SELECT文で確認してみましょう。
.headers on　（ヘッダ情報を出力）
.mode column　（カラム毎に左揃え出力）

（１）現在日時の確認

・現在の日付を取得する。
　SELECT DATE('now');

・現在の時間を取得する。
　SELECT TIME('now');

・現在の日時を取得する。
　SELECT DATETIME('now');

blog18-01
実際に試していただくと、時間の表示が9時間ずれているのではないでしょうか。
修飾子に‘now’のみを指定した場合の時間は、UTC(協定世界時)時間が取得されるので、日本の標準時間のマイナス9時間が取得されます。
日本の標準時間を取得するには、修飾子の’localtime’を合わせて使用します。

・現在のローカル日付を取得する。
　SELECT DATE('now', 'localtime');

・現在のローカル時間を取得する。
　SELECT TIME('now', 'localtime');

・現在のローカル日時を取得する。
　SELECT DATETIME('now', 'localtime');

blog18-02

いかがでしょうか、今度は日本の標準時間が表示されていることが確認できます。

（２）日付の加減算

・指定した日付の10日後を取得する。
　SELECT DATE('2024-01-01', '+10 days');

・指定した日付の2カ月後を取得する。
　SELECT DATE('2024-01-01', '+2 month');

・指定した日付の1年前を取得する。
　SELECT DATE('2024-01-01', '-1 year');

・指定した日付の1年3カ月後を取得する。
　SELECT DATE('2024-01-01', '+1 year', '+3 months');

blog18-03

それぞれ、指定した修飾子の値で日付を加減して表示されていることが確認できます。

（３）時間の加減算

・指定した時間の1時間後を取得する。
　SELECT TIME('12:00:00', '+1 hour');

・指定した時間の40分前を取得する。
　SELECT TIME('12:00:00', '-40 minutes');

・指定した時間の50秒後を取得する。
　SELECT TIME('12:00:00', '+50 seconds');

・指定した時間の2時間10分後を取得する。
　SELECT TIME('12:00:00', '+2 hour', '+10 minutes');

blog18-04

日付の場合と同様に、指定した修飾子の値で時間を加減して表示されていることが確認できます。

（４）日時の加減算

・指定した日時の20日を取得する。
　SELECT DATETIME('2024-01-01 12:00:00', '+20 days');

・指定した日時の10時間後を取得する。
　SELECT DATETIME('2024-01-01 12:00:00', '+10 hours');

・指定した時間の50分後を取得する。
　SELECT DATETIME('2024-01-01 12:00:00', '+50 minutes');

・指定した日時の2日後の2時間20分後を取得する。
　SELECT DATETIME('2024-01-01 12:00:00', '+2 day', '+3 hours', '-40 minutes');

blog18-05

日付、時間の場合と同様に、指定した修飾子の値で日時を加減して表示されていることが確認できます。日時の加減算のイメージが掴めたでしょうか。

（５）区切りの日時

・指定した日時の月初日時を取得する。
　SELECT DATETIME('2024-08-15 13:00:00', 'start of month');

・指定した日時の年初日時を取得する。
　SELECT DATETIME('2024-08-15 13:00:00', 'start of year');

・指定した日時の日付の始まり日時を取得する。
　SELECT DATETIME('2024-08-15 13:00:00', 'start of day');

blog18-06

それぞれ、月初、年初、日の初めの日時が取得できることが確認できます。DATE関数、TIME関数も同じ修飾子で、初めの日時を取得することができますので、試してみてください。

（６）その他の修飾子

・指定した日付から、次の日曜日を取得する。
　SELECT DATE('2024-08-15', 'weekday 0');
　‘weekday’ の後の数字は曜日を指しています。（0:日 1:月 2:火 3:水 4:木 5:金 6:土）

blog18-07

指定した日付の次の日曜日の「2024-08-18」が表示されていることが、確認できます。
主な修飾子を紹介しました。他の修飾子も気になったら色々試してみてください。
※修飾子は指定された順序で適用されます。例えば、+1 yearと+2 monthsは、指定された通りに計算されます。順序が異なると結果も異なる場合がありますので注意してください。

JULIANDAY

JULIANDAY 関数は、日数の計算に便利な関数で、指定された日時をユリウス日（Julian Day Number: 連続する日付の通し番号）に変換するために使用されます。ユリウス日とは、紀元前4713年1月1日正午からの経過日数を表すものです。

構文は下記になります。

JULIANDAY ( 基本となる日時 , 修飾子A , [修飾子B], ... )
基本となる日時：この引数には、'YYYY-MM-DD HH:MM:SS' 形式の日時、
　　　　　　　　'now' (現在の日時)、またはISO8601形式の文字列を使用できます。
修飾子：日時の加減算、要素の設定などが含まれます。複数の修飾子を連続して
　　　　使用できます。
※[ ]内は省略可能です。

では、実際にためしてみましょう。

・現在日時をユリウス日に変換した値を取得する。
　SELECT JULIANDAY('now');

・指定した日時をユリウス日に変換した値を取得する。
　SELECT JULIANDAY('2024-08-15 13:50:00');

・指定した日付から1年を加え、その3カ月前、さらに5日を加えた日付をユリウス日に
　変換した値を取得する。
　SELECT JULIANDAY('2024-08-15', '+1 year', '-3 months', '+5 days');

blog18-08

上記のように、DATE関数等と同様に修飾子を使うことができます。日数計算等に便利な関数なので、是非、覚えてください。

STRFTIME

STRFTIME 関数は、日時データを指定された書式文字列にフォーマットして返すために使用されます。この関数は、日付や時刻のフォーマットを変更したい場合に非常に便利です。
構文は下記になります。

STRFTIME ( 書式文字列 , 基本となる日時 , 修飾子A , [修飾子B], ... )
書式文字列：変更する書式文字列を指定します。
基本となる日時：この引数には、'YYYY-MM-DD HH:MM:SS' 形式の日時、
　　　　　　　　'now' (現在の日時)、またはISO8601形式の文字列を使用できます。
修飾子：日時の加減算、要素の設定などが含まれます。複数の修飾子を連続して
　　　　使用できます。
　　　　　　　　　　　　　　　　　　　　　　　　　　　　※[ ]内は省略可能です。　　　

以下は、STRFTIME 関数で使用できる主な書式文字列の一覧です。
　・%d：月日（01から31）
　・%f：少数秒（SS.SSS）
　・%H：時間（00から23）
　・%j：年内の日数（001から366）
　・%J：ユリウス日数
　・%m：月（01から12）
　・%M：分（00から59）
　・%s:：1970-01-01 00:00:00 UTCからの秒数
　・%S：秒（00から59）
　・%w：曜日（０から６　0:日曜日）
　・%W：年内の週（00から53）
　・%Y：年（0000から9999）

それでは、書式文字列を使用して確認してみましょう。

・指定した日時から年月を取得する。
　SELECT STRFTIME('%Y-%m', '2024-08-13 13:00:00');

・指定した日時から、時分秒を取得する。
　SELECT STRFTIME('%H:%M:%S', '2023-10-13 15:30:00');

・現在日時の１年後の日時を取得する。
　SELECT STRFTIME('%Y-%m-%d %H:%M:%S', 'now', '+1 year');

blog18-09

書式文字列に指定した内容が取得できているのが、確認できます。もう少し例を見てみましょう。

・指定した日時から曜日と日付を取得する。
　SELECT STRFTIME('%w - %d', '2024-08-20');

・現在日時から、ユリウス日を取得する。
　SELECT STRFTIME('%J', 'now');

・指定した日付の次の月曜日を取得する。
　SELECT STRFTIME('%Y-%m-%d', '2024-08-13', 'weekday 1');

blog18-10

初めのSELECT文の結果の「2」は、火曜日を指します。

以上のように、STRFTIME関数は書式文字列の組合せにより様々な形式にして取得することができる便利な関数なので、是非、使い方をマスターしてください。

説明は以上となります。

あとがき

「初心者訓練所」の第18弾では、日付と時間に関する関数について説明してきました。これらの関数を駆使することで、日付や時間の操作、フォーマット、計算を柔軟に行うことができるようになり、日付や時間を扱うさまざまなシナリオに対応できるようになります。データベースにおける日時の操作は重要なスキルの一つですので、本書がこれを習得する一助となれば幸いです。さらなる詳細な使い方や応用例についてもぜひ探究してみてください！

(株)システムインテグレータ　TOPSICチーム　元鬼軍曹　久保　司