Timestamp related in Postgres

Question

In a Postgres database I have a table that in one of its columns holds a date as a character varying data type. Its format is dd MMM yyyy HH:mm.

Eg.: 21 Sep 2012 12:23.

Is there a way to convert it in a timestamp fromat so that I can compare it with the current time (i.e. now()- function ) ?

postgresql.org/docs/8.2/static/functions-formatting.html Please have a look at it. — SenthilPrabhu
– SenthilPrabhu, Commented Apr 12, 2013 at 12:02

Chris Farmiloe · Accepted Answer · 2013-04-12 12:04:21Z

4

There are SET DATA TYPE and USING parts to the ALTER TABLE statement. If you wanted to fix the column permanently.

ALTER TABLE foo
    ALTER COLUMN my_time_stamp SET DATA TYPE timestamp
    USING to_timestamp(my_time_stamp);

answered Apr 12, 2013 at 12:04

Chris Farmiloe

14.3k5 gold badges51 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

I need it to stay a character varying data type. Anyway, as A.H. mentiond very good pointed.

Jakub Kania · Accepted Answer · 2013-04-12 12:10:43Z

2

You're looking for to_timestamp function:

SELECT to_timestamp('21 Sep 2012 12:23', 'DD Mon YYYY HH24:MI')

answered Apr 12, 2013 at 12:02

Jakub Kania

16.6k2 gold badges44 silver badges51 bronze badges

I'm sure that you do not want to use HH but HH24.

@A.H. Thank you, I didn't even know HH12 is the default.

Lorenz Lo Sauer · Accepted Answer · 2013-04-12 12:16:28Z

2

I had somewhere read about to_timestamp try this out to_timestamp(text, text)

functionname:---to_timestamp(text, text)    
returntype---timestamp with time zone 
its function---convert string to time stamp

example: to_timestamp('05 Dec 2000', 'DD Mon YYYY')

Lorenz Lo Sauer

24.9k16 gold badges89 silver badges87 bronze badges

answered Apr 12, 2013 at 12:08

Ayush Pandey

1687 bronze badges