最近MSYS2 + MinGW-w64の長い記事が続いたので(少し疲れた)、今日は短く。日付を直接入力する時、常にYYYY-MM-DDで書く必要はなく ↓ こんな感じにも書けます。各年元日の入力とかが結構楽。(以下9.5.3で動作確認)
values (date '0-1-2'), ('1-2-3'), ('2-3-4'),
('3-4-5'), ('4-5-6'), ('5-6-7'),
('10-9-8'), ('11-1-1');
+------------+
| column1 |
+------------+
| 2000-01-02 |
| 2001-02-03 |
| 2002-03-04 |
| 2003-04-05 |
| 2004-05-06 |
| 2005-06-07 |
| 2010-09-08 |
| 2011-01-01 |
+------------+
(8 rows)
ところが先日、同じように入力しようとしたら ↓ エラー。
values (date '0-1-2'), ('1-2-3'), ('2-3-4'),
('3-4-5'), ('4-5-6'), ('5-6-7'),
('10-9-8'), ('11-1-1');
ERROR: date/time field value out of range: "0-1-2"
LINE 1: values (date '0-1-2'), ('1-2-3'), ('2-3-4'),
^
HINT: Perhaps you need a different "datestyle" setting.
幸いエラーの後のHINTにdatestyleが~とあり、そういう設定項目の存在を初めて知りました。ドキュメントの該当ヶ所は下記。
Date/Time Output - Table 8-15. Date Order Conventions
Locale and Formatting - DateStyle
記述が2ヶ所に分かれTable 8-15の方にはYMD形式がないなど、少し分かりにくかったので実際にDateStyleを操作してみました。まず現在の設定は ↓ これで確認。クエリ上DateStyleの大文字小文字は関係ないようです。
select current_setting('datestyle');
+-----------------+
| current_setting |
+-----------------+
| ISO, MDY |
+-----------------+
(1 row)
ISOは表示(出力)形式、MDYは入力形式。後者が「月-日-年」の想定なので、先ほどの最初「0-1-2」がエラーになってました。その他はちゃんと日付になります。↓
values (date '1-2-3'), ('2-3-4'),
('3-4-5'), ('4-5-6'), ('5-6-7'),
('10-9-8'), ('11-1-1');
+------------+
| column1 |
+------------+
| 2003-01-02 |
| 2004-02-03 |
| 2005-03-04 |
| 2006-04-05 |
| 2007-05-06 |
| 2008-10-09 |
| 2001-11-01 |
+------------+
(7 rows)
変更はSETコマンドで。入力形式はMDYの他にYMD(年-月-日)、DMY(日-月-年)が可能。出力形式(ISO等)は省略でき、その場合は現在の設定が維持されました。
# set datestyle to 'YMD';
# select current_setting('datestyle');
+-----------------+
| current_setting |
+-----------------+
| ISO, YMD |
+-----------------+
# set datestyle to 'DMY';
# select current_setting('datestyle');
+-----------------+
| current_setting |
+-----------------+
| ISO, DMY |
+-----------------+
↓ 空文字を入力した場合、現在の設定が維持されるだけ。つまり「設定なし」状態は存在しません。
# set datestyle to '';
# select current_setting('datestyle');
+-----------------+
| current_setting |
+-----------------+
| ISO, DMY |
+-----------------+
先ほどリンク先を挙げたドキュメントで、出力形式は4つありました。年・月・日の入力3形式と合わせて12通りの組み合わせがあり、実際どうなるか一括して試します。まず12個のSQLを無名コードプロックのraise文で出力。↓
do $$
declare
param text;
sqls text[];
begin
for param in
select concat(a, ', ', b)
from unnest('{ISO, SQL, PostgreSQL, German}' :: text[]) as a,
unnest('{YMD, MDY, DMY}' :: text[]) as b
loop
sqls = sqls || format(
'set datestyle to %L; select date %L;', param, '1-2-3');
end loop;
raise info e'\n%', array_to_string(sqls, e'\n');
end;
$$;
INFO:
set datestyle to 'ISO, YMD'; select date '1-2-3';
set datestyle to 'ISO, MDY'; select date '1-2-3';
set datestyle to 'ISO, DMY'; select date '1-2-3';
set datestyle to 'SQL, YMD'; select date '1-2-3';
set datestyle to 'SQL, MDY'; select date '1-2-3';
set datestyle to 'SQL, DMY'; select date '1-2-3';
set datestyle to 'PostgreSQL, YMD'; select date '1-2-3';
set datestyle to 'PostgreSQL, MDY'; select date '1-2-3';
set datestyle to 'PostgreSQL, DMY'; select date '1-2-3';
set datestyle to 'German, YMD'; select date '1-2-3';
set datestyle to 'German, MDY'; select date '1-2-3';
set datestyle to 'German, DMY'; select date '1-2-3';
上のSQLを4種の表示形式に分けてpsqlに貼り付け実行した結果が ↓ こちら。1-2-3という文字が3通りの日付になってます(月と日が紛わしいので、末尾に整理)。
# set datestyle to 'ISO, YMD'; select date '1-2-3';
+------------+
| 2001-02-03 |
+------------+
# set datestyle to 'ISO, MDY'; select date '1-2-3';
+------------+
| 2003-01-02 |
+------------+
# set datestyle to 'ISO, DMY'; select date '1-2-3';
+------------+
| 2003-02-01 |
+------------+
# set datestyle to 'SQL, YMD'; select date '1-2-3';
+------------+
| 02/03/2001 |
+------------+
# set datestyle to 'SQL, MDY'; select date '1-2-3';
+------------+
| 01/02/2003 |
+------------+
# set datestyle to 'SQL, DMY'; select date '1-2-3';
+------------+
| 01/02/2003 |
+------------+
# set datestyle to 'PostgreSQL, YMD'; select date '1-2-3';
+------------+
| 02-03-2001 |
+------------+
# set datestyle to 'PostgreSQL, MDY'; select date '1-2-3';
+------------+
| 01-02-2003 |
+------------+
# set datestyle to 'PostgreSQL, DMY'; select date '1-2-3';
+------------+
| 01-02-2003 |
+------------+
# set datestyle to 'German, YMD'; select date '1-2-3';
+------------+
| 03.02.2001 |
+------------+
# set datestyle to 'German, MDY'; select date '1-2-3';
+------------+
| 02.01.2003 |
+------------+
# set datestyle to 'German, DMY'; select date '1-2-3';
+------------+
| 01.02.2003 |
+------------+
月と日が紛わしいというか、SQLとPostgreSQLでは表示・入力が合わさって変化するので間違いやすいです。整理すると ↓ こんな感じ。
ISO … 常に YYYY-MM-DD
SQL, YMD … MM/DD/YYYY
SQL, MDY … 〃
SQL, DMY … DD/MM/YYYY
PostgreSQL, YMD … MM-DD-YYYY
PostgreSQL, MDY … 〃
PostgreSQL, DMY … DD-MM-YYYY
German … 常に DD.MM.YYYY
というわけで、もし年月日の表示や入力であれっと思った時はDateStyleの設定を確認してみて下さい。