PostgreSQLスキルアップノート（自己啓発のための個人サイト）
日付時刻データ型・日付時刻の書式加工
【一覧に戻る】
マニュアルへのリンクは/9.2/としています。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
■■■■ PostgreSQL スキルアップノート
■■■■
■◆■■ 日付時刻データ型・日付時刻の書式加工
■■■■
■■■■
■■■■ 使用環境：PostgreSQL9.1.7 (CentOS6.2)
■■■■
2013-03-10
2013-03-13：修正
(C) 2013 ohdb
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
日付時刻の書式をきめ細かく加工したい場合や日付・時刻型への変換方法など。
【マニュアル】
第9章関数と演算子／データ型書式設定関数→●[マニュアル]
第9章関数と演算子／日付/時刻関数と演算子→●[マニュアル]
【マニュアル参考】
第8章データ型／日付/時刻データ型→●[マニュアル]
【その他】
−
■１■ 文字列を日付時刻に変換する方法
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
戻る値はそれぞれのデータ型になる。
日付時刻型のカラムへ格納したい場合などに使用する。
１．to_date,to_timestamp関数等で日付時刻データ型へ変換
──────────────────────────────
データ型書式設定関数→●[マニュアル]
【□】 select to_date('2013-03-09','YYYY-MM-DD');
2013-03-09
【□】 select to_date('09 Mar 2013', 'DD Mon YYYY');
2013-03-09
【□】 select to_timestamp('2013-03-09 21:30:59','YYYY-MM-DD HH24:MI:SS');
2013-03-09 21:30:59+09
・・・to_timestamp関数自体の戻り値は「timestamp with time zone」のため「+9」がつく
【□】 select to_timestamp('2013-03-09 21:30:59','YYYY-MM-DD HH24:MI:SS')::timestamp;
2013-03-09 21:30:59
・・・to_timestampしたものをtimestampでキャスト。
timestamp型は「whithout time zone」なので+9はつかない
【□】 select to_date('03月09日(2013年)でした','MM月DD日(YYYY年)でした');
2013-03-09
２．型キャストで日付時刻データ型へ変換
──────────────────────────────
書式を明示指定しなくても日付時刻として認識できる場合（→●[サイト内]）は型キャストを使って変換すればよい。
型キャストは少数秒の値を秒単位に丸めるなどの用途にも使用できる。(timestamp(0)でキャスト）
型キャスト→●[マニュアル]
他の型の定数→●[マニュアル]
キャストには全部で４通りの方法があり、マニュアル日付時刻型の章では4)の方法で書かれているが
制約等もあり、日付時刻の入力時に使用する型キャストは1)か2)が一般的。
○ 1) 'string'::type ・・postgres独自
○ 2) CAST ( 'string' AS type ) ・・標準SQL準拠
3) typename ( 'string' )
4) type 'string' ・・リテラルのみに限る。配列動作制約あり。
2)の方法が標準SQLのため本来は望ましいが、
1)のコロンを使う方法の方が扱いやすい。
標準SQLによるCAST 上記2)の方法
【□】 select CAST('2013-03-09 21:30:59' AS timestamp); -- 2013-03-09 21:30:59
PostgreSQL独自 上記1)の方法
【□】 select '2013-03-09 21:30:59'::timestamp; -- 2013-03-09 21:30:59
【□】 select '2013-03-09 21:30:59'::timestamp with time zone; -- 2013-03-09 21:30:59+09
【□】 select '2013-03-09 21:30:59'::date; -- 2013-03-09
【□】 select current_timestamp; -- 2013-03-10 01:17:09.691635+09
【□】 select current_timestamp::timestamp; -- 2013-03-10 01:17:22.059385
・・・timestampでキャストすることで暗黙でwithout time zone。(+9が無し）
【□】 select current_timestamp::timestamp(0); -- 2013-03-09 21:30:59
・・・timestamp(0)でキャストすることで秒単位となる。（小数秒は四捨五入）
【□】 select current_timestamp::timestamp(1); -- 2013-03-09 21:30:59.1
【□】 select current_timestamp::timestamp(3); -- 2013-03-09 21:30:59.111
■２■ 日付時刻の出力書式を加工する方法
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
（参考）何も加工しない場合
Datestyleが「ISO,xxx」の環境にて何も加工しないで出力した例
select current_date; ・・・ 2013-03-09
select current_timestamp; ・・・ 2013-03-09 23:13:35.190397+09
１．to_char関数で整形
──────────────────────────────
データ型書式設定関数→●[マニュアル]
【□】 select to_char(current_date,'YYYY-MM-DD');
2013-03-09
【□】 select to_char(current_timestamp,'HH24:MI:DD');
23:12:09
【□】 select to_char(current_timestamp,'YYYY-MM-DD HH24:MI:SS');
2013-03-09 23:19:32
【□】 select to_char(date '2013-03-09','YYYY年MM月DD日');
2013年03月09日
【□】 select to_char(timestamp '2013-03-09','YYYY年MM月DD日');
2013年03月09日
【□】 select to_char('2013-03-09'::date,'YYYY年MM月DD日');
2013年03月09日
２．date_truncで整形
──────────────────────────────
日付/時刻関数→●[マニュアル]
整形といってもto_char関数のように自由に整形できるわけではなく、主に時刻などの切捨て用途である。
戻り値の型もto_charとは異なる。
戻り値
基本的に一部を除き、戻り値はtimestamp型になる。（マニュアル）
date型を引数で与えても戻り値はtimestamp型。
★正しくは戻り値はtimestamp with time zoneとなる模様。
timestamp型という以上、「without」のはずなので、
マニュアルの表 9-27（date_trunc）の戻り値型は明らかに間違っている・・（？？）
【□】 select date_trunc('second',current_timestamp);
2013-03-13 20:42:55+09
【□】 select date_trunc('second',current_timestamp)::timestamp(0);
2013-03-13 20:43:31
【□】 select date_trunc('month',current_date);
2013-03-01 00:00:00+09
この他に指定できる主なもの
second
minute
hour
day
week
month
year
３．型キャストで整形
──────────────────────────────
整形といってもto_char関数のように自由に整形できるわけではなく、主に小数秒などの丸め用途である。
が、記述量が少なく扱いやすい。
戻り値の型もto_charとは異なる。
小数秒をtimestamp(0)で秒単位にキャストすると、date_truncやto_charとは異なり、四捨五入されるようなので注意。
内容自体は■１■と同じ。
【□】 select current_timestamp::timestamp(0);
2013-03-13 21:04:20
【□】 select current_timestamp::date;
2013-03-13
【□】 select '2013-03-13 23:59:59.5'::timestamp;
■３■ 秒単位への加工方法による結果の違い
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
前項でも一部触れたとおり、
timestamp型のカラムの値を秒単位にして取り出す際、加工方法によって取り出した時刻
が異なる場合が発生する。
小数秒を切り捨てが行われるか、四捨五入されるかの違いから発生する。
小数秒をもつ必要がないのに、安易に「timestamp」とだけ定義することは余計なゴミを
持ち込んで扱いに苦慮するだけになりかねない？
【□】 テーブル作成・格納
drop table t1;
CREATE TABLE t1 (c1 timestamp);
INSERT INTO t1 VALUES ('2013-03-09 23:59:59.49'::timestamp);
INSERT INTO t1 VALUES ('2013-03-09 23:59:59.50'::timestamp);
【□】 値を参照
SELECT
c1,
c1::timestamp(0) as c1_cast,
to_char(c1,'YYYY-MM-DD HH24:MI:SS') as c1_tochar,
date_trunc('second', c1) as c1_trunc
FROM t1;
c1 | c1_cast | c1_tochar | c1_trunc
------------------------+---------------------+---------------------+---------------------
2013-03-09 23:59:59.49 | 2013-03-09 23:59:59 | 2013-03-09 23:59:59 | 2013-03-09 23:59:59
2013-03-09 23:59:59.5 | 2013-03-10 00:00:00★ 2013-03-09 23:59:59 | 2013-03-09 23:59:59
項目名 内容 戻り値の型 備考
-----------------------------------------------------------------------------
c1 格納元データ timestamp
c1_cast c1をtimestamp(0)でキャスト timestamp(0) 四捨五入★
c1_tochar to_char関数で整形したもの text 切捨て
c1_trunc date_trunc関数で秒単位にしたもの timestamp 切捨て
【参考】select単体でも同様です。
SELECT
current_timestamp as c1,
current_timestamp::timestamp(0) as c1_cast,
to_char(current_timestamp,'YYYY-MM-DD HH24:MI:SS') as c1_tochar,
date_trunc('second', current_timestamp) as c1_runc;
■４■ timestamp(0)、dateの型キャストで小数秒の丸め方が違う
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
型キャストtimestamp(0)を使って秒単位へ丸めようとすると小数秒は四捨五入される。
しかし同じ型キャストでもdate型の場合は切捨てになる。
（というよりも、dateの型キャストではおそらく小数秒の部分などは一切見ていないのだろう・・）
同じ時刻データをそれぞれキャストすると
select
'2013-03-13 23:59:59.5'::timestamp(0),
'2013-03-13 23:59:59.5'::date;
timestamp | date
---------------------+------------
2013-03-14 00:00:00 | 2013-03-13
実際に考えられる問題
current_timestampやstatement_timestampなどで取り出した時刻データを、
ある用途にはcurrent_timestamp::timestamp(0)でキャストし、
一方で別の用途ではcurrent_timestamp::dateなどというような使い方をすると、
同じ時刻でも日付が変わってしまうケースが発生する。
以上
