recasting to timestamp from varchar

On Sat, Jan 5, 2013 at 4:28 AM, Kirk Wythers <wythe001@umn.edu> wrote:

I am trying to re-cast a column as a timestamp>

ALTER TABLE sixty_min ALTER COLUMN time2 TYPE timestamp;
ERROR: column "time2" cannot be cast to type timestamp without time zone

The column time2 is currently a varchar. I actually do not want to mess with time zones, but it seems that postgres will not let me do this.

What exactly do you mean about your time zones? Are all your times in
UTC? If they're all in "local time", then you'll want to record time
zones, if only to prevent confusion around DST. It's really much MUCH
safer to incorporate tz.

ChrisA

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 1/4/2013 9:28 AM, Kirk Wythers wrote:

ALTER TABLE sixty_min ALTER COLUMN time2 TYPE timestamp;
ERROR: column "time2" cannot be cast to type timestamp without time zone

you need to give it some hints as to how to do that casting.

ALTER TABLE sixty_min ALTER COLUMN time2 TYPE timestamp USING
to_timestamp(time2, 'YYYY-MM-DD HH24-MI-SS');

I would use timezone with time zone as the type here. Effectively, this
type converts all input times to UTC from either the specified or
client's default TZ, and converts all time output to either the
specified or client's TZ. it behaves properly, while 'timestamp'
without timezone doesn't..

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general