Redshiftの日時型を試してみた

背景・目的

Redshiftのドキュメントの手順に倣い、RedshiftのデータをS3にUNLOADする。

内容

概要

• 日時データ型には DATE、TIME、TIMETZ、TIMESTAMP、TIMESTAMPTZ がある。
• 以下に特徴を整理する。

名称(エイリアス)	名称	用途	ストレージ	解像度	デフォルトタイムゾーン
DATE	-	タイムスタンプなしで単純にカレンダー日付だけを保存する場合	4バイト	日	-
TIME	TIME WITHOUT TIME ZONE	時刻を保存する場合	8バイト	マイクロ秒	UTC
TIMETZ	TIME WITH TIME ZONE	時刻とタイムゾーンを保存する場合	8バイト	マイクロ秒	UTC
TIMESTAMP	TIMESTAMP WITHOUT TIME ZONE	日付と時刻を含む完全なタイムスタンプ値を保存する場合	8バイト	マイクロ秒	UTC
TIMESTAMPTZ	TIMESTAMP WITH TIME ZONE	日付、時刻、タイムゾーンを含む完全なタイムスタンプ値を入力する場合	8バイト	マイクロ秒	UTC

実践

事前準備

CREATE DATABASE datetestdb WITH OWNER = {ユーザ名}

• 今回の検証用のデータベース「datatestdb」を予め作成しておく。

日時型を使用する例

• 上記を元に実際に動かしてみる。

日付の例

• テーブルの作成と確認

create table datetable (start_date date, end_date date);
SELECT * FROM pg_table_def WHERE tablename = 'datetable';

-- 結果
schemaname,tablename,column,type,encoding,distkey,sortkey,notnull
public,datetable,start_date,date,az64,false,0,false
public,datetable,end_date,date,az64,false,0,false

• INSERTと確認その1
• 下記の通り、入力形式でハイフン付きと、それ以外でINSERTしてみる。

insert into datetable values ('2008-06-01','2008-12-31');
insert into datetable values ('Jun 1,2008','20081231');

select * from datetable order by 1;

-- 結果
start_date,end_date
2008-06-01,2008-12-31
2008-06-01,2008-12-31

• デフォルトの出力結果としては、ハイフン付きで表示されるようだ。

• INSERTと確認その２

• DATE型に対してタイムスタンプ値を含めてINSERTしてみる。

insert into datetable values ('2022-03-17 22:00:00','2022-03-17 22:00:00');

select * from datetable order by 1;

-- 結果
start_date,end_date
2008-06-01,2008-12-31
2008-06-01,2008-12-31
2022-03-17,2022-03-17

マニュアルにも書いてあるとおり、Date型にINSERTした場合、時刻部分が無視され、日付のみロードされた。

タイムスタンプの例

• 日付を TIMESTAMP 列または TIMESTAMPTZ 列に挿入すると、時刻はデフォルトでは午前 0 時になる。
• テーブルの作成と確認

create table tstamp(timeofday timestamp, timeofdaytz timestamptz);
SELECT * FROM pg_table_def WHERE tablename = 'tstamp';

--- 結果
schemaname,tablename,column,type,encoding,distkey,sortkey,notnull
public,tstamp,timeofday,timestamp without time zone,az64,false,0,false
public,tstamp,timeofdaytz,timestamp with time zone,az64,false,0,false

• INSERTと確認その1
• 下記の通り、入力形式でハイフン付きと、それ以外でINSERTしてみる。

insert into tstamp values('Jun 1,2008  09:59:59', 'Jun 1,2008 09:59:59 EST' );
insert into tstamp values('Dec 31,2008 18:20','Dec 31,2008 18:20');
insert into tstamp values('Jun 1,2008  09:59:59 EST', 'Jun 1,2008 09:59:59');

select * from tstamp order by 1,2

--- 結果
timeofday,timeofdaytz
2008-06-01 09:59:59,2008-06-01 09:59:59+00
2008-06-01 09:59:59,2008-06-01 14:59:59+00
2008-12-31 18:20:00,2008-12-31 18:20:00+00

• INSERTと確認その2
• timestamp timestamptzに対して、日付のみINSERTした場合の挙動を確認する。

insert into tstamp values('2022-03-18','20220318');

select * from tstamp order by 1,2

--- 結果
timeofday,timeofdaytz
2008-06-01 09:59:59,2008-06-01 09:59:59+00
2008-06-01 09:59:59,2008-06-01 14:59:59+00
2008-12-31 18:20:00,2008-12-31 18:20:00+00
2022-03-18 00:00:00,2022-03-18 00:00:00+00

マニュアルにも書いてあるとおり、timestamp timestamptzにINSERTした場合、デフォルトで00:00:00がロードされる。

特殊な日時値

• 上記を元に試してみる。

select dateadd(day,1,'today');

---結果
2022-03-18 00:00:00

select dateadd(day,1,'now');

---結果
2022-03-18 14:12:39.134862

考察

• 日時型にも、日付、日時、タイムゾーンの有無など複数の型がある。
• 今後は、日付関数を試す予定。

参考