SQL Server、BigQuery、Redshift 日付型の比較&リファレンス

この記事はEnigmo Advent Calendar 2020 の14日目の記事です。

はじめに

こんにちは、エニグモ 嘉松です。

簡単な自己紹介ですが、BUYMAのプロモーションやマーケティングを行っている事業部に所属して、その中のデータ活用推進室という部署で会社のデータ活用の推進やマーケティング・オートメーションツール(MAツール)を活用した販促支援、CRMなどを担当しています。(データ活用推進室、長らく私一人部署だったのですが、先月1名増えて2名体制になりました!)

背景

エニグモのデータ活用の大きな特徴として、エンジニアに限らず、マーケターやマーチャンダイザー(MD)、カスタマーサポートや役員まで、多くの社員、ほとんどの社員と言っても過言では無いくらいの人が自分でSQLを叩いてデータを見る、分析するという文化、カルチャーが根付いているということが言えると思います。

また、データ活用基盤の整備も積極的に進めており、クラウドで提供されているビックデータ向けのデータベースをデータレイクやデータウェアハウス(DWH)として利用しています。

このように複数のデータベースを活用してく中で出てくる問題点が、SQLシンタックスの違いです。 特にエニグモではエンジニアでは無いユーザもたくさんいるので、SQLの作成に多くの時間がとられてしまうと、本来の業務へも影響がでてきてしまいますし、データ活用は停滞してしまいます。

そこで、この記事ではMicrosoftが提供しているSQLServerGoogleが提供しているフルマネージド型分析データウェアハウスであるBigQuery、Amazon Web ServicesAWS)のクラウド型データウェアハウスであるRedshiftの3製品を対象として、特に混乱するであろう日付および時刻関連のデータ型について整理することで、今後のリファレンスになればと思っています。

f:id:enigmo7:20201212155053p:plain

日付および時刻関連のデータ型

まず、ここでは各データベースの日付および時刻関連のデータ型(の代表的なもの)を列挙します。

※データ型の表記(大文字小文字)、説明の内容については各データベースのマニュアルにおおよそ準拠しています。

SQL Server

データ型 説明 タイムゾーン
date 日付型 なし
datetime 日時型(タイムゾーンなし) なし
datetimeoffset 日時型(タイムゾーンあり) あり

BigQuery

データ型 説明 タイムゾーン
DATE 日付型 なし
DATETIME 日時型 なし
TIMESTAMP タイムスタンプ型 あり

Redshift

データ型 説明 タイムゾーン
DATE カレンダー日付 (年、月、日) なし
TIMESTAMP 日付と時刻 (タイムゾーンなし) なし
TIMESTAMPTZ 日付と時刻 (タイムゾーンあり) あり

タイムゾーンとは?

タイムゾーンについて言及すると、それだけで1本の記事になるくらいなので、簡単に説明します。

データーベースにおけるタイムゾーンのあり・なしとは、標準時間をUTCとするか、それとも個々のデータベースで決めるか、ということです。

タイムゾーンありのデータ型を使う場合は、当然、データを格納する時にもタイムゾーンを指定してデータを格納する必要があります。

また、タイムゾーンなしのデータ型を使う場合は、そのデーターベースにはどのタイムゾーンでデータが格納されているかを、意識して使う必要があります。 例えば日本の時間で格納したデータを、ニューヨークの時間帯で表示させるには、時間を14時間戻してあげるといったことを意識的に行う必要があります。

いずれにしても、ひとつのデータベースで時差のある地域の時間を扱う場合は、時差を意識することからは逃れられません。基準となる時間をUTCにするのか、どうかの違いです。 逆に日本時間だけで良いシステムであれば、扱う時間は常に日本時間なので、タイムゾーンなしのデータ型を使うことで、タイムゾーンを意識する必要がなくなります。

データ型まとめ

日付型 日時型(タイムゾーン無し) 日時型(タイムゾーン有り)
SQLServer date datetime datetimeoffset
BigQuery DATE DATETIME TIMESTAMP
Redshift DATE TIMESTAMP TIMESTAMPTZ

日付型は3データベースともDATEで分かりやすいですね。

日時型(タイムゾーン無し)はSQLServerとBigQueryがDATETIMEなのに対して、RedshiftがTIMESTAMP

日時型(タイムゾーン有り)は全てのデーターベースで異なります。

更にTIMESTAMPはRedshiftでは日時型(タイムゾーン有り)なのに対して、BigQueryでは日時型(タイムゾーン無し)となっています。

この時点で既にややこしくなってますね。

現在日時(日付と時間)の取得方法

次に、それぞれのデータベースで現在の日時(日付と時間)を取得する関数を見ていきます。 ここに挙げた関数以外もありますが、よく使う(であろう)ものを列挙しています。

SQL Server

GETDATE関数

戻り値の型:datetime

SELECT
 GETDATE()
