最近
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.
幸いエラーの後の
記述が
select current_setting('datestyle'); +-----------------+ | current_setting | +-----------------+ | ISO, MDY | +-----------------+ (1 row)
ISO
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 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 | +-----------------+
先ほどリンク先を挙げたドキュメントで、出力形式は
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';
上の
# 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
• 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
というわけで、もし年月日の表示や入力であれっと思った時は