Saving datetime in UTC isn't accurate sometimes

Question

In general, best practice when dealing with dates is to store them in UTC and convert back to whatever the user expects within the application layer.

That doesn't necessarily work with future dates, particularly where the date/time is specific to the user in their timezone. A schedule based on local time requires storing a local time.

In my instance,there’s one attribute that’s a timestamp containing the start_time of a future event, compared to everything else that's now or in the past (including the created_at and updated_at timestamps).

Why

This particular field is the timestamp of a future event where the user selects the time.

For future events, it seems best practice is not to store UTC.

Instead of saving the time in UTC along with the time zone, developers can save what the user expects us to save: the wall time.

When the user chooses 10am, it needs to stay 10am even when the user’s offset from UTC changes between creation and the event date due to daylight savings.

So, in June 2016, if a user creates an event for 1st Jan 2017 at midnight in Sydney, that timestamp will be stored in the database as 2017-01-01 00:00. The offset at time of creation would be +10:00, but at the time of the event, it’d be +11:00.. unless government decides to change that in the meantime.

Like wise, I’d expect a separate event that I create for 1 Jan 2016 at midnight in Brisbane to also be stored as 2017-01-01 00:00. I store the timezone i.e. Australia/Brisbane in a separate field.

What’s a best practice way to do this in Rails?

I’ve tried lots of options with no success:

1. Skip conversion

Problem, this only skips conversion on read, not writing.

self.skip_time_zone_conversion_for_attributes = [:start_time]

2. Change the whole app configuration to use config.default_timestamp :local

To do this, I set:

config/application.rb

config.active_record.default_timezone = :local
config.time_zone = 'UTC'

app/model/event.rb

...
self.skip_time_zone_conversion_for_attributes = [:start_time]
before_save :set_timezone_to_location
after_save :set_timezone_to_default

def set_timezone_to_location
  Time.zone = location.timezone
end

def set_timezone_to_default
  Time.zone = 'UTC'
end
...

To be frank, I’m not sure what this is doing.. but not what I want.

I thought it was working as my Brisbane event was stored as 2017-01-01 00:00 but when I created a new event for Sydney, it was stored as 2017-01-01 01:00even though it displays as midnight correctly in the view.

That being the case, I’m concerned that still have the same problem with the Sydney event that I’m trying to avoid.

3. Override the getter and setter for the model to store as integer

I’ve tried to also store the event start_time as an integer in the database.

I tried doing this by monkey patching the Time class and adding a before_validates callback to do the conversion.

config/initializers/time.rb

class Time
  def time_to_i
    self.strftime('%Y%m%d%H%M').to_i
  end
end

app/model/event.rb

before_validation :change_start_time_to_integer
def change_start_time_to_integer
  start_time = start_time.to_time if start_time.is_a? String
  start_time = start_time.time_to_i
end

# read value from DB
# TODO: this freaks out with an error currently
def start_time
  #take integer YYYYMMDDHHMM and convert it to timestamp
  st = self[:start_time]
  Time.new(
    st / 100000000,
    st / 1000000 % 100,
    st / 10000 % 100,
    st / 100 % 100,
    st % 100,
    0,
    offset(true)
  )
end

Ideal Solution

I’d like to be able to store a timestamp in its natural datatype in the database so queries don’t get messy in my controllers, but I can’t figure out how to store “wall time” that doesn’t convert.

Second best, I’d settle for the integer option if I have to.

How do others deal with this? What am I missing? Particularly with the "integer conversion" option above, I'm making things far more complicated than they need to be.

Answer 1

I propose that you still use the first option but with a little hack: in essence, you can switch off the time zone conversion for the desired attribute and use a custom setter to overcome the conversion during attribute writes.

The trick saves the time as a fake UTC time. Although technically it has an UTC zone (as all the times are saved in db in UTC) but by definition it shall be interpreted as local time, regardless of the current time zone.

class Model < ActiveRecord::Base
  self.skip_time_zone_conversion_for_attributes = [:start_time]

  def start_time=(time)
    write_attribute(:start_time, time ? time + time.utc_offset : nil)
  end
end

Let's test this in rails console:

$ rails c
>> future_time = Time.local(2020,03,30,11,55,00)
=> 2020-03-30 11:55:00 +0200

