2

This is the first time I've tried out Rails with PostgreSQL. I am confused by the lack of timestamp with timezone usage in code I usually see regarding columns storing absolute time. A prime example is devise, it stores columns a datetime column which translates to a timestamp with out timezone in PostgreSQL.

I consider it a bad practice because timestamp with out timezone could mean any time zone. If you were accessing the database from another application, you would have no idea which time zone the timestamp references without looking at Rails. It just becomes a decentralized mess.

Is there any way for me to create a timestamp with time zone in a ActiveRecord migration/schema for PostgreSQL? Will create incompatibility issues with the current Rails framework code?

I understand ActiveRecord tries to be database-agnostic, but this is just a pet peeve I cannot ignore.

Maxine Lewis
  • 21
  • 1
  • 3

2 Answers2

2

I would suggest taking a look at this answer (very good explanation): Ignoring timezones altogether in Rails and PostgreSQL

UPDATE

According to ActiveRecord documentation, the above link, and my own tests - I have concluded that the default ActiveRecord datetime and timestamp column types in schema migrations cannot be modified to force PostgreSQL to use timestamp with time zone.

PostgreSQL Documentation:

Note: The SQL standard requires that writing just timestamp be equivalent to timestamp without time zone, and PostgreSQL honors that behavior. (Releases prior to 7.3 treated it as timestamp with time zone.) timestamptz is accepted as an abbreviation for timestamp with time zone; this is a PostgreSQL extension.

http://www.postgresql.org/docs/9.1/static/datatype-datetime.html

Community
  • 1
  • 1
Brandon Jernigan
  • 461
  • 2
  • 4
  • 1
    You must assume all time is stored as UTC, which is good practice across multiple timezones. In any 3rd party application or query, just convert from UTC to the timezone you wish and you have the correct time. All of your data in PostgreSQL should be UTC. If it isn't being stored as true UTC, then that could be an issue. – Brandon Jernigan Jul 30 '12 at 01:19
0

We can use this gem to override the configuration and use Adapter specific datatypes.

Nishutosh Sharma
  • 1,926
  • 2
  • 24
  • 39