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.

Improve this question
0

2 Answers 2

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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.
0

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.