SQL の Unix タイムスタンプ スニペット
MySQL・PostgreSQL・SQLite で Unix タイムスタンプを扱う SQL の例。現在のエポック値、日付への変換、タイムスタンプへの逆変換を含みます。
-- Seconds
UNIX_TIMESTAMP()
-- Milliseconds (MySQL 8.0+)
ROUND(UNIX_TIMESTAMP(NOW(3)) * 1000)FROM_UNIXTIME(1700000000)
FROM_UNIXTIME(1700000000, '%Y-%m-%d %H:%i:%S')UNIX_TIMESTAMP('2023-11-15 06:13:20')-- Seconds (integer)
EXTRACT(EPOCH FROM NOW())::BIGINT
-- Milliseconds
ROUND(EXTRACT(EPOCH FROM NOW()) * 1000)::BIGINTTO_TIMESTAMP(1700000000)
TO_TIMESTAMP(1700000000) AT TIME ZONE 'America/New_York'EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2023-11-15T06:13:20Z')::BIGINTstrftime('%s', 'now') -- secondsdatetime(1700000000, 'unixepoch')
datetime(1700000000, 'unixepoch', 'localtime')SQL のタイムスタンプの基本
各データベースは、名前が少しずつ異なる Unix タイムスタンプ用のヘルパーを提供します。MySQL は UNIX_TIMESTAMP() を使い、PostgreSQL は now() からエポック秒を抽出でき、SQLite は一般に strftime('%s', 'now') を使います。
データベース変換のメモ
高速な数値比較が必要なときはタイムスタンプを整数で保存し、データベースレベルのタイムゾーンや日付関数が必要なときはネイティブのタイムスタンプ型を使います。インポートとエクスポートの境界では UTC を明示してください。
SQL の本番運用メモ
データベースのタイムスタンプの選択は、その値がどう照会されるかに合わせるべきです。整数の Unix 秒はログの期間やレンジスキャンに単純です。データベースが暦日でグループ化したり、月に切り詰めたり、タイムゾーン対応の関数を適用したりする必要があるときは、ネイティブのタイムスタンプ型のほうが適しています。明確な変換規則なしに両方の表現を混在させないでください。
- ミリ秒タイムスタンプを保存するときは INT ではなく BIGINT を使う
- アプリサーバーが境界で一致するよう、インポートしたタイムスタンプには UTC を使う
- created_at >= start and created_at < end のような半開区間を使う
- エポック秒であれタイムスタンプ型であれ、レンジフィルターで使うまさにその列にインデックスを張る
SQL での datetime 変換：GETDATE、CONVERT、CAST
「datetime convert in sql」「getdate sql」「sql conversion date」「sql getdate date」「convert date timestamp to date」を検索する SQL Server ユーザーは、たいてい T-SQL のヘルパー GETDATE()、CONVERT()、CAST() を求めています。MySQL と PostgreSQL は別の組み込み関数を使いますが、操作は同じです。Unix 整数を datetime に、または datetime を対応する Unix 整数に変換します。
- SQL Server GETDATE() — サーバーのローカルゾーンの現在の datetime；UTC には SYSUTCDATETIME() を使う
- SQL Server epoch → datetime: DATEADD(SECOND, 1700000000, '1970-01-01T00:00:00')
- SQL Server datetime → epoch: DATEDIFF_BIG(SECOND, '1970-01-01', GETUTCDATE())
- SQL Server CONVERT/CAST date↔datetime: CONVERT(date, GETDATE()) は時刻を落とす；CAST('2024-03-15T14:30:00' AS datetime2) は ISO 8601 を解析する
- MySQL: SELECT UNIX_TIMESTAMP(NOW()); 現在のエポック；逆方向は FROM_UNIXTIME(1700000000)
- PostgreSQL: SELECT EXTRACT(EPOCH FROM now()); 現在のエポック；逆方向は to_timestamp(1700000000)
- SQLite: SELECT strftime('%s','now'); 現在のエポック；逆方向は datetime(1700000000, 'unixepoch')
- SQL CAST AS DATE / sql cast as date — CAST(GETDATE() AS date) は時刻部分を落とし、YYYY-MM-DD だけを返す
- SQL CAST datetime to date / sql cast datetime to date — CAST(your_datetime AS date) は暦日だけを残す
- SQL CONVERT datetime / sql convert datetime — CONVERT(varchar, GETDATE(), 120) は "YYYY-MM-DD HH:MI:SS" を返す；スタイル 126 は ISO 8601 を返す
- SQL format date convert / sql format date convert — SQL Server：独自レイアウトには FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss')、ISO 8601 には CONVERT(varchar, GETDATE(), 126)
- SQL CAST AS integer / sql cast as integer — CAST(value AS int) — 数値列に保存されたエポック値に便利
- SQL convert from datetime to date / sql convert from datetime to date — CAST(dt AS date) または CONVERT(date, dt) は時刻部分を落とす
- SQL convert integer / sql convert integer — INT を超える 13 桁のミリ秒タイムスタンプには CAST(value AS bigint)
- SQL datetime to date / sql datetime to date — CAST(your_datetime AS date) は YYYY-MM-DD だけを返す
FAQ
- エポックのミリ秒を INT に保存すべきか？
- いいえ。現代のミリ秒タイムスタンプは 13 桁で、32 ビットの INT には安全に収まりません。BIGINT またはネイティブのタイムスタンプ型を使ってください。
- データベースのタイムスタンプは常に UTC か？
- いいえ。挙動はデータベースの種類とセッションのタイムゾーンに依存します。システム間で値をインポート・エクスポート・比較するときは UTC を明示してください。