Thread: recasting to timestamp from varchar
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. The actual values in the column time2 look like this:
| 7/15/08 12:00 |
Is this possible?
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 willnot 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
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..