対応エンジン
| エンジン | 対応 | 備考 |
|---|---|---|
| TD（Presto） | ✅ | TD_TIME_FORMATはTD固有関数 |
| Trino / Presto | ⚠️ | format_datetime + from_unixtimeを使う |
| Hive | ⚠️ | from_unixtimeを使う |
こんなときに使う
TDのテーブルではtimeカラムがUNIXタイム（秒）で格納されています。
レポートやBIツールへの出力時に2024-01-01や2024年1月のような人間が読める形式に変換したい場面で使います。
TDでの実装
入力例：1704034800
出力例：'2024-01-01'
SELECT
TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'Asia/Tokyo') AS date_str
FROM your_tableフォーマット一覧
-- 日付のみ
TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'Asia/Tokyo')
-- → '2024-01-01'
-- 日時
TD_TIME_FORMAT(time, 'yyyy-MM-dd HH:mm:ss', 'Asia/Tokyo')
-- → '2024-01-01 09:00:00'
-- 年月のみ（月次集計に便利）
TD_TIME_FORMAT(time, 'yyyy-MM', 'Asia/Tokyo')
-- → '2024-01'
-- 年のみ
TD_TIME_FORMAT(time, 'yyyy', 'Asia/Tokyo')
-- → '2024'
-- 日本語表記
TD_TIME_FORMAT(time, 'yyyy年MM月dd日', 'Asia/Tokyo')
-- → '2024年01月01日'
-- 曜日付き
TD_TIME_FORMAT(time, 'yyyy-MM-dd (E)', 'Asia/Tokyo')
-- → '2024-01-01 (Mon)'
フォーマット記号の意味
| 記号 | 意味 | 例 |
|---|---|---|
yyyy | 年（4桁） | 2024 |
MM | 月（2桁・ゼロ埋め） | 01 |
M | 月（ゼロ埋めなし） | 1 |
dd | 日（2桁・ゼロ埋め） | 01 |
d | 日（ゼロ埋めなし） | 1 |
HH | 時（24時間） | 09 |
mm | 分 | 30 |
ss | 秒 | 00 |
E | 曜日（短縮） | Mon |
EEEE | 曜日（フル） | Monday |
Trino / Prestoでの実装
TD_TIME_FORMATは使えないため、from_unixtimeとformat_datetimeを組み合わせます。
SELECT
format_datetime(
from_unixtime(time) AT TIME ZONE 'Asia/Tokyo',
'yyyy-MM-dd'
) AS date_str
FROM your_table
-- 日時込み
format_datetime(
from_unixtime(time) AT TIME ZONE 'Asia/Tokyo',
'yyyy-MM-dd HH:mm:ss'
)
-- → '2024-01-01 09:00:00'
-- 年月のみ
format_datetime(
from_unixtime(time) AT TIME ZONE 'Asia/Tokyo',
'yyyy-MM'
)
-- → '2024-01'
Hiveでの実装
Hiveではfrom_unixtime一発で変換できます。
SELECT
from_unixtime(time, 'yyyy-MM-dd') AS date_str
FROM your_table-- 日時込み
from_unixtime(time, 'yyyy-MM-dd HH:mm:ss')
-- → '2024-01-01 09:00:00'
-- 年月のみ
from_unixtime(time, 'yyyy-MM')
-- → '2024-01'タイムゾーンはHiveのセッション設定に依存します。
エンジン別まとめ
| エンジン | 関数 | タイムゾーン指定 |
|---|---|---|
| TD | TD_TIME_FORMAT(time, format, timezone) | 第3引数に指定 |
| Trino / Presto | format_datetime(from_unixtime(time) AT TIME ZONE ..., format) | AT TIME ZONEで指定 |
| Hive | from_unixtime(time, format) | セッション設定に依存 |
⚠️ よくあるミス
MMとmmを間違える
-- ❌ 月のつもりが「分」になってしまう
TD_TIME_FORMAT(time, 'yyyy-mm-dd', 'Asia/Tokyo')
-- → '2024-00-01'（mmは分なので0分が入る）
-- ✅ 月はMM（大文字）
TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'Asia/Tokyo')
-- → '2024-01-01'
タイムゾーンを省略する
-- ❌ UTCで解釈されてしまい9時間ずれる
TD_TIME_FORMAT(time, 'yyyy-MM-dd')
-- → '2023-12-31'（日本時間の午前9時未満は前日になる）
-- ✅ 日本データは必ずAsia/Tokyoを指定
TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'Asia/Tokyo')
-- → '2024-01-01'よくある応用：月次で集計する
SELECT
TD_TIME_FORMAT(time, 'yyyy-MM', 'Asia/Tokyo') AS month,
COUNT(*) AS cnt
FROM your_table
GROUP BY 1
ORDER BY 1出力例：
| month | cnt |
|---|---|
| 2024-01 | 1,204 |
| 2024-02 | 980 |
| 2024-03 | 1,531 |
まとめ
| ポイント | 内容 |
|---|---|
| TD基本形 | TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'Asia/Tokyo') |
| フォーマットは自由 | 年月だけ・日時込み・日本語表記なんでもOK |
| MMとmmは別物 | 月は大文字MM、分は小文字mm |
| タイムゾーン必須 | 省略するとUTCで9時間ずれる |