;
------------
2020-12-09 08:20:17.645

BigQuery

CURRENT_TIMESTAMP関数

戻り値の型:TIMESTAMP

SELECT
 CURRENT_TIMESTAMP()
;
------------
2020-12-10 08:07:47.222776 UTC

括弧は省略可能です。 UTCで表示されます。 日本時間(JST)で表示させたい場合は以降の「日付型 → 文字型」を参照ください。

Redshift

SYSDATE関数

戻り値の型:TIMESTAMP

select
 sysdate
;
------------
2020-12-09T08:20:17.645728

GETDATE関数

戻り値の型:TIMESTAMP

select
 getdate()
;
------------
2020-12-09T08:20:17.645728

どちらもデフォルトではUTCが表示されるので、日本時間を表示したい場合はセッションのタイム ゾーン(デフォルトではUTC)を設定してあげる必要があります。

set timezone = 'Asia/Tokyo';
select
 sysdate
;
------------
2020-12-09T17:20:17.645728

 

set timezone = 'Asia/Tokyo';
select
 getdate()
;
------------
2020-12-09T17:20:17.645728

現在日付の取得方法

SQL Server

SQL Serverには単体で日付を取得する関数が無いので、GETDATE()で現在の日にち時刻を取得した後に、CONVERTを使ってdate型に変換してあげる必要があります。

SELECT
 CONVERT(date, GETDATE())
;
------------
2020-12-09

BigQuery

CURRENT_DATE関数

戻り値の型:DATE

SELECT
 CURRENT_DATE()
;
------------
2020-12-09

引数に何もして指定しないとUTCの日にちが返ってくるので、日本時間での日にちを取得する場合は、引数にタイムゾーンを指定してあげます。ここ注意ですね。

SELECT
 CURRENT_DATE("Asia/Tokyo")
;
------------
2020-12-09

Redshift

CURRENT_DATE関数

戻り値の型:DATE

select
 current_date
;
------------
2020-12-09

現在日時の取得方法まとめ

SQL Server BigQuery Redshift
日時 GETDATE() CURRENT_DATETIME() sydate
getdate()
日付 なし CURRENT_DATE() current_date

日付型 → 文字型

日付型のデータを文字列に変換する方法について記載します。

SQL Server

年月日(YYYY/MM/DD形式)

SELECT
 CONVERT(nvarchar, getdate(), 111)
;
------------
2020/12/10

年月日(YYYYMMDD形式)

SELECT
 CONVERT(nvarchar, getdate(), 112)
;
------------
20201210

年月日時分秒(yyyy-mm-dd hh:mi:ss.mmm (24h))

SELECT
 CONVERT(nvarchar, getdate(), 21)
;
------------
2020-12-10 16:34:37.837

年月日時分秒(yyyy-mm-ddThh:mi:ss.mmm形式(ISO8601標準))

SELECT
 CONVERT(nvarchar, getdate(), 126)
;
------------
2020-12-10T16:30:05.690

BigQuery

DATE型

FORMAT_DATE(format_string, date_expr)

指定されたformat_string(形式設定要素)に従ってdate_exprをフォーマットします。

DATE型でサポートされる形式設定要素

形式設定要素 説明
%Y 10 進数として表示される、世紀を含む年。
%y 10 進数(00-99)として表示される年。世紀は含みません。
%m 0 進数として表示される月(01~12)。
%d 10 進数として表示される、月内の日付(01~31)。
%F %Y-%m-%d 形式の日付。

年月日(YYYYMMDD形式)

SELECT
 FORMAT_DATE("%Y%m%d", CURRENT_DATE())
;
------------
20201210

年月日(YYYY-MM-DD形式)

SELECT
 FORMAT_DATE("%F", CURRENT_DATE())
;
------------
2020-12-10

TIMESTAMP型

FORMAT_TIMESTAMP(format_string, timestamp[, timezone])

指定されたformat_string(形式設定要素)に従ってtimestampをフォーマットします。

タイムゾーンを指定すると指定したタイムゾーンに変換されて表示されます。

タイムゾーン名

TIMESTAMP型でサポートされる形式設定要素

形式設定要素 説明
%Y 10 進数として表示される、世紀を含む年。
%y 10 進数(00-99)として表示される年。世紀は含みません。
%m 10 進数として表示される月(01~12)。
%d 10 進数として表示される、月内の日付(01~31)。
%H 10 進数で表示される時間(24 時間制)(00~23)。
%M 10 進数として表示される分(00~59)。
%S 10 進数として表示される秒(00~60)。
%F %Y-%m-%d 形式の日付。
%T %H:%M:%S 形式の時刻。
%Z タイムゾーンの名前。
%z 必要に応じて +HHMM または -HHMM の形式で示されるグリニッジ子午線からのオフセット。

年月日時分秒(yyyy-mm-dd hh:mi:ss.mmm (24h))

