MySQL にて unixtime を日付表示に変換する

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