rails: timestamp schema migration on postgres doesn't include timezone

We are facing this problem in the company I work.

We are using Metabase (http://www.metabase.com) for reports and charts, directly connected to the rails database.

Since timestamp without zone does not store any information about time zone at all, other-than-rails clients are not able to convert the timestamps to another time zone. Actually, they don’t even know what time zone is being used. They are “forced” to assume that the timestamps are already using the desired time zone, which causes problems if you are using any other-than-UTC timezone.

From Metabase docs:

Make sure all of your database columns are properly setup to include timezone awareness.

In this example, setting the session time zone in a PostgreSQL connection doesn’t affect timestamp without zone columns:

• created_at is timestamp without zone
• updated_at is timestamp with zone
• both timestamps were stored by rails as UTC

mydb => show timezone;
 TimeZone
----------
 UTC
(1 row)

mydb=> select created_at, updated_at from orders limit 1;
         created_at         |          updated_at
----------------------------+-------------------------------
 2016-06-03 15:06:35.021706 | 2016-06-03 15:06:35.672491+00
(1 row)

mydb=> set timezone='US/Eastern';
SET
mydb=> select created_at, updated_at from orders limit 1;
         created_at         |          updated_at
----------------------------+-------------------------------
 2016-06-03 15:06:35.021706 | 2016-06-03 11:06:35.672491-04
(1 row)

Note the beautiful +00 and -04 on updated_at column.

As a solution, we are planning on modifying all datetime columns to timestamp with zone and also change active record default datetime type for PostgreSQL:

1. Generate all necessary “alter table” statments:

select 'ALTER TABLE '
  || table_name
  || ' ALTER '
  || column_name
  || ' TYPE timestamptz USING '
  || column_name
  || ' AT TIME ZONE ''UTC'';'
  from information_schema.columns
  where table_schema = 'public'
  and data_type = 'timestamp without time zone';

Result example:

...
ALTER TABLE orders ALTER updated_at TYPE timestamptz USING updated_at AT TIME ZONE 'UTC';
...

1. Change active record default for future migrations:

# config/initializers/active_record.rb
ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::
  NATIVE_DATABASE_TYPES[:datetime] = {name: "timestamp with time zone"}

Extracted from http://nandovieira.com/working-with-dates-on-ruby-on-rails

We are running those changes on a test server and we haven’t noticed any problem so far.

EDIT: We’ve executed this two weeks ago, everything working properly, no issues so far.

As a followup to this, I think the default behavior of Rails is somewhat incompatible with most other tools. An “out of the box” datetime (timestamp without zone written as UTC) field in Rails will not be parsed correctly in a at least a few other postgres clients I’ve seen. The ruby pg gem doesn’t parse it correctly, the sequel gem doesn’t, and nodejs pg module does not. And when I say it “doesn’t parse it correctly”, I mean Rails writes a UTC timestamp, and the other postgres clients are setup to always parse it as a local timestamp. I’m not sure which is correct, but it doesn’t seem ideal to create data that doesn’t play nice with other clients. I’m sure there’s a good reason for it, I just ran into this years after having it in place when we wanted to read Rails-created data from a nodejs app.

This behavior is the same as mysql database using datetime which also ignores time zone. Default options look like this:

rails	mysql (tz aware?)	postgresql (tz aware?)
datetime	datetime (no)	timestamp without time zone (no)
timestamp	timestamp (yes)	timestamp without time zone (no)
timestamptz	error	timestamp with time zone (yes)

If I understand it correctly, mysql’s timestamp (and thus rails’) is in violation of ansi sql. And timestamps definition should use timestamptz (or whatever general equivalent is) by default instead of datetime if aiming for better interoperability with non-rails db access.

I’m running into this as well. I’d vote for Rails defaulting timestamps to ‘timestamp with time zone’ – especially for the created_at and updated_at fields – I can’t see any downsides.