データベースの日時比較とタイムゾーンの扱いについて

システム開発では、データベースに保存された日時情報を正確に比較することが求められます。
しかし、開発環境と本番環境でタイムゾーンの設定が異なる場合や、日時のミリ秒が影響して意図しない比較結果が得られることがあります。
本記事では、SQLとアプリケーション側の日時処理について詳しく解説し、正確な比較を行う方法を紹介します。

日時比較で発生する問題

データベースに保存された日時と、アプリケーション側から送信された日時を比較する際に、以下のような問題が発生することがあります。

1. ミリ秒の影響
   • 例えば、データベースに 2025-05-14 10:56:46.540431+00 と保存されている場合、2025-05-14 10:56:46 という値と単純に比較すると一致しないことがあります。
2. タイムゾーンの違い
   • データベースでは UTC（協定世界時）で保存されているが、アプリケーション側では JST（日本標準時、UTC+9）で処理しているケース。
3. データのフォーマットの違い
   • 例えば、アプリケーション側で YYYY/MM/DD HH:mm:ss 形式で送信していても、データベースのフォーマットが異なる場合、適切に処理しないと比較が失敗する。

SQLでミリ秒を無視して比較する方法

PostgreSQLなどのデータベースでは、DATE_TRUNC 関数を利用することで、ミリ秒以下の情報を切り捨てて比較できます。

SELECT COUNT(*) AS count
FROM テーブル名
WHERE id = $1
AND DATE_TRUNC('second', update_dt) = DATE_TRUNC('second', TO_TIMESTAMP($2, 'YYYY/MM/DD HH24:MI:SS') AT TIME ZONE 'UTC');

SQLのポイント

• DATE_TRUNC('second', update_dt) でミリ秒を切り捨て
• TO_TIMESTAMP($2, 'YYYY/MM/DD HH24:MI:SS') で文字列を日時型に変換
• AT TIME ZONE 'UTC' でUTC基準に統一

アプリケーション側（JavaScript）のタイムゾーン変換

Node.jsでの日時の処理には luxon ライブラリを使用することが多いです。DateTime を適切に変換する例を紹介します。

import { DateTime } from 'luxon';

const update_dt = '2025-05-14 10:56:46';
const utcDateTime = DateTime.fromISO(update_dt, { zone: 'utc' }).toFormat("yyyy/MM/dd HH:mm:ss");
console.log(utcDateTime); // 2025/05/14 10:56:46

コードのポイント

• fromISO(update_dt, { zone: 'utc' }) でUTCとして解釈
• toFormat("yyyy/MM/dd HH:mm:ss") でミリ秒を含まない形式に整形

JSTに変換したい場合

const jstDateTime = DateTime.fromISO(update_dt, { zone: 'utc' }).setZone('Asia/Tokyo').toFormat("yyyy/MM/dd HH:mm:ssZ");
console.log(jstDateTime); // 2025/05/14 10:56:46+09

このコードでは setZone('Asia/Tokyo') を使用して JST（UTC+9）に変換しています。

正確な比較のためのポイントまとめ

1. データベース内の時刻のフォーマットとタイムゾーンを確認
   • SELECT update_dt FROM テーブル名 WHERE id = 5; で時刻の保存形式を確認する。
2. ミリ秒を無視するには DATE_TRUNC('second', update_dt) を使う
   • TIMESTAMP 型でも TIMESTAMPTZ 型でもミリ秒を切り捨て可能。
3. アプリケーション側で送るデータのフォーマットを統一する
   • YYYY/MM/DD HH:mm:ss のように決めておく。
4. アプリケーションでタイムゾーンを明示的に変換する
   • DateTime.fromISO(update_dt).setZone('Asia/Tokyo') などを使う。

まとめ

データベースとアプリケーションでの日時の比較は、ミリ秒やタイムゾーンの違いによって不具合が発生しやすいポイントです。
SQLの DATE_TRUNC を活用し、アプリケーション側では luxon などのライブラリを適切に使うことで、精度の高い比較が可能になります。
開発の際は、常に日時のフォーマットとタイムゾーンを意識し、データが意図した形で処理されているかを検証することが重要です。

• Original:https://minory.org/db-datetime-comparison.html
• Source:minory
• Author:管理者

Amazonベストセラー