[postgis-users] Convert from character to timestamptz

[Mathieu Basille]

Le 30/09/2011 17:49, Sandro Santilli a écrit :
>> You're right, I'm being too vague (in my defence, error messages are
>> in French for me!).
>
> See if exporting LANG=C helps there.

Did you mean at the command line or in the SQL query window? I tried 
both with no success.
I also tried to launch pgAdmin with 'LC_ALL=C pgadmin3', but error 
messages were still in French. What's weird is that the whole software 
runs in English!

> I was wrong about the syntax, but the idea is that you tell it
> how to do the conversion. Maybe it was USING instead of WITH.
> The definitive answer is in the PostgreSQL reference manual, under
> the SQL section, in the ALTER TABLE syntax.

That's beautiful! I've seen this page previously, but couldn't figure 
out what was the correct command for my case. Now, with:

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

I have now date as a 'timestamptz'! I checked, and the timezone is taken 
into account (winter times are GMT-5, summer times are GMT-4). I think 
I'm starting to understand the mistakes I made and the rationale behind 
this command.

Sandro, thanks a lot!
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.