Navigation Menu

Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

quoted_date has an hour window of failure for timestamp with timezone #38189

Open
JasonHerr opened this issue Jan 8, 2020 · 5 comments
Open

quoted_date has an hour window of failure for timestamp with timezone #38189

JasonHerr opened this issue Jan 8, 2020 · 5 comments
Labels
activerecord With reproduction steps

Comments

@JasonHerr
Copy link

JasonHerr commented Jan 8, 2020 •
edited

Description

This is a catch-22. The code is built to work well with timestamp without time zone (and that is the convention for things like created_at/updated_at). However, for timestamp with time zone and timezones with DST there exists a window of time where this causes any timestamp within that window to disappear, shifting the offset to standard time.

Steps to reproduce

  1. Have a model with a column that has postgresql timestamp with time zone
  2. update that column with a Time during that window.
  3. Retrieving that column of that row shifts it to the prevailing timezone.

For details, see actual behavior.

Expected behavior

The timestamp with timezone should be persisted with the correct timezone.

Actual behavior

Example (while in Eastern Standard Time):

  • In rails console, using a model with a column that's backed by a postgresql timestamp with time zone, update the column to a time during that period.
    model.update(time_column: Time.new(2018,11,04,01,01,00,'-04:00'))
  • Check the models time_column:

model.time_column
=> Sun, 04 Nov 2018 01:01:00 EDT -04:00

  • Reload the model

model.reload

  • Check the model's time_column

model.time_column
=> Sun, 04 Nov 2018 01:01:00 EST -05:00

  • Check the postgresql commandline:
# select time_column from models where id = X; -- fictitious id
   time_column
------------------------
 2018-11-04 01:01:00-05
(1 row)

System configuration

Rails version:
2.5
Ruby version:
2.4

Attempted workarounds

Add timezone to all db activities:

config/initializers/time_formats.rb

Time::DATE_FORMATS[:db] = lambda { |time|
  offset_format = time.formatted_offset(false)
  time.strftime("%Y-%m-%d %H:%M:%S.%6N#{offset_format}")
}

Broken by https://github.com/rails/rails/blob/master/activerecord/lib/active_record/connection_adapters/abstract/quoting.rb#L115

This appends the microseconds to the string after the offset format.

Final solution?

I will probably have to monkey patch which I hate. I don't see a good global resolution offhand unless there's a new approach for timestamp with timezone that is separate from timestamp without time zone?

Looking for other suggestions.
@rails-bot
Copy link

rails-bot bot commented Apr 7, 2020

This issue has been automatically marked as stale because it has not been commented on for at least three months.
The resources of the Rails team are limited, and so we are asking for your help.
If you can still reproduce this error on the 6-0-stable branch or on master, please reply with all of the information you have about it in order to keep the issue open.
Thank you for all your contributions.

@rails-bot rails-bot bot added the stale label Apr 7, 2020
@rails-bot rails-bot bot closed this as completed Apr 14, 2020
@jonathanhefner
Copy link
Member

If I am understanding correctly, this is actually a problem for all databases when ActiveRecord::Base.default_timezone == :local. When DST ends, a wall-clock hour is repeated, e.g. "1:30" happens once under EDT, then again under EST. If the time is expressed in local time and the time zone is stripped away (as to_s(:db) does), there is no way to distinguish "1:30" from "1:30".

Some Ruby to illustrate the point 
ENV["TZ"] = "America/New_York"

zone = ActiveSupport::TimeZone[ENV["TZ"]]

twzs = [
  zone.parse("2018-11-04 00:59:00 -04:00"), # == Sun, 04 Nov 2018 00:59:00 EDT -04:00
  zone.parse("2018-11-04 01:30:00 -04:00"), # == Sun, 04 Nov 2018 01:30:00 EDT -04:00
  zone.parse("2018-11-04 01:30:00 -05:00"), # == Sun, 04 Nov 2018 01:30:00 EST -05:00
  zone.parse("2018-11-04 02:01:00 -05:00"), # == Sun, 04 Nov 2018 02:01:00 EST -05:00
]

