forem PostgreSQL: change timestamp columns to timestamp with timezone Ruby

Is your feature request related to a problem? Please describe.

Rails defaults to timestamp columns, but those aren't aware of time zone, making timestamp arithmetics and data access from non Rails sources possibly trickier.

timestamptz vs timestamp

PostgreSQL has 2 data types to represent time: timestamp (the one Rails uses) and timestamptz (the one suggested 99.99% of the time).

The main difference is that timestamptz (despite the name which is confusing) stores the time as an integer as a single point in time since epoch in UTC, timestamp doesn't keep any information of the timezone (not even UTC).

This works now because we don't do time arithmetics via SQL and because Rails converts dates to UTC before storing them BUT theoretically the DB has no idea in which timezones these dates are stored, so 2020-01-24 15:22:19.44627 could be UTC or any other timezone depending on the who is connected to the DB. We know because of a convention, the DB doesn't know.

Example:

> select created_at from users limit 1;
+---------------------------+
| created_at                |
|---------------------------|
| 2020-01-24 15:22:19.44627 |
+---------------------------+
SELECT 1
Time: 0.071s
> show timezone;
+-------------+
| TimeZone    |
|-------------|
| Europe/Rome |
+-------------+
SHOW
Time: 0.022s
> set timezone = 'utc';
SET
Time: 0.044s
> select created_at from users limit 1;
+---------------------------+
| created_at                |
|---------------------------|
| 2020-01-24 15:22:19.44627 |
+---------------------------+
SELECT 1
Time: 0.035s
> create table test (id serial, created_at timestamptz);
CREATE TABLE
Time: 0.059s
> insert into test (created_at) values (now());
INSERT 0 1
Time: 0.012s
> select created_at from test;
+-------------------------------+
| created_at                    |
|-------------------------------|
| 2020-01-30 15:49:33.966385+00 |
+-------------------------------+
SELECT 1
Time: 0.032s
> show timezone;
+------------+
| TimeZone   |
|------------|
| UTC        |
+------------+
SHOW
Time: 0.024s
> set timezone = 'Europe/Rome';
SET
Time: 0.001s
> select created_at from test;
+-------------------------------+
| created_at                    |
|-------------------------------|
| 2020-01-30 16:49:33.966385+01 |
+-------------------------------+
SELECT 1
Time: 0.017s
>

As you can see, with timestamp (the example from the users table) the date string never changes, because the timezone setting is simply ignored by the DB. With timestamptz I get back the date string correctly converted in the timezone set in the connection.

It's one of those things that Rails never actually got right in my opinion. Fun fact: Django defaults to timestamptz since the dawn of time: https://github.com/django/django/blob/8c0c0235b6a23a0d69cd77286d0212de90d4af44/django/db/backends/postgresql/base.py#L79

Describe the solution you'd like

Transition timestamp columns to timestamptz with UTC

The Rails core team is also interested in having Rails 6.2 migrations default to timestamptz if someone provides a PR:

https://github.com/rails/rails/issues/21126#issuecomment-732422541 https://github.com/rails/rails/issues/21126#issuecomment-732463383

Describe alternatives you've considered

Not do anything and potentially have this problem surface in the future.

Asked Oct 07 '21 02:10
avatar rhymes
rhymes

4 Answer:

Thanks for the issue! We'll take your request into consideration and follow up if we decide to tackle this issue.

To our amazing contributors: issues labeled type: bug are always up for grabs, but for feature requests, please wait until we add a ready for dev before starting to work on it.

To claim an issue to work on, please leave a comment. If you've claimed the issue and need help, please ping @forem/oss and we will follow up within 3 business days.

For full info on how to contribute, please check out our contributors guide.

1
Answered Nov 24 '20 at 14:10
avatar  of github-actions[bot]
github-actions[bot]

Hey @rhymes do you think this would make a good potential RFC?

1
Answered Feb 23 '21 at 21:28
avatar  of cmgorton
cmgorton

@cmgorton in theory yes but I realized that we should wait for Rails 6.2/7 for this to be fully integrated with Rails, though I think we should close this in the meantime. We can resurface this idea a few months from now when Rails 6.2/7 will be out.

Fore more information:

We should wait :)

Thanks!

1
Answered Feb 24 '21 at 12:16
avatar  of rhymes
rhymes

Hi, author of https://github.com/rails/rails/pull/41084 here. I don't know how long it will be (if ever) before this is merged into Rails.

I'd love to see you go ahead with this, though I can see why you may not want to right away. Partially becausea I don't know how long you'll be waiting otherwise, but also selfishly because more real world use cases would help with battle testing https://github.com/rails/rails/pull/41084 + may make a better case for it being merged.

Thanks to https://github.com/rails/rails/pull/41395, you can start using timestamptz in migrations right away. So the changes would be:

Once https://github.com/rails/rails/pull/41084 is live and you're running a Rails version with it, you can remove your monkey patch and migration changes with ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.datetime_type = :timestamptz.

edit: I just noticed forem runs on Rails 6.0.3. That's going to make this a bit harder. If you're interested I can try and help get the changes in https://github.com/rails/rails/pull/41395 working with that Rails version. But I would suggest bumping to Rails 6.1 if you can. Then you can target rails/master to develop against my PRs.

1
Answered Mar 09 '21 at 19:40
avatar  of ghiculescu
ghiculescu