>> Model.create(start_time: future_time)
D, [2016-03-15T00:01:09.112887 #28379] DEBUG -- :    (0.1ms)  BEGIN
D, [2016-03-15T00:01:09.114785 #28379] DEBUG -- :   SQL (1.4ms)  INSERT INTO `models` (`start_time`) VALUES ('2020-03-30 11:55:00')
D, [2016-03-15T00:01:09.117749 #28379] DEBUG -- :    (2.7ms)  COMMIT
=> #<Model id: 6, start_time: "2020-03-30 13:55:00">

Note that Rails saved the time as a 11:55, in a "fake" UTC zone.

Also note that the time in the object returned from create is wrong because the zone is converted from the "UTC" in this case. You would have to count with that and reload the object every time after setting the start_time attribute, so that the zone conversion skipping can take place:

>> m = Model.create(start_time: future_time).reload
D, [2016-03-15T00:08:54.129926 #28589] DEBUG -- :    (0.2ms)  BEGIN
D, [2016-03-15T00:08:54.131189 #28589] DEBUG -- :   SQL (0.7ms)  INSERT INTO `models` (`start_time`) VALUES ('2020-03-30 11:55:00')
D, [2016-03-15T00:08:54.134002 #28589] DEBUG -- :    (2.5ms)  COMMIT
D, [2016-03-15T00:08:54.141720 #28589] DEBUG -- :   Model Load (0.3ms)  SELECT  `models`.* FROM `models` WHERE `models`.`id` = 10 LIMIT 1
=> #<Model id: 10, start_time: "2020-03-30 11:55:00">

>> m.start_time
=> 2020-03-30 11:55:00 UTC

After loading the object, the start_time attribute is correct and can be manually interpreted as local time regardless of the actual time zone.

I really don't get it why Rails behaves the way it does regarding the skip_time_zone_conversion_for_attributes configuration option...

Update: adding a reader

We can also add a reader so that we automatically interpret the saved "fake" UTC time in local time, without shifting the time due to timezone change:

class Model < ActiveRecord::Base
  # interprets time stored in UTC as local time without shifting time
  # due to time zone change
  def start_time
    t = read_attribute(:start_time)
    t ? Time.local(t.year, t.month, t.day, t.hour, t.min, t.sec) : nil
  end
end

Test in rails console:

>> m = Model.create(start_time: future_time).reload
D, [2016-03-15T08:10:54.889871 #28589] DEBUG -- :    (0.1ms)  BEGIN
D, [2016-03-15T08:10:54.890848 #28589] DEBUG -- :   SQL (0.4ms)  INSERT INTO `models` (`start_time`) VALUES ('2020-03-30 11:55:00')
D, [2016-03-15T08:10:54.894413 #28589] DEBUG -- :    (3.1ms)  COMMIT
D, [2016-03-15T08:10:54.895531 #28589] DEBUG -- :   Model Load (0.3ms)  SELECT  `models`.* FROM `models` WHERE `models`.`id` = 12 LIMIT 1
=> #<Model id: 12, start_time: "2020-03-30 11:55:00">

>> m.start_time
=> 2020-03-30 11:55:00 +0200

I.e. the start_time is correctly interpreted in local time, even though it was stored as the same hour and minute, but in UTC.

Answer 2

This may sound a bit out there, but I have dealt with similar issues with a recent application I was tasked with - but on the opposite side - when I run an ETL to load data for the application, dates from the source are stored in EST. Rails believes that it is UTC when serving the data, so for that, I converted the dates back to UTC using P/SQL. I did not want these dates to be different than the other date fields within the app.

Option A In this case, could you capture the user timezone at creation, and send that back as a hidden field in the form? I am still learning RoR, so am not sure on the "proper" way to do this, but right now I would do something like this:

Example (I tested this, and it will submit the offset (minutes) in a hidden field):

<div class="field">
  <%= f.hidden_field :offset %>
</div>

<script>
  utcDiff = new Date().getTimezoneOffset();
  dst_field = document.getElementById('timepage_offset');
  dst_field.value = utcDiff;
</script>

If you then send utcDiff along with the user selected date, you could calculate the UTC date before storing. I suppose you could add that to the model as well if that data is necessary to know at a later date.

I think that no matter how this is done, there will always be slight area for confusion, unless the user is capable of providing the proper information, which leads me to...

Option B: You could, instead of a hidden field, provide a select list (and to be friendly, default it to the users' local offset), to allow them to provide the zone for which their date is specified in.

Update - TimeZone select I've done some research, and it looks like there is already a form helper for a time zone select box.