MySQL で unixtime を「○○年○月○日」みたいな表示に変えたく、
その作業メモ。
例えばこんなデータ
| created_at |
|---|
| 1630998895 |
| 1635346872 |
| 1638451365 |
| 1638454844 |
| 1638454985 |
を、
| created_at | conv_created_at |
|---|---|
| 1630998895 | 2021-09-08 |
| 1635346872 | 2021-10-28 |
| 1638451365 | 2021-12-03 |
| 1638454844 | 2021-12-03 |
| 1638454985 | 2021-12-03 |
↑こんな感じにしたい
サンプル色々
以下、前提。
- 日本時間 (+9:00) で表示したい
使う関数、変数
- SET time_zone
- CONVERT_TZ(dt,from_tz,to_tz)
- DATE_FORMAT(date,format)
- FROM_UNIXTIME(unix_timestamp[,format])
例1
SET time_zone は次の行に書かれた SQL 文のみ有効っぽいので注意。
少し使い勝手が悪いかもしれない。
SET time_zone = 'Asia/Tokyo';
SELECT *, FROM_UNIXTIME(created_at, '%Y-%m-%d') as conv_created_at, FROM_UNIXTIME(created_at) as conv_created_at_temp FROM `tests`;
| created_at | conv_created_at | conv_created_at_temp |
|---|---|---|
| 1630998895 | 2021-09-08 | 2021-09-08 01:14:55 |
| 1635346872 | 2021-10-28 | 2021-10-28 09:01:12 |
| 1638451365 | 2021-12-03 | 2021-12-03 07:22:45 |
| 1638454844 | 2021-12-03 | 2021-12-03 08:20:44 |
| 1638454985 | 2021-12-03 | 2021-12-03 08:23:05 |
例2
FROM_UNIXTIME(unix_timestamp[,format]) でもフォーマットを指定できるけど、
CONVERT_TZ(dt,from_tz,to_tz) と併用する際は使わない。
DATE_FORMAT(date,format) で対応する。
SELECT *, DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(created_at), '+00:00', '+09:00'), '%Y-%m-%d') as conv_created_at, CONVERT_TZ(FROM_UNIXTIME(created_at), '+00:00', '+09:00') as conv_created_at_temp FROM `tests`;
| created_at | conv_created_at | conv_created_at_temp |
|---|---|---|
| 1630998895 | 2021-09-08 | 2021-09-08 01:14:55 |
| 1635346872 | 2021-10-28 | 2021-10-28 09:01:12 |
| 1638451365 | 2021-12-03 | 2021-12-03 07:22:45 |
| 1638454844 | 2021-12-03 | 2021-12-03 08:20:44 |
| 1638454985 | 2021-12-03 | 2021-12-03 08:23:05 |