sqlite3でスラッシュ区切りの日付を扱う

概要

SQLiteでYYYY/MM/DDなどのスラッシュ区切りの日付を何とかdate型にする方法のメモ。

完全にゴリ押し。
もっとスマートなやり方を知っている方教えてください。
(DBにinsertする前に整形しとけ、というのはごもっとも)

sqlite3では以下の理由で日付の扱いが少々面倒になっている。

• データ型に日付型がない、日付は文字列として登録する。
  
• 日付の計算をするには date() や datetime() 関数を使い 文字列 ⇒ 日付 に変換する必要がある。
  • SQLite3では日時は文字列型で保存する
• date() や datetime() で引数に指定できるのは YYYY-MM-DD の形式。
  • SQLite3 date()のサンプル
  • SQLite3 datetime()のサンプル

YYYY/MM/DD のようにスラッシュ区切りの日付を扱うには 以下のようなことをする。

1. substr() や instr() を使い YYYY/MM/DD の文字列を YYYY-MM-DD に整形。
2. date() や datetime() 関数を使って文字列 ⇒ 日付に変換する。

環境

• Windows 10
• sqlite3 (3.24.0)

2019/4/1 の文字列を日付形式のデータに変換するサンプル

                        
select
    date(
        sub.sub_Y
        || '-' 
        || substr('00' || sub.sub_M, -2, 2)
        || '-'
        || substr('00' || sub.sub_D, -2, 2)
    )
from
    (
    select
        -- 年(YEAR) -----------------------------
        substr(
            '2019/4/1'
            , 1
            , instr('2019/4/1', '/')-1	
        ) as sub_Y
        
        -- 月(MONTH) ----------------------------
        , substr(
            substr(
                '2019/4/1'
                , instr('2019/4/1', '/')+1
            )
            , 1
            , instr(
                substr(
                    '2019/4/1'
                    , instr('2019/4/1', '/')+1
                )
                , '/'
            )-1
        ) as sub_M
        
        -- 日(DAY) ------------------------------
        , substr(
            substr(
                '2019/4/1'
                , instr('2019/4/1', '/')+1
            )
            , instr(
                substr(
                    '2019/4/1'
                    , instr('2019/4/1', '/')+1
                )
                , '/'
            )+1
        ) as sub_D
) sub
                        
                    

外部参考サイト

• SQLite Query Language: Date And Time Functions(英語)
  公式の日時型に関するドキュメント
  https://www.sqlite.org/lang_datefunc.html