[postgis-users] Convert from character to timestamptz

[Mathieu Basille]

Dear PostGIS users,

I've been trying for hours with no result... My problem is that I import 
a simple shapefile (points) to PostGIS using shp2pgsql. In the attribute 
table, there is a 'date' column formatted as '2005-03-20 00:00:00 
EST5EDT'. This column is then imported as a 'character' column into 
PostGIS. Now, I'd like to convert this column to a real 'timestamp with 
time zone'. I tried many things like:

ALTER TABLE test
     ALTER COLUMN date SET DATA TYPE timestamptz;

or

SELECT TIMESTAMP WITH TIME ZONE 'date' FROM test;

and all return an error, while e.g.:

SELECT TIMESTAMP WITH TIME ZONE '2005-03-20 00:00:00 EST5EDT';

perfectly works... I also tried:

SELECT to_timestamp(date, 'YYYY-MM-DD HH24:MI:SS TZ') FROM test;

which also returns an error stating that 'TZ'/'tz' are unsupported in 
'to_date' (!). It works without the 'TZ', but I really need the timezone...

I have to admit I'm now totally in the dark and feel I really miss 
something obvious. This might be a strict PostgreSQL question... In this 
case, I apologize, as I couldn't find my answers either after reading 
several times the doc on date/time for PostgreSQL...

Thanks for any hint!

All the best,
Mathieu.


-- 

~$ whoami
Mathieu Basille, Post-Doc

~$ locate
Laboratoire d'Écologie Comportementale et de Conservation de la Faune
+ Centre d'Étude de la Forêt
Département de Biologie
Université Laval, Québec

~$ info
http://ase-research.org/basille

~$ fortune
``If you can't win by reason, go for volume.''
Calvin, by Bill Watterson.