CODEWITHSUNDEEP
ruby-on-railsrubypostgresqldatetime
Joao Torres
Joao Torres

Reputation: 39

Rails + PostgreSQL: How to query timezone info along with time

I'm using Rails4 and PostgreSQL v9.6.2, my problem is that I want to process datetimes in Rails in the timezone that it was entered, it gets stored properly in PostreSQL but when I retrieve it with Rails the timezone info gets lost.

I'm using a timestamp with time zone in PostgreSQL and datetime in Rails. For example, while on PostgreSQL I see 2012-10-26 19:26:00+02, on Rails I'm getting Fri, 26 Oct 2012 17:26:00 UTC +00:00, which is technically the same date, but on UTC, or without the original timezone. I'd want to get, from Rails, the timezone info stored on PostgreSQL.

Upvotes: 2

Views: 821

Answers (1)

Ivan Mogila
Ivan Mogila

Reputation: 467

From Postgres manual:

For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone.

When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct

You can override current time zone on a session level using:

SET TIME ZONE timezone;

where timezone your desired time zone.

Upvotes: 1

Related Questions