How to set in Rails a default timezone for query or set offset for datetime value when migrating record from MySql to Postgres

Question

I created a ruby class in my models folder to connect to a legacy MySQL database that by default uses Pacific/PT for datetime columns.

However, rails is treating datetime values from legacy database records as UTC as opposed PT/Pacific. Inserting this value into Postgres would be incorrect since Postgres timestamp (for rails) is timestamp without time zone (therefore, value needs to be UTC).

How can I either 1) set my default_timezone connection to be Pacific or 2) create some type of offset that will correctly convert the value to UTC? For the later, how would an offset incorporate daylight savings time nuances?

Here's how I've confirmed the issue:

[6] pry(LegacyClient)> @legacy.ClientRecordAddDate.to_yaml
=> "--- 2010-04-11 14:23:01.000000000 Z\n...\n"
[7] pry(LegacyClient)> @legacy.ClientRecordAddDate
=> Sun, 11 Apr 2010 07:23:01 PDT -07:00
[8] pry(LegacyClient)> self.default_timezone
=> :utc

Just to be clear, my goal is to get the `datetime` value to be correctly saved in corresponding UTC for consistency with the default behavior of the app. — user1322092, Aug 29 '14 at 16:38

score 0 · Answer 1 · edited May 23 '17 at 12:21

0

First, find a time zone name that actually exists in your Postgres DB cluster:

SELECT *
FROM   pg_timezone_names
WHERE  name ~~* '%Pacif%'
   OR  abbrev = 'PT';

Then set this time zone in your session:

SET timezone = 'US/Pacific';

Which means values from timestamp [without time zone] columns are interpreted as being in this time zone, and timestamp [with time zone] is offset from / to UTC accordingly on input / output.

There are many ways to set a config parameter in Postgres. Compare:

How does the search_path influence identifier resolution and the "current schema"

More information:

Ignoring timezones altogether in Rails and PostgreSQL

edited May 23 '17 at 12:21

Community

1
1

answered Aug 29 '14 at 16:21

Erwin Brandstetter

605,456
145
1,078
1,228

thanks for taking the time to review and providing a thoughtful response! Is there a 'rails/active model' solution - this would be my preferred approach so I can test/write rspec against the migration? Postgres would be a Heroku DB service too. In addition, a scenario is migration data on while app in, so I would want to adversely impact connecting sessions. – user1322092 Aug 29 '14 at 16:31
@user1322092: I am an expert with Postgres. With Rails, not so much. Eventually, all you do in Rails to talk to the database has to be translated to native Postgres commands. – Erwin Brandstetter Aug 29 '14 at 16:36

score 0 · Accepted Answer · answered Sep 05 '14 at 04:00

I solved this after a few days toiling with this issue. It's somewhat of a subtle hack. default_timezone in rails can be either :local or :utc. So, since rails misinterpreted my Pacific-based Mysql records as 'utc' or -7 hours behind UTC, I had to change it back to UTC (really back to 'Pacific' since it was originally misinterpreted) and convert the value to a string. ActiveRecord would then treat convert the string to Pacific (since in application.rb I have config.time_zone = 'Pacific Time (US & Canada)') and then save it to Postgres in correct UTC format!

[2] pry(LegacyClient)> @legacy.ClientRecordAddDate.utc.to_s
=> "2010-04-11 14:23:01"

In my hash that would capture the legacy mysql values, I would have the below for created_at and updated_at:

{
...
created_at: @legacy.ClientRecordAddDate.utc.to_s,
updated_at: @legacy.ClientRecordUpdateDate.utc.to_s
}

How to set in Rails a default timezone for query or set offset for datetime value when migrating record from MySql to Postgres

2 Answers2