timestamp型データの保存

Python2.7でpgdbライブラリ使ってPostgres9.1.3に時刻データ(timestamp with time zone)保存するときにハマったので、その備忘録。

旧データベース定義

datetime	kind	num	comment	trend
timestamp without time zone	text	int	text	int

SQLはこんな感じ↓

CREATE TABLE hoge (datetime timestamp without time zone,kind text,num int,comment text,trend int);

状況

INSERT INTO hoge VALUES('2012-05-16 21:30:00','usd',2,'hogehoge',0);
error 'ERROR:  syntax error at or near "'2012-05-16 03:00:00'"
LINE 1: INSERT INTO hoge VALUES('2012-05-16 2...

解消

ネットで探していると、to_timestamp関数を使ってる例を見かけたので試してみた。

INSERT INTO hoge VALUES(to_timestamp('2012-05-16 21:30:00','YYYY-MM-DD HH:MI:SS'),'usd',2,'hogehoge',0);
error 'ERROR:  column "datatime" is of type timestamp without time zone[] but expression is of type timestamp with time zone
LINE 1: INSERT INTO hoge VALUES(to_timestamp('2012-05-16 2...
                                          ^
HINT:  You will need to rewrite or cast the expression.
' in 'INSERT INTO hoge VALUES(to_timestamp('2012-05-16 21:30:00','YYYY-MM-DD HH:MI:SS'),'usd',2,'hogehoge',0);'

エラーが変わってこれは行けそうだ！
どうやらPostgresはデフォルトでtimezoneが設定されていて、それでwithout time zoneのカラムには入らないらしい。
ということでテーブル定義を変えてみた。

新データベース定義

datetime	kind	num	comment	trend
timestamp(0) with time zone	text	int	text	int

そういえば、timestamp[0]って指定してエラーになったこともあったっけ。

ALTER TABLE hoge
    ALTER COLUMN datetime TYPE timestamp(0) with time zone;

で変えようと思ったが、この方法ではダメらしいということで新しくテーブル作成してからやり直した。
そして再び試してみると、

INSERT INTO hoge VALUES(to_timestamp('2012-05-16 21:30:00','YYYY-MM-DD HH:MI:SS'),'usd',2,'comment',0);
error 'ERROR:  hour "21" is invalid for the 12-hour clock
HINT:  Use the 24-hour clock, or give an hour between 1 and 12.
' in 'INSERT INTO hoge VALUES(to_timestamp('2012-05-16 21:30:00','YYYY-MM-DD HH:MI:SS'),'usd',2,'comment',0);'

はいはい。
ちなみにこの時

error 'ERROR:  current transaction is aborted, commands ignored until end of transaction block

っていうエラーも起きてた。

try:
    cur.execute(SQL)
except Exception,e:
    print e

という処理をしてたのが悪かったらしい。詳しくは他のところで調べてください。

話は戻って、SQLを↓のように変えたら無事OKだった！

INSERT INTO hoge VALUES(to_timestamp('2012-05-16 21:30:00','YYYY-MM-DD HH24:MI:SS'),'usd',2,'comment',0);

---追記
to_timestampを見かけたのはココ
トランザクションでエラーが起きた後、ロールバックしないとクエリを実行できない件の理由はココ