この記事はEnigmo Advent Calendar 2020 の14日目の記事です。
はじめに
こんにちは、エニグモ 嘉松です。
簡単な自己紹介ですが、BUYMAのプロモーションやマーケティングを行っている事業部に所属して、その中のデータ活用推進室という部署で会社のデータ活用の推進やマーケティング・オートメーションツール(MAツール)を活用した販促支援、CRMなどを担当しています。(データ活用推進室、長らく私一人部署だったのですが、先月1名増えて2名体制になりました!)
目次
背景
エニグモのデータ活用の大きな特徴として、エンジニアに限らず、マーケターやマーチャンダイザー(MD)、カスタマーサポートや役員まで、多くの社員、ほとんどの社員と言っても過言では無いくらいの人が自分でSQLを叩いてデータを見る、分析するという文化、カルチャーが根付いているということが言えると思います。
また、データ活用基盤の整備も積極的に進めており、クラウドで提供されているビックデータ向けのデータベースをデータレイクやデータウェアハウス(DWH)として利用しています。
このように複数のデータベースを活用してく中で出てくる問題点が、SQLのシンタックスの違いです。 特にエニグモではエンジニアでは無いユーザもたくさんいるので、SQLの作成に多くの時間がとられてしまうと、本来の業務へも影響がでてきてしまいますし、データ活用は停滞してしまいます。
そこで、この記事ではMicrosoftが提供しているSQLServer、Googleが提供しているフルマネージド型分析データウェアハウスであるBigQuery、Amazon Web Services(AWS)のクラウド型データウェアハウスであるRedshiftの3製品を対象として、特に混乱するであろう日付および時刻関連のデータ型について整理することで、今後のリファレンスになればと思っています。
日付および時刻関連のデータ型
まず、ここでは各データベースの日付および時刻関連のデータ型(の代表的なもの)を列挙します。
※データ型の表記(大文字小文字)、説明の内容については各データベースのマニュアルにおおよそ準拠しています。
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
最後に
この記事では、SQL Server、BigQuery、Redshiftの3つのデーターベースを対象に、日付および時刻関連のデータ型についてまとめました。
日頃、私は上記のデータベースを使い分けている、それもおおよそ均等に使っているような状況なので、特に日付型の関数についてはよく迷ったりしています。
何度bigquery 日付 文字列 変換
でググったことか。
今回、このように整理することで、迷ったときはこの記事を参照することで、少しでも生産性を高められたらと思っています。
記載したSQLについては、実際に実行した上で確認していますが、データーベースのバージョンの違いなどによってエラーになったり、そもそも間違っていたりする可能性もあるので、その場合はコメントなどに記載いただければ、修正や補足など入れていきたいと思っています。
株式会社エニグモ 正社員の求人一覧