対応エンジン
| エンジン | 対応 | 備考 |
|---|---|---|
| TD（Presto） | ✅ | TD_TIME_PARSEはTD固有関数 |
| Trino / Presto | ⚠️ | TD_TIME_PARSEは使えない。代替あり |
| Hive | ⚠️ | SUBSTR・CONCATは使える。変換関数が異なる |
こんなときに使う
会員登録日や購買日など、20240101のような整数8桁で日付が格納されているテーブルはTDあるあるです。TD_TIME_RANGEや日付計算に使うにはUNIXタイム（秒）に変換する必要があります。
TDでの実装
入力例：20240101
出力例：1704034800
SELECT
TD_TIME_PARSE(
CONCAT(
SUBSTR(CAST(date_col AS VARCHAR), 1, 4), '-',
SUBSTR(CAST(date_col AS VARCHAR), 5, 2), '-',
SUBSTR(CAST(date_col AS VARCHAR), 7, 2)
),
'Asia/Tokyo'
) AS unix_time
FROM your_table
ステップごとの解説
STEP1｜整数を文字列にキャスト
CAST(date_col AS VARCHAR)
-- 20240101 → '20240101'整数のままでは文字列操作ができないので、まずVARCHARに変換します。
STEP2｜ハイフン区切りの日付文字列を作る
CONCAT(
SUBSTR('20240101', 1, 4), '-', -- '2024'
SUBSTR('20240101', 5, 2), '-', -- '01'
SUBSTR('20240101', 7, 2) -- '01'
)
-- → '2024-01-01'SUBSTR(文字列, 開始位置, 文字数)で年・月・日を切り出してCONCATでつなぎます。
STEP3｜TD_TIME_PARSEでUNIXタイムに変換
TD_TIME_PARSE('2024-01-01', 'Asia/Tokyo')
-- → 1704034800TD_TIME_PARSEはTD固有関数です。第2引数にタイムゾーンを指定できます。
日本のデータなら必ず'Asia/Tokyo'を指定しましょう。省略するとUTCとして解釈され9時間ずれます。
Trino / Prestoでの実装
TD_TIME_PARSEは使えないため、to_unixtimeとdate_parseを組み合わせます。
SELECT
to_unixtime(
date_parse(
CONCAT(
SUBSTR(CAST(date_col AS VARCHAR), 1, 4), '-',
SUBSTR(CAST(date_col AS VARCHAR), 5, 2), '-',
SUBSTR(CAST(date_col AS VARCHAR), 7, 2)
),
'%Y-%m-%d'
)
) AS unix_time
FROM your_table
date_parseで文字列をTIMESTAMP型に変換してからto_unixtimeでUNIXタイム（秒）に変換します。タイムゾーンはセッション設定に依存するため、必要に応じてAT TIME ZONE 'Asia/Tokyo'を付与します。
-- タイムゾーンを明示的に指定する場合
SELECT
to_unixtime(
date_parse(..., '%Y-%m-%d') AT TIME ZONE 'Asia/Tokyo'
) AS unix_time
FROM your_table
Hiveでの実装
Hiveではunix_timestamp関数を使います。
SELECT
unix_timestamp(
CONCAT(
SUBSTR(CAST(date_col AS STRING), 1, 4), '-',
SUBSTR(CAST(date_col AS STRING), 5, 2), '-',
SUBSTR(CAST(date_col AS STRING), 7, 2)
),
'yyyy-MM-dd'
) AS unix_time
FROM your_table
unix_timestamp(文字列, フォーマット)で日付文字列をUNIXタイムに変換します。TDと異なりタイムゾーンはHiveのセッション設定に従います。
エンジン別まとめ
| エンジン | 変換関数 | タイムゾーン指定 |
|---|---|---|
| TD（Presto） | TD_TIME_PARSE | 第2引数に'Asia/Tokyo'を指定 |
| Trino / Presto | to_unixtime + date_parse | AT TIME ZONE 'Asia/Tokyo' |
| Hive | unix_timestamp | セッション設定に依存 |
よくある応用：そのまま期間フィルタに使う
-- TD版
SELECT *
FROM your_table
WHERE TD_TIME_RANGE(
time,
TD_TIME_PARSE(
CONCAT(
SUBSTR(CAST(register_date AS VARCHAR), 1, 4), '-',
SUBSTR(CAST(register_date AS VARCHAR), 5, 2), '-',
SUBSTR(CAST(register_date AS VARCHAR), 7, 2)
),
'Asia/Tokyo'
)
)
⚠️ よくあるミス
タイムゾーンを省略する
-- ❌ UTCで解釈されてしまい9時間ずれる
TD_TIME_PARSE('2024-01-01')
-- ✅ 必ずAsia/Tokyoを指定
TD_TIME_PARSE('2024-01-01', 'Asia/Tokyo')
8桁文字列をそのままTD_TIME_PARSEに渡す
-- ❌ ハイフンなしはNULLになる
TD_TIME_PARSE('20240101', 'Asia/Tokyo')
-- ✅ SUBSTR+CONCATでハイフン区切りにしてから渡す
TD_TIME_PARSE(
CONCAT(
SUBSTR('20240101', 1, 4), '-',
SUBSTR('20240101', 5, 2), '-',
SUBSTR('20240101', 7, 2)
),
'Asia/Tokyo'
)
まとめ
| ステップ | 関数 | やること |
|---|---|---|
| 1 | CAST(... AS VARCHAR) | 整数→文字列 |
| 2 | SUBSTR + CONCAT | yyyyMMdd→yyyy-MM-dd |
| 3 | TD_TIME_PARSE | 日付文字列→UNIXタイム（TD） |
| 3 | to_unixtime + date_parse | 日付文字列→UNIXタイム（Trino/Presto） |
| 3 | unix_timestamp | 日付文字列→UNIXタイム（Hive） |