quoted_utc = twzs.map{|twz| twz.getutc.to_s(:db) }
# == [
#   "2018-11-04 04:59:00",
#   "2018-11-04 05:30:00",
#   "2018-11-04 06:30:00",
#   "2018-11-04 07:01:00",
# ]

quoted_local = twzs.map{|twz| twz.getlocal.to_s(:db) }
# == [
#   "2018-11-04 00:59:00",
#   "2018-11-04 01:30:00",
#   "2018-11-04 01:30:00",
#   "2018-11-04 02:01:00",
# ]

If we appended time zone offsets to quoted times, zone-aware data types (like PostgreSQL's TIMESTAMPTZ) could dodge the problem. But all other time data types would remain affected.

Some SQL to illustrate the point 
CREATE TABLE facepalm (s VARCHAR, t TIMESTAMP, ttz_utc TIMESTAMPTZ, ttz_east TIMESTAMPTZ);

INSERT INTO facepalm (s) VALUES
  ('2018-11-04 04:59:00'),
  ('2018-11-04 05:30:00'),
  ('2018-11-04 06:30:00'),
  ('2018-11-04 07:01:00'),
  ('2018-11-04 04:59:00z'),
  ('2018-11-04 05:30:00z'),
  ('2018-11-04 06:30:00z'),
  ('2018-11-04 07:01:00z'),
  ('2018-11-04 00:59:00'),
  ('2018-11-04 01:30:00'),
  ('2018-11-04 01:30:00'),
  ('2018-11-04 02:01:00'),
  ('2018-11-04 00:59:00 -04:00'),
  ('2018-11-04 01:30:00 -04:00'),
  ('2018-11-04 01:30:00 -05:00'),
  ('2018-11-04 02:01:00 -05:00');

UPDATE facepalm SET t = s::TIMESTAMP;

SET TIME ZONE "Etc/UTC";
UPDATE facepalm SET ttz_utc = s::TIMESTAMPTZ;

SET TIME ZONE "America/New_York";
UPDATE facepalm SET ttz_east = s::TIMESTAMPTZ;

SELECT * FROM facepalm;
|                          s |                    t |              ttz_utc |             ttz_east |
|----------------------------|----------------------|----------------------|----------------------|
|        2018-11-04 04:59:00 | 2018-11-04T04:59:00Z | 2018-11-04T04:59:00Z | 2018-11-04T09:59:00Z |
|        2018-11-04 05:30:00 | 2018-11-04T05:30:00Z | 2018-11-04T05:30:00Z | 2018-11-04T10:30:00Z |
|        2018-11-04 06:30:00 | 2018-11-04T06:30:00Z | 2018-11-04T06:30:00Z | 2018-11-04T11:30:00Z |
|        2018-11-04 07:01:00 | 2018-11-04T07:01:00Z | 2018-11-04T07:01:00Z | 2018-11-04T12:01:00Z |
|       2018-11-04 04:59:00z | 2018-11-04T04:59:00Z | 2018-11-04T04:59:00Z | 2018-11-04T04:59:00Z |
|       2018-11-04 05:30:00z | 2018-11-04T05:30:00Z | 2018-11-04T05:30:00Z | 2018-11-04T05:30:00Z |
|       2018-11-04 06:30:00z | 2018-11-04T06:30:00Z | 2018-11-04T06:30:00Z | 2018-11-04T06:30:00Z |
|       2018-11-04 07:01:00z | 2018-11-04T07:01:00Z | 2018-11-04T07:01:00Z | 2018-11-04T07:01:00Z |
|        2018-11-04 00:59:00 | 2018-11-04T00:59:00Z | 2018-11-04T00:59:00Z | 2018-11-04T04:59:00Z |
|        2018-11-04 01:30:00 | 2018-11-04T01:30:00Z | 2018-11-04T01:30:00Z | 2018-11-04T06:30:00Z |
|        2018-11-04 01:30:00 | 2018-11-04T01:30:00Z | 2018-11-04T01:30:00Z | 2018-11-04T06:30:00Z |
|        2018-11-04 02:01:00 | 2018-11-04T02:01:00Z | 2018-11-04T02:01:00Z | 2018-11-04T07:01:00Z |
| 2018-11-04 00:59:00 -04:00 | 2018-11-04T00:59:00Z | 2018-11-04T04:59:00Z | 2018-11-04T04:59:00Z |
| 2018-11-04 01:30:00 -04:00 | 2018-11-04T01:30:00Z | 2018-11-04T05:30:00Z | 2018-11-04T05:30:00Z |
| 2018-11-04 01:30:00 -05:00 | 2018-11-04T01:30:00Z | 2018-11-04T06:30:00Z | 2018-11-04T06:30:00Z |
| 2018-11-04 02:01:00 -05:00 | 2018-11-04T02:01:00Z | 2018-11-04T07:01:00Z | 2018-11-04T07:01:00Z |

On the plus side, PostgreSQL will happily ignore a time zone offset if the column type is plain TIMESTAMP. Meaning we could append time zone offsets to quoted times for PostgreSQL without caring about the column type.

@JasonHerr
Copy link
Author

When I try this, the t column doesn't show a timezone/offset for either session time zone:

# SET TIME ZONE "Etc/UTC";

# SELECT * FROM facepalm;
             s              |          t          |        ttz_utc         |        ttz_east        
----------------------------+---------------------+------------------------+------------------------
 2018-11-04 04:59:00        | 2018-11-04 04:59:00 | 2018-11-04 04:59:00+00 | 2018-11-04 09:59:00+00
 2018-11-04 05:30:00        | 2018-11-04 05:30:00 | 2018-11-04 05:30:00+00 | 2018-11-04 10:30:00+00
 2018-11-04 06:30:00        | 2018-11-04 06:30:00 | 2018-11-04 06:30:00+00 | 2018-11-04 11:30:00+00
 2018-11-04 07:01:00        | 2018-11-04 07:01:00 | 2018-11-04 07:01:00+00 | 2018-11-04 12:01:00+00
 2018-11-04 04:59:00z       | 2018-11-04 04:59:00 | 2018-11-04 04:59:00+00 | 2018-11-04 04:59:00+00
 2018-11-04 05:30:00z       | 2018-11-04 05:30:00 | 2018-11-04 05:30:00+00 | 2018-11-04 05:30:00+00
 2018-11-04 06:30:00z       | 2018-11-04 06:30:00 | 2018-11-04 06:30:00+00 | 2018-11-04 06:30:00+00
 2018-11-04 07:01:00z       | 2018-11-04 07:01:00 | 2018-11-04 07:01:00+00 | 2018-11-04 07:01:00+00
 2018-11-04 00:59:00        | 2018-11-04 00:59:00 | 2018-11-04 00:59:00+00 | 2018-11-04 04:59:00+00
 2018-11-04 01:30:00        | 2018-11-04 01:30:00 | 2018-11-04 01:30:00+00 | 2018-11-04 06:30:00+00
 2018-11-04 01:30:00        | 2018-11-04 01:30:00 | 2018-11-04 01:30:00+00 | 2018-11-04 06:30:00+00
 2018-11-04 02:01:00        | 2018-11-04 02:01:00 | 2018-11-04 02:01:00+00 | 2018-11-04 07:01:00+00
 2018-11-04 00:59:00 -04:00 | 2018-11-04 00:59:00 | 2018-11-04 04:59:00+00 | 2018-11-04 04:59:00+00
 2018-11-04 01:30:00 -04:00 | 2018-11-04 01:30:00 | 2018-11-04 05:30:00+00 | 2018-11-04 05:30:00+00
 2018-11-04 01:30:00 -05:00 | 2018-11-04 01:30:00 | 2018-11-04 06:30:00+00 | 2018-11-04 06:30:00+00
 2018-11-04 02:01:00 -05:00 | 2018-11-04 02:01:00 | 2018-11-04 07:01:00+00 | 2018-11-04 07:01:00+00
(16 rows)


# SET TIME ZONE "America/New_York";

# SELECT * FROM facepalm;
             s              |          t          |        ttz_utc         |        ttz_east        
----------------------------+---------------------+------------------------+------------------------
 2018-11-04 04:59:00        | 2018-11-04 04:59:00 | 2018-11-04 00:59:00-04 | 2018-11-04 04:59:00-05
 2018-11-04 05:30:00        | 2018-11-04 05:30:00 | 2018-11-04 01:30:00-04 | 2018-11-04 05:30:00-05
 2018-11-04 06:30:00        | 2018-11-04 06:30:00 | 2018-11-04 01:30:00-05 | 2018-11-04 06:30:00-05
 2018-11-04 07:01:00        | 2018-11-04 07:01:00 | 2018-11-04 02:01:00-05 | 2018-11-04 07:01:00-05
 2018-11-04 04:59:00z       | 2018-11-04 04:59:00 | 2018-11-04 00:59:00-04 | 2018-11-04 00:59:00-04
 2018-11-04 05:30:00z       | 2018-11-04 05:30:00 | 2018-11-04 01:30:00-04 | 2018-11-04 01:30:00-04
 2018-11-04 06:30:00z       | 2018-11-04 06:30:00 | 2018-11-04 01:30:00-05 | 2018-11-04 01:30:00-05
 2018-11-04 07:01:00z       | 2018-11-04 07:01:00 | 2018-11-04 02:01:00-05 | 2018-11-04 02:01:00-05
 2018-11-04 00:59:00        | 2018-11-04 00:59:00 | 2018-11-03 20:59:00-04 | 2018-11-04 00:59:00-04
 2018-11-04 01:30:00        | 2018-11-04 01:30:00 | 2018-11-03 21:30:00-04 | 2018-11-04 01:30:00-05
 2018-11-04 01:30:00        | 2018-11-04 01:30:00 | 2018-11-03 21:30:00-04 | 2018-11-04 01:30:00-05
 2018-11-04 02:01:00        | 2018-11-04 02:01:00 | 2018-11-03 22:01:00-04 | 2018-11-04 02:01:00-05
 2018-11-04 00:59:00 -04:00 | 2018-11-04 00:59:00 | 2018-11-04 00:59:00-04 | 2018-11-04 00:59:00-04
 2018-11-04 01:30:00 -04:00 | 2018-11-04 01:30:00 | 2018-11-04 01:30:00-04 | 2018-11-04 01:30:00-04
 2018-11-04 01:30:00 -05:00 | 2018-11-04 01:30:00 | 2018-11-04 01:30:00-05 | 2018-11-04 01:30:00-05
 2018-11-04 02:01:00 -05:00 | 2018-11-04 02:01:00 | 2018-11-04 02:01:00-05 | 2018-11-04 02:01:00-05
(16 rows)

I'm at 9.6 though, maybe that is the difference?

@jonathanhefner
Copy link
Member

jonathanhefner commented Apr 15, 2020 •
edited

I think that was a rendering artifact of using SQL Fiddle and its default PostgreSQL / JDBC settings. I don't think it affects the underlying issue. For what it's worth, SQL Fiddle uses 9.6 also.

@jonathanhefner
Copy link
Member

@rafaelfranca Would you mind re-opening this issue and adding the "with reproduction steps" tag?

@rafaelfranca rafaelfranca reopened this Apr 16, 2020
@rails-bot rails-bot bot removed the stale label Apr 16, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
activerecord With reproduction steps
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@rafaelfranca @jonathanhefner @JasonHerr