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.
