More than 3 years have passed since last update.

BigQuery での unix timestamp (micro秒) の JST への変換を何度も忘れるのでメモ

Last updated at 2022-03-26Posted at 2020-06-03

タイトルの通りです

select 
  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_MICROS(CAST('1590968103557000' AS INT64)), 'Asia/Tokyo') AS s0, -- STRING
  STRING(TIMESTAMP_MICROS(CAST('1590968103557000' AS INT64)), 'Asia/Tokyo') AS s1, -- STRING
  DATETIME(TIMESTAMP_MICROS(CAST('1590968103557000' AS INT64)), 'Asia/Tokyo') as s2, -- DATETIME
  PARSE_DATE('%Y%m%d',  '20200601')  as  d0 -- DATE

行	s0	s1	s2	d0
1	2020-06-01 08:35:03	2020-06-01 08:35:03.557+09	2020-06-01T08:35:03.557000	2020-06-01

メモ: DATETIME にしておくとその後 EXTRACT 等で操作できるので柔軟。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up