SQL 日付操作
from_iso8601_timestamp(iso8601っぽい文字列) -> timestamp with zone
AT TIME ZONE
timestamp 型の timezone の変換ができる
Function ではなく Operator
code:sql
SELECT from_iso8601_timestamp('2022-02-15T20:45:00Z') AT TIME ZONE 'Asia/Tokyo';
2022-02-16 05:45:00.000 Asia/Tokyo
SELECT from_iso8601_timestamp('2022-02-15T20:45:00+0900') AT TIME ZONE 'America/Los_Angeles';
2022-02-15 03:45:00.000 America/Los_Angeles
date_format(timestamp, format) -> string
code:sql
SELECT date_format(
from_iso8601_timestamp(
substr(
'2022-02-15T09:00:00.000+00:00', 1, 13
)
) AT TIME ZONE 'Asia/Tokyo', '%Y-%m-%d %H:00'
);
year month day -> date
year, month, day が int の場合 CAST が必要
month, day は 0埋め (zero padding) が必要(LPAD )
code:sql
date(
CONCAT(
CAST(year AS varchar(4)),
'-',
LPAD(CAST(month AS varchar(4)), 2, '0'),
'-',
LPAD(CAST(day AS varchar(4)), 2, '0')
)
)
date -> hour