SQLiteでUnixエポック時間をCASE式、CAST関数、substr()関数で変換
SQLiteでUnixエポック時間を変換する方法
方法1：strftime() 関数を使用する
strftime() 関数は、Unixエポック時間を指定された書式に変換するために使用できます。以下は、strftime() 関数を使用してUnixエポック時間をYYYY-MM-DD HH:MM:SS形式に変換する例です。
SELECT strftime('%Y-%m-%d %H:%M:%S', unix_timestamp);
このクエリは、unix_timestamp カラム内のすべての値をYYYY-MM-DD HH:MM:SS形式に変換して返します。
datetime() 関数は、Unixエポック時間をDateTimeオブジェクトに変換するために使用できます。その後、strftime() 関数を使用して、DateTimeオブジェクトを指定された書式に変換することができます。以下は、datetime() 関数と strftime() 関数を使用してUnixエポック時間をYYYY-MM-DD HH:MM:SS形式に変換する例です。
SELECT strftime('%Y-%m-%d %H:%M:%S', datetime(unix_timestamp, 'unixepoch'));
このクエリは、unix_timestamp カラム内のすべての値をUnixエポック時間からDateTimeオブジェクトに変換し、次にYYYY-MM-DD HH:MM:SS形式に変換して返します。
-
以下の書式文字列を使用して、strftime() 関数で日付と時刻をフォーマットすることができます。
%Y: 年 (4桁)%H: 時 (24時間表記、2桁)
例
CREATE TABLE my_table (
id INTEGER PRIMARY KEY,
unix_timestamp INTEGER
);
INSERT INTO my_table (unix_timestamp) VALUES (1670906000);
SELECT id, strftime('%Y-%m-%d %H:%M:%S', unix_timestamp) AS formatted_timestamp
FROM my_table;
このクエリは、my_table テーブルを作成し、1つのレコードを挿入します。次に、strftime() 関数を使用してUnixエポック時間をYYYY-MM-DD HH:MM:SS形式に変換し、結果を返します。
この例では、出力は以下のようになります。
id | formatted_timestamp
------- | --------
1 | 2023-12-08 20:00:00
-- テーブルの作成
CREATE TABLE my_table (
id INTEGER PRIMARY KEY,
unix_timestamp INTEGER
);
-- データの挿入
INSERT INTO my_table (unix_timestamp) VALUES (1670906000);
-- 方法1：strftime() 関数を使用する
SELECT id, strftime('%Y-%m-%d %H:%M:%S', unix_timestamp) AS formatted_timestamp
FROM my_table;
-- 方法2：datetime() 関数を使用する
SELECT id, strftime('%Y-%m-%d %H:%M:%S', datetime(unix_timestamp, 'unixepoch')) AS formatted_timestamp
FROM my_table;
このコードを実行すると、以下の結果が出力されます。
id | formatted_timestamp
------- | --------
1 | 2023-12-08 20:00:00
説明:
- テーブルの作成:
CREATE TABLE my_table (id INTEGER PRIMARY KEY, unix_timestamp INTEGER);というクエリで、my_tableという名前のテーブルを作成します。- このテーブルには、
idという主キー列と、unix_timestampという列があります。 unix_timestamp列は、Unixエポック時間を格納するために使用されます。
- データの挿入:
INSERT INTO my_table (unix_timestamp) VALUES (1670906000);というクエリで、my_tableテーブルに1つのレコードを挿入します。- このレコードの
unix_timestamp列には、1670906000 という値が格納されます。 - この値は、2023年12月8日20:00:00 (UTC) のUnixエポック時間です。
- 方法1：strftime() 関数を使用する:
SELECT id, strftime('%Y-%m-%d %H:%M:%S', unix_timestamp) AS formatted_timestamp FROM my_table;というクエリで、my_tableテーブル内のすべてのレコードを検索します。strftime('%Y-%m-%d %H:%M:%S', unix_timestamp)という式を使用して、unix_timestamp列の値をYYYY-MM-DD HH:MM:SS形式に変換します。- 変換された値は、
formatted_timestampという列名で返されます。
- 方法2：datetime() 関数を使用する:
datetime(unix_timestamp, 'unixepoch')という式を使用して、unix_timestamp列の値をUnixエポック時間からDateTimeオブジェクトに変換します。strftime('%Y-%m-%d %H:%M:%S', datetime(unix_timestamp, 'unixepoch'))という式を使用して、DateTimeオブジェクトをYYYY-MM-DD HH:MM:SS形式に変換します。
- 上記のコードは、SQLiteバージョン3.36.0以降で使用できます。
SELECT
id,
CASE
WHEN unix_timestamp < 1600000000 THEN '1970-01-01 00:00:00'
ELSE strftime('%Y-%m-%d %H:%M:%S', unix_timestamp)
END AS formatted_timestamp
FROM my_table;
このクエリは、unix_timestamp 列の値が1600000000 (1970年1月1日00:00:00 UTC) より小さい場合は "1970-01-01 00:00:00" を返し、それ以外の場合は strftime() 関数を使用してUnixエポック時間をYYYY-MM-DD HH:MM:SS形式に変換します。
SELECT
id,
CAST(unix_timestamp AS TEXT) || ' ' || CAST(unix_timestamp / 3600 % 24 AS TEXT) || ':' ||
CAST(unix_timestamp / 60 % 60 AS TEXT) || ':' ||
CAST(unix_timestamp % 60 AS TEXT) AS formatted_timestamp
FROM my_table;
このクエリは、CAST 関数を使用して、unix_timestamp 列の値をテキストに変換します。その後、文字列連結演算子 (||) を使用して、年、月、日、時、分、秒を結合します。
substr() 関数を使用する
SELECT
id,
substr(strftime('%Y-%m-%d %H:%M:%S', unix_timestamp), 1, 11) || ' ' ||
substr(strftime('%Y-%m-%d %H:%M:%S', unix_timestamp), 13, 2) || ':' ||
substr(strftime('%Y-%m-%d %H:%M:%S', unix_timestamp), 16, 2) || ':' ||
substr(strftime('%Y-%m-%d %H:%M:%S', unix_timestamp), 19, 2) AS formatted_timestamp
FROM my_table;
このクエリは、strftime() 関数を使用して、unix_timestamp 列の値をYYYY-MM-DD HH:MM:SS形式に変換します。その後、substr() 関数を使用して、文字列から年、月、日、時、分、秒を抽出します。
これらの方法は、それぞれ長所と短所があります。どの方法を使用するかは、状況によって異なります。
長所と短所:
- strftime() 関数:
- 長所: シンプルでわかりやすい
- 短所: 古いバージョンのSQLiteでは使用できない
- CASE式:
- 長所: 複雑な条件処理が可能
- 短所: 読みづらい
- CAST関数:
- 長所: 可読性が高い
- 短所: パフォーマンスが遅い
- substr() 関数:
- 短所: 複雑
sql sqlite unix
