[postgis-users] Convert from character to timestamptz

[Sandro Santilli]

On Fri, Sep 30, 2011 at 12:10:00PM -0400, Mathieu Basille wrote:
> 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

You should report the error, when you get one.
I can just "guess" that the second is invalid syntax as you're 
using the literal string 'date' rather than the identifier "date".
And I can "guess" that the first is a syntax error (but I'm not sure).
I'd write the first as:

 ALTER TABLE test
   ALTER COLUMN date TYPE timestamptz
   WITH date::timestamptz;

Or something like that (but would really first lookup the reference).

--strk;

  ()   Free GIS & Flash consultant/developer
  /\   http://strk.keybit.net/services.html