PostgreSQLスキルアップノート（自己啓発のための個人サイト）
日付時刻データ型・入力出力書式編
【一覧に戻る】
マニュアルへのリンクは/9.2/としています。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
■■■■ PostgreSQL スキルアップノート
■■■■
■◆■■ 日付時刻データ型・入力出力書式編
■■■■
■■■■
■■■■ 2013/03/07
■■■■ 使用環境：PostgreSQL9.1.7 (CentOS6.2)
(C) 2013 ohdb
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
【マニュアル】
第8章データ型／日付/時刻データ型→●[マニュアル]
第 18章サーバの設定／クライアント接続デフォルト／（DateStyle）→●[マニュアル]
第9章関数と演算子／データ型書式設定関数→●[マニュアル]
第9章関数と演算子／日付/時刻関数と演算子→●[マニュアル]
【マニュアル参考】
付録 B. 日付/時刻のサポート→●[マニュアル]
【その他】
ISO 8601→●[その他]
第8章データ型／日付/時刻データ型→●[マニュアル]
■１■ 要約・結論
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
学習した結果（結論）を以下に要約しています。
DateStyle周辺の挙動がはじめ理解しづらく、まだ誤解、考慮漏れがあるかもしれません。
１．PostgreSQLを使用する上で無難な書式は？
──────────────────────────────
小数秒を取り込まず、タイムゾ−ン不要を前提とした場合の一例
以下のような入力、出力で統一するのが相性がよい。
また、ISO 8601ベースであるため、PostgreSQL以外で採用しても好ましい書式である。
・日付 2013-03-09 YYYY-MM-DD
・時刻 21:30:59 HH24:MI:SS
・日付時刻 2013-03-09 21:30:59 YYYY-MM-DD HH24:MI:SS (日付と時刻の間にTなし）
（補足）ISO 8601では日付と時間の間に"T"をいれるが、PostgreSQLは入力は可能
であるが出力時に"T"は基本的に使用しない。
（参考）「YYYY/MM/DD」という書式・・
・ISOにないので事情がなければハイフンYYYY-MM-DDの方が適切。
・また、PostgreSQLが提供する出力書式にYYYY/MM/DDは存在しない。
入力は問題ないが、出力時に若干手間になり、あえて採用するメリットはない。
２．DateStyle設定（postgresql.conf）
──────────────────────────────
postgresql.confをデフォルトから以下に変更することで、ISO書式との整合がより適切になる。
datestyle = 'iso, ymd'
デフォルトdatestyle = 'iso, mdy' のままだと、
YYYY-MM-DD（つまり年-月-日の順）を方針としているデータベース環境なのに、
年を２桁で入力した場合、意図しない日付が格納されてしまう危険がある。
例：'12/01/20'が2012年ではなく2020年と解釈される・・
コーディングや作業ルールを(年4桁で）明確にすればmdyのままでも問題ないが、
念のため datestyle = 'iso, ymd' とした方が無難。
■２■ 日付時刻値の書式設定「DateStyle」
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
・DateStyleは日付時刻値の出力や入力に使用されるパラメータ。
・設定には２つの意味を持たせており、２つをカンマで区切って設定する。
例 datestyle = 'iso, mdy'
1) 出力書式指定 ・・・４種類
指定 出力例 備考
-------------------------------------------------------------------------
ISO 2013-03-09 19:38:39.047421+09 ISO8601及び標準SQLで規定
SQL 03/09/2013 19:38:00.47038 JST
Postgres Sat Mar 09 19:39:07.880421 2013 JST
German 09.03.2013 19:39:38.185425 JST
・SQLとPostgres指定の場合に限り月日の順番が下の設定DMYで変化する。
2) 年・月・日の順序の入出力指定・・・３種類
この設定は「入出力」の設定とあるが事実上は「入力時の曖昧回避」に限定したものと
考えた方がよい。（本当は違う）
ISOとセットで設定する限り、出力には影響しない。 （後述参照）
DMY ・・・日月年の順
MDY ・・・月日年の順
YMD ・・・年月日の順
・設定できる箇所・設定方法
設定箇所 設定例
------------------------------------------------------------------------------
postgresql.confのDateStyle datestyle = 'iso, mdy' （デフォルト）
PGDATESTYLE環境変数 export PGDATESTYLE='iso,mdy' （server側,client側）
SET DATESTYLE set datestyle to iso,mdy;
設定は順番に上書きされていくので、最終的にセッションで現在有効になっている
設定は show datestyle; で確認するのが確実。
■３■ 日付時刻の「入力」
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
・日付/時刻の入力は適正とみなされるような書式なら柔軟に受け付ける。
・ただし日-月-年の順なのかどうかなど判断つかないような形式の場合、PostgreSQLは、
DateStyleの設定(DMY等）に従った解釈をする。
・したがって常にDateStyleMDYの順どおりに入力しなければならないというものではない。
・日付時刻を入力する際の基本
以下のような型キャストにより、文字列を指定の型にして入力を行う。
SELECT '2013/03/09'::date;
SELECT '2013-03-09 21:30:59.123'::timestamp(3);
SELECT '2013-03-09 21:30:59.123'::timestamp(0) with time zone;
SELECT CAST('2013-03-09 21:30:59' AS timestamp(0)) ;
SELECT CAST('2013-03-09 21:30:59' AS date);
（補足）入力の際に用いることができるキャスト方法
型キャスト→●[マニュアル]
他の型の定数→●[マニュアル]
○ 1) 'string'::type ・・postgres独自
○ 2) CAST ( 'string' AS type ) ・・標準SQL準拠
3) typename ( 'string' )
4) type 'string' ・・リテラルのみに限る。配列動作制約あり。
キャストには全部で４通りの方法があり、マニュアル日付時刻型の章では4)の方法で書かれている。
しかしこの方法はいくつかの制限があるため、通常、日付時刻での入力は1)か2)が一般的。
2)の方法が標準SQLのため本来は望ましいが、1)の方法が扱いやすい。
■４■ 日付時刻の「出力」
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
ここでは出力形式として事実上の推奨である「ＩＳＯ」指定のみを取り上げる。
以外の指定(SQL,Postgers,German）の必要性は、少なくとも日本では低いと思われる。
・ISO指定の場合の出力例
select current_date; -- 2013-03-09
select current_timestamp; -- 2013-03-09 19:07:34.790391+09
select current_timestamp::timestamp(0); -- 2013-03-09 19:07:34
select current_time; -- 19:07:34.388375+09
select current_time::time(0); -- 19:07:34
（補足） ::timestamp(0)
小数秒なしのタイムスタンプ型にキャスト。
timestamp型へキャストすることでWITHOUT TIME ZONEが暗黙で指定されるので
タイムゾーン（+09の部分）もなしとなる。
（注意）timestampなど小数秒をもつデータを秒単位までにする場合、
上のようにキャストして秒単位にする場合と、
関数を使って秒単位にする場合とでは秒の丸め方が違い結果が変わってくる。
(詳細は別ページ)
・DMYの設定が出力に影響するかどうか
ISO指定等ではdmy,dmy,ymd何を設定しても出力の年月日は固定（YYYY-MM-DD)。
ISO指定等では、DMY指定はあくまでも入力時の曖昧回避のために使用される。
"SQL"や"Postgres"指定時にはDMY指定が出力にも影響する（詳細はマニュアル参照）
■４■ 日付時刻の出力を細かく制御したい場合
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
出力書式を細かく制御する場合は「データ型書式設定関数」にて行う。
本ページはここまでとし、この内容は別記を参照
以上
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
■■■■ 確認・検証メモ
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
以下は実機確認の内容です。
■■ 日付時刻の入力
【例１】 現在の設定がMDY。 ３月９日を入力したい場合。
──────────────────────────────
【□】 SET DATESTYLE TO 'iso,mdy';show datestyle;
DateStyle
-----------
ISO, MDY ←DateStyle設定は月・日・年の順
【□】 SELECT文により日付を入力
3月9日として
認識:○
-------------------------------------------------------------------------------------------------------
1) SELECT timestamp '2013-03-09 21:30:59'; ○ YMDの順でも3月9日と解釈
2) SELECT timestamp '09/03/13 21:30:59'; × DateStyle設定にしたがって9月3日と解釈
3) SELECT date '03/09/2013'; ○ DateStyle設定にしたがって3月9日と解釈
4) SELECT date '09/03/2013'; × DateStyle設定にしたがって9月3日と解釈
5) SELECT date '09 Mar 2013'; ○ DMYの順でも3月9日と正しく解釈
6) SELECT date '03/09/13'; ○ 3月9日と解釈
【例２】 現在の設定がDMY。 ３月９日を入力したい場合。
──────────────────────────────
【□】 SET DATESTYLE TO 'iso,dmy';show datestyle;
DateStyle
-----------
ISO, DMY ←DateStyle設定は日・月・年の順
【□】 SELECT文により日付を入力
3月9日として
認識:○
-------------------------------------------------------------------------------------------------------
1) SELECT timestamp '2013-03-09 21:30:59'; ○ 3月9日と解釈
2) SELECT timestamp '09/03/13 21:30:59'; ○ DateStyle設定にしたがって3月9日と解釈
3) SELECT date '03/09/2013'; × DateStyle設定にしたがって9月3日と解釈
4) SELECT date '09/03/2013'; ○ DateStyle設定にしたがって3月9日と解釈
5) SELECT date '09 Mar 2013'; ○ 3月9日と解釈
6) SELECT date '03/09/13'; × 9月3日と解釈
【例３】 現在の設定がYMD。 ３月９日を入力したい場合。
──────────────────────────────
【□】 SET DATESTYLE TO 'iso,ymd';show datestyle;
show datestyle;
ISO, YMD ←DateStyle設定は年・月・日の順
3月9日として
認識:○
-------------------------------------------------------------------------------------------------------
1) SELECT timestamp '2013-03-09 21:30:59'; ○ 3月9日と解釈
2) SELECT timestamp '03.09.13 21:30:59'; × DateStyle設定にしたがって2003年9月13日と解釈
3) SELECT date '03/09/2013'; × DateStyle設定にしたがって日を2013と解釈しエラー
4) SELECT date '09/03/2013'; × DateStyle設定にしたがって日を2013と解釈しエラー
5) SELECT date '09 Mar 2013'; ○ 3月9日と解釈
6) SELECT date '03/09/13'; × DateStyle設定にしたがって2003年9月13日と解釈
【補足】エラーの場合の出力例
postgres=# SELECT date '03/09/2013';
ERROR: date/time field value out of range: "03/09/2013"
行 1: SELECT date '03/09/2013';
^
HINT: Perhaps you need a different "datestyle" setting.
■■ 日付時刻の入力・その２
システムとしての出力がYMDの順（YYYY-MM-DD)の場合にやはり
DatestyleデフォルトのMDYをYMDに合わせておくべきかどうか。
１．デフォルト状態
──────────────────────────────
【□】 show datestyle;
DateStyle
-----------
ISO, MDY
(1 行)
2012年1月13日という日付を入れようとしたが、これで大丈夫と思い、年の上２桁を省略して入力した・・・
【□】 SELECT date '12/01/20';
date
------------
2020-12-01
(1 行)
・・・意図しない日付2020年12月1日で格納されてしまう。
もちろん年を４桁きちんといれればmdy設定であってもyyyy-mm-ddで入力して問題ない。
あくまで年を２桁で格納させようとした場合の問題となる。
２．YMDに変更した状態
──────────────────────────────
【□】 SET DATESTYLE TO 'iso,ymd';show datestyle;
SET
DateStyle
-----------
ISO, YMD
(1 行)
【□】 SELECT date '12/01/20';
date
------------
2012-01-20
(1 行)
・・・意図したとおりに格納される。
■■ 日付時刻の出力（ＩＳＯ設定のみ確認）
postgres=# show datestyle;
DateStyle
-----------
ISO, MDY
(1 行)
postgres=# select current_date;
date
------------
2013-03-09
(1 行)
postgres=# select current_timestamp;
now
------------------------------
2013-03-09 20:16:42.00972+09
(1 行)
postgres=# select current_timestamp::timestamp(0);
now
---------------------
2013-03-09 20:16:42
(1 行)
postgres=# select current_time;
timetz
--------------------
20:16:42.012093+09
(1 行)
postgres=# select current_time::time(0);
time
----------
20:16:42
(1 行)
以上
