背景・目的
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
考察
- 日時型にも、日付、日時、タイムゾーンの有無など複数の型がある。
- 今後は、日付関数を試す予定。
参考