【保存形式は関係ない】SQLiteの時刻を思いのままに！フォーマットの達人になるための秘訣

2024-05-21

SQLite でタイムスタンプをフォーマットする方法

strftime() 関数は、タイムスタンプを指定した形式に変換するために使用されます。以下の例では、タイムスタンプを "YYYY-MM-DD HH:MM:SS" 形式に変換しています。

SELECT strftime('%Y-%m-%d %H:%M:%S', my_timestamp_column);

strftime()` 関数で使用できる形式指定子は以下の通りです。

%Y: 年 (4 桁)
%f: マイクロ秒

これらの形式指定子を組み合わせて、必要な形式に変換することができます。

CAST() 関数は、データ型を別のデータ型に変換するために使用されます。以下の例では、タイムスタンプを TEXT 型に変換しています。

SELECT CAST(my_timestamp_column AS TEXT);

TEXT 型に変換すると、strftime() 関数を使用してフォーマットすることができます。

サードパーティ製のライブラリを使用する

SQLite には、タイムスタンプをフォーマットするためのサードパーティ製のライブラリがいくつか用意されています。これらのライブラリは、strftime() 関数よりも多くの機能を提供している場合が多いです。

注意事項

SQLite は、タイムスタンプを内部的に NUMERIC 型で保存します。これは、タイムスタンプを整数として扱うことができることを意味しますが、精度が制限されることを意味します。
タイムスタンプをフォーマットする際には、ロケール設定の影響を受けることに注意する必要があります。ロケール設定によって、日付と時刻の形式が異なる場合があります。

SQLite には、タイムスタンプを様々な形式で表示するための様々な方法があります。上記の方法を参考に、状況に応じて適切な方法を選択してください。

SQLite でタイムスタンプをフォーマットするサンプルコード

-- タイムスタンプを "YYYY-MM-DD HH:MM:SS" 形式に変換する
SELECT strftime('%Y-%m-%d %H:%M:%S', my_timestamp_column);

-- タイムスタンプを "YYYY年MM月DD日 HH時MM分SS秒" 形式に変換する
SELECT strftime('%Y年%m月%d日 %H時%M分%S秒', my_timestamp_column);

-- タイムスタンプを "2024-05-21 12:34:56" 形式に変換する
SELECT strftime('%Y-%m-%d %H:%M:%S', 1684936496);

CAST() 関数を使用する

-- タイムスタンプを TEXT 型に変換する
SELECT CAST(my_timestamp_column AS TEXT);

-- 変換された TEXT 型の値をフォーマットする
SELECT strftime('%Y-%m-%d %H:%M:%S', CAST(my_timestamp_column AS TEXT));

例: SQLite DateTime Extensions ライブラリを使用する

-- SQLite DateTime Extensions ライブラリをロードする
PRAGMA load_extension('mod_datetime.so');

-- タイムスタンプを "YYYY-MM-DD HH:MM:SS" 形式に変換する
SELECT format('%Y-%m-%d %H:%M:%S', my_timestamp_column);

上記のコードはあくまで例であり、状況に合わせて変更する必要があります。
サードパーティ製のライブラリを使用する場合は、ライブラリのドキュメントを参照してください。

SQLite でタイムスタンプをフォーマットするその他の方法

SUBSTR() 関数と STRftime() 関数を組み合わせる

SUBSTR() 関数は、文字列の一部を抽出するために使用されます。この関数を使用して、タイムスタンプ文字列の一部を抽出し、strftime() 関数を使用してフォーマットすることができます。

以下の例では、タイムスタンプ文字列から日付部分のみを抽出し、"YYYY年MM月DD日" 形式に変換しています。

SELECT strftime('%Y年%m月%d日', SUBSTR(my_timestamp_column, 1, 10));

printf() 関数は、書式付き文字列を出力するために使用されます。この関数を使用して、タイムスタンプを必要な形式で直接出力することができます。

以下の例では、タイムスタンプを "YYYY-MM-DD HH:MM:SS" 形式で出力しています。

SELECT printf('%Y-%m-%d %H:%M:%S', my_timestamp_column);

カスタム SQL 関数を作成する

独自のフォーマット要件がある場合は、カスタム SQL 関数を作成することができます。この関数は、タイムスタンプを受け取り、必要な形式で文字列を返すように設計することができます。

以下の例は、"YYYY年MM月DD日 HH時MM分" 形式でタイムスタンプを返すカスタム SQL 関数の例です。

CREATE FUNCTION format_timestamp(timestamp)
RETURNS TEXT
BEGIN
  RETURN strftime('%Y年%m月%d日 %H時%M分', timestamp);
END;

-- カスタム SQL 関数を使用する
SELECT format_timestamp(my_timestamp_column);