0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

BigQuery よく使うSQL

Last updated at Posted at 2021-12-24

strをDATETIME型に変換

select SAFE.PARSE_DATETIME("%Y-%m-%d %H:%M:%E3S",'2019-01-02 18:11:12’)

strをDATE型に変換

select SAFE.PARSE_DATE("%Y-%m-%d %H:%M:%E3S",'2019-01-02 18:11:12’)

DATE,DATETIMEをstr型に変換

select FORMAT_DATE("%Y%m", (select CURRENT_DATE('Asia/Tokyo')))

現在の日付を取得できる

select CURRENT_DATE('Asia/Tokyo')
select CURRENT_DATETIME('Asia/Tokyo')
→‘Asia/Tokyo’の指定を省略すると、UTC(日本時間ー9時間)になる

一か月前の日を取得する

select SAFE.DATE_ADD((select CURRENT_DATE('Asia/Tokyo')), INTERVAL -1 MONTH)
SAFE.は、お約束で書いておくと、もしも、エラーが起きてもその行だけがNULLになって、全体は落ちずに動くので便利  

↓でも可
select CURRENT_DATE('Asia/Tokyo')  - INTERVAL 1 MONTH

9時間プラスする

DATETIME_ADD(PARSE_DATETIME("%Y-%m-%dT%H:%M:%SZ",a.CreationTime), INTERVAL 9 HOUR)

月初の日付を取得する

select SAFE.DATE_TRUNC((CURRENT_DATE('Asia/Tokyo’)), MONTH)

日付の差を取る

select SAFE.DATE_DIFF(DATE "2018-12-22", DATE "2017-12-12", DAY)

データを削除する

truncate table `プロジェクト名.データセット名.テーブル名`;

データを復元する(時間を変更する)

SELECT
  *
FROM
  `プロジェクト名.データセット名.テーブル名` FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?