【保存版】SQLiteでタイムゾーンの壁を越える！18桁タイムスタンプを現地時間に簡単変換

SQLite で 18 桁のタイムスタンプを SQL クエリ内で現地時間にタイムスタンプ変換する方法

方法 1：strftime() 関数と cast() 関数を使用する

1. strftime() 関数を使用して、18 桁のタイムスタンプから必要な形式の文字列に変換します。
2. cast() 関数を使用して、文字列を DATETIME 値に変換します。
3. localtime() 関数を使用して、DATETIME 値を現地時間に調整します。

SELECT
  cast(strftime('%Y-%m-%d %H:%M:%S', timestamp_column) AS DATETIME) AS local_time
FROM your_table;

1. coalesce() 関数を使用して、NULL 値の可能性がある strftime() 関数の結果を処理します。

SELECT
  localtime(coalesce(cast(strftime('%Y-%m-%d %H:%M:%S', timestamp_column) AS DATETIME), '1970-01-01 00:00:00')) AS local_time
FROM your_table;

方法 3：Julian Day 関数を使用する

1. 18 桁のタイムスタンプをジュリアン日付に変換します。
2. ジュリアン日付をグレゴリオ暦の日付に変換します。

SELECT
  localtime(
    julianday(
      strftime('%Y-%m-%d', timestamp_column) || ' 00:00:00'
    ) +
    (strftime('%H:%M:%S', timestamp_column) / (24 * 60 * 60))
  )
) AS local_time
FROM your_table;

注意事項

• 上記の例では、timestamp_column を実際のタイムスタンプ列名に置き換えてください。
• 使用する方法は、データの形式や要件によって選択してください。
• SQLite にはバージョンによって機能が異なる場合があります。使用している SQLite のバージョンに対応した関数や構文を使用していることを確認してください。

CREATE TABLE your_table (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  timestamp_column INTEGER NOT NULL
);

INSERT INTO your_table (timestamp_column) VALUES (1677802400); -- 2023-12-31 20:00:00 PST

SELECT
  cast(strftime('%Y-%m-%d %H:%M:%S', timestamp_column) AS DATETIME) AS local_time
FROM your_table;

CREATE TABLE your_table (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  timestamp_column INTEGER NOT NULL
);

INSERT INTO your_table (timestamp_column) VALUES (1677802400); -- 2023-12-31 20:00:00 PST

SELECT
  localtime(coalesce(cast(strftime('%Y-%m-%d %H:%M:%S', timestamp_column) AS DATETIME), '1970-01-01 00:00:00')) AS local_time
FROM your_table;

CREATE TABLE your_table (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  timestamp_column INTEGER NOT NULL
);

INSERT INTO your_table (timestamp_column) VALUES (1677802400); -- 2023-12-31 20:00:00 PST

SELECT
  localtime(
    julianday(
      strftime('%Y-%m-%d', timestamp_column) || ' 00:00:00'
    ) +
    (strftime('%H:%M:%S', timestamp_column) / (24 * 60 * 60))
  )
) AS local_time
FROM your_table;

説明

• 上記のコードは、your_table という名前のテーブルを作成し、id 列と timestamp_column 列を定義します。
• timestamp_column 列は、18 桁のタイムスタンプを格納します。
• サンプルデータとして、1677802400 (2023-12-31 20:00:00 PST) を timestamp_column 列に挿入します。
• 3 つの方法それぞれを使用して、18 桁のタイムスタンプを現地時間にタイムスタンプ変換し、local_time 列として表示します。

実行結果

local_time
----------
2024-01-01 08:00:00

• 上記のサンプルコードは、Morrow County, Oregon, United States の現地時間を想定しています。他の地域の場合は、localtime() 関数の引数を調整する必要があります。

SQLite で 18 桁のタイムスタンプを SQL クエリ内で現地時間にタイムスタンプ変換する方法：その他の方法

1. strptime() 関数を使用して、UTC 時間形式の文字列を現地時間のタイムスタンプに変換します。

SELECT
  strptime(strftime('%Y-%m-%dT%H:%M:%S.000Z', timestamp_column), '%Y-%m-%dT%H:%M:%S.000%z') AS local_time
FROM your_table;

方法 5：WITH clause を使用する

1. WITH 句を使用して、18 桁のタイムスタンプを現地時間にタイムスタンプ変換する副問い合わせを作成します。
2. メインのクエリで、副問い合わせの結果を参照します。

WITH local_time_alias AS (
  SELECT
    localtime(
      julianday(
        strftime('%Y-%m-%d', timestamp_column) || ' 00:00:00'
      ) +
      (strftime('%H:%M:%S', timestamp_column) / (24 * 60 * 60))
    )
  ) AS local_time
FROM your_table
)
SELECT * FROM local_time_alias;

方法 6：カスタム関数を使用する

1. メインのクエリで、カスタム関数を使用します。

CREATE FUNCTION localtime_from_timestamp(timestamp INTEGER)
RETURNS DATETIME
BEGIN
  RETURN localtime(
    julianday(
      strftime('%Y-%m-%d', timestamp) || ' 00:00:00'
    ) +
    (strftime('%H:%M:%S', timestamp) / (24 * 60 * 60))
  );
END;

SELECT
  localtime_from_timestamp(timestamp_column) AS local_time
FROM your_table;

• 上記の方法にはそれぞれ長所と短所があります。使用方法は、データの形式や要件によって選択してください。
• カスタム関数を作成する場合は、その関数の動作を十分に理解し、テストすることを忘れないでください。