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

はじめに

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

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

はじめに
目次
背景
日付および時刻関連のデータ型
現在日時（日付と時間）の取得方法
現在日付の取得方法
日付型 → 文字型
文字型 → 日付型
- SQL Server
  - datetime型
- BigQuery
  - DATE型
  - TIMESTAMP型
- Redshift
  - DATE型
  - TIMESTAMP型
最後に

背景

エニグモのデータ活用の大きな特徴として、エンジニアに限らず、マーケターやマーチャンダイザー（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については、実際に実行した上で確認していますが、データーベースのバージョンの違いなどによってエラーになったり、そもそも間違っていたりする可能性もあるので、その場合はコメントなどに記載いただければ、修正や補足など入れていきたいと思っています。

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

hrmos.co

はじめに

目次

背景

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

SQL Server

BigQuery

Redshift

タイムゾーンとは？

データ型まとめ

現在日時（日付と時間）の取得方法

SQL Server

GETDATE関数

BigQuery

CURRENT_TIMESTAMP関数

Redshift

SYSDATE関数

GETDATE関数

現在日付の取得方法

SQL Server

BigQuery

CURRENT_DATE関数

Redshift

CURRENT_DATE関数

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

日付型 → 文字型

SQL Server

年月日（YYYY/MM/DD形式）

年月日（YYYYMMDD形式）

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

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

BigQuery

DATE型

TIMESTAMP型

Redshift

文字型 → 日付型

SQL Server

datetime型

BigQuery

DATE型

TIMESTAMP型

Redshift

DATE型

TIMESTAMP型

最後に