CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecordtimezonetimezone-offset
Carpela
Carpela

Reputation: 2195

Parse Postgres date stored as string including offset timezone

I have some dates stored as strings in a postgresdb

"Fri, 24 Jun 2016 04:13:26 -0700"

I want to treat those dates as dates.

I can use 

to_timestamp(date,'Dy, DD Mon YYYY HH24:MI:SS')

But I can't work out how to deal with the timezone. there appears to be OF as the parameter for the offset.

If I use 

to_timestamp(date, 'Dy, DD Mon YYYY HH24:MI:SS OF')

The query hangs. I can't work out what I'm doing wrong there.

Note: I'm using activerecord and rails. so the query is actually 

Model.all.order("to_timestamp(date,'Dy, DD Mon YYYY HH24:MI:SS OF') DESC")

Upvotes: 0

Views: 1880

Answers (2)

Abelisto
Abelisto

Reputation: 15624

PostgreSQL supports several formats for date/time/timestamp values input. See the reference.

Your format is also supported so the simple type cast can be used. There are three ways and also two type notations in the PostgreSQL:

timestamp with time zone or shorter timestamptz

and type cast like:

cast('Fri, 24 Jun 2016 04:13:26 -0700' as timestamp with time zone)
timestamp with time zone 'Fri, 24 Jun 2016 04:13:26 -0700'
'Fri, 24 Jun 2016 04:13:26 -0700'::timestamptz

which is equal. You can use any combinations depending on your taste.

So answering to your question:

Model.all.order("date::timestamptz DESC")

should make the trick.

Upvotes: 2

Hardik Upadhyay
Hardik Upadhyay

Reputation: 2659

You can try this method

"Fri, 24 Jun 2016 04:13:26 -0700".to_datetime

Hope, this will help you.

Upvotes: 0

Related Questions