CODEWITHSUNDEEP
sqlpostgresqldatetimestamp
artaxerxe
artaxerxe

Reputation: 6411

Timestamp related in Postgres

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 ) ?

Upvotes: 1

Views: 197

Answers (3)

Ayush Pandey
Ayush Pandey

Reputation: 168

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')

Upvotes: 2

Jakub Kania
Jakub Kania

Reputation: 16487

You're looking for to_timestamp function:

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

http://sqlfiddle.com/#!12/d41d8/785

Upvotes: 2

Chris Farmiloe
Chris Farmiloe

Reputation: 14185

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);

See docs

Upvotes: 4

Related Questions