SELECT
 FORMAT_TIMESTAMP("%Y-%m-%d %H:%M:%S", CURRENT_TIMESTAMP(), "Asia/Tokyo")
;
------------
2020-12-10 17:13:13

Redshift

TO_CHAR (timestamp_expression, 'format')

日時形式の文字列

形式設定要素 説明
YYYY 4 桁の年数
MM 月番号 (01~12)
DD 日にちを数字表示 (01–31)
HH24 時 (24 時間制、00–23)
MI 分 (00–59)
SS 秒 (00–59)

年月日時分秒(yyyy-mm-dd hh:mi:ss.mmm (24h)) UTC

select
 to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') 
;
------------
2020-12-10 08:13:13

年月日時分秒(yyyy-mm-dd hh:mi:ss.mmm (24h)) JTC

select
,to_char(convert_timezone('Asia/Tokyo', sysdate), 'YYYY-MM-DD HH24:MI:SS') 
;
------------
2020-12-10 17:13:13

文字型 → 日付型

SQL Server

datetime型

日付だけ指定した場合は時分秒は0時0分0秒となります。

SELECT
 CONVERT(datetime, '2020/12/10')
;
------------
2020-12-10T00:00:00

日付のスラッシュ(/)は省略することもできます。

SELECT
 CONVERT(datetime, '20201210')
;
------------
2020-12-10T00:00:00

時分秒を指定したい場合は日付の後にスペースを開けて、時:分:秒を付けます。

SELECT
 CONVERT(datetime, '2020/12/10 12:15:30')
;
------------
2020-12-10T12:15:30

BigQuery

DATE型

CASTを使います。 年月日は-ハイフンで区切ります。 2020/12/10のように/スラッシュで区切ったり、20201210のように区切らない場合はエラーになります。

SELECT
 CAST('2020-12-10' AS DATE)
;
------------
2020-12-10

TIMESTAMP型

日付だけ指定した場合は時分秒は0時0分0秒となります。 また、タイムゾーンUTCになります。

SELECT
 CAST('2020-12-10' AS TIMESTAMP)
;
------------
2020-12-10 00:00:00 UTC

時分秒を指定したい場合は日付の後にスペースを開けて、時:分:秒を付けます。

SELECT
 CAST('2020-12-10 12:15:30' AS TIMESTAMP)
;
------------
2020-12-10 12:15:30 UTC

タイムゾーンを指定したい場合は+09のようにUTCからの時差を指定します。

SELECT
 CAST('2020-12-10 12:15:30+09' AS TIMESTAMP)
;
------------
2020-03-10 12:15:30 UTC

Redshift

DATE型

TO_DATE (string, format)

引数には、変換したい文字列とそのフォーマットを指定します。

SELECT
 TO_DATE('2020/12/10', 'YYYY/MM/DD')
;
------------
2020-12-10

フォーマットの方法によって変換したい文字列の形式を指定できます。

SELECT
 TO_DATE('2020-12-10', 'YYYY-MM-DD')
;
------------
2020-12-10

こんなことでも大丈夫です。

SELECT
 TO_DATE('2020###12$$$10', 'YYYY###MM$$$DD')
;
------------
2020-12-10

CASTを使うこともできます。

SELECT
 CAST('2020-12-10' AS DATE)
;
------------
2020-12-10

TIMESTAMP型

日付だけ指定した場合は時分秒は0時0分0秒となります。

SELECT
 CAST('2020-12-10' AS TIMESTAMP)
;
------------
2020-12-10 00:00:00

年月日の区切りは/でも大丈夫です。

SELECT
 CAST('2020/12/10' AS TIMESTAMP)
;
------------
2020-12-10 00:00:00

区切り文字がなくても大丈夫です。

SELECT
 CAST('20201210' AS TIMESTAMP)
;
------------
2020-12-10 00:00:00

時分秒を指定したい場合は日付の後にスペースを開けて、時:分:秒を付けます。

SELECT
 CAST('2020-12-10 12:15:30' AS TIMESTAMP)
;
------------
2020-12-10 12:15:30

f:id:enigmo7:20201212154555p:plain

最後に

この記事では、SQL Server、BigQuery、Redshiftの3つのデーターベースを対象に、日付および時刻関連のデータ型についてまとめました。 日頃、私は上記のデータベースを使い分けている、それもおおよそ均等に使っているような状況なので、特に日付型の関数についてはよく迷ったりしています。 何度bigquery 日付 文字列 変換でググったことか。 今回、このように整理することで、迷ったときはこの記事を参照することで、少しでも生産性を高められたらと思っています。 記載したSQLについては、実際に実行した上で確認していますが、データーベースのバージョンの違いなどによってエラーになったり、そもそも間違っていたりする可能性もあるので、その場合はコメントなどに記載いただければ、修正や補足など入れていきたいと思っています。


株式会社エニグモ 正社員の求人一覧

hrmos.co