title: Saving datetime in UTC isn't accurate sometimes
body: <p>In general, best practice when dealing with dates is to <a href="https://stackoverflow.com/a/2532962/426935">store them in UTC</a> and convert back to whatever the user expects within the application layer.</p>
  
  <p>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.</p>
  
  <p>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 <code>created_at</code> and <code>updated_at</code> timestamps).</p>
  
  <h2>Why</h2>
  
  <p>This particular field is the timestamp of a <strong>future event</strong> where the user selects the time. </p>
  
  <p>For future events, it seems best practice is <a href="http://www.creativedeletion.com/2015/03/19/persisting_future_datetimes.html" rel="noreferrer">not to store UTC</a>.</p>
  
  <blockquote>
    <p>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.</p>
  </blockquote>
  
  <p>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.</p>
  
  <p>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 <code>2017-01-01 00:00</code>. 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.</p>
  
  <p>Like wise, I’d expect a separate event that I create for 1 Jan 2016 at midnight in Brisbane to also be stored as <code>2017-01-01 00:00</code>. I store the timezone i.e. <code>Australia/Brisbane</code> in a separate field.</p>
  
  <h2>What’s a best practice way to do this in Rails?</h2>
  
  <p>I’ve tried lots of options with no success:</p>
  
  <h3>1. Skip conversion</h3>
  
  <p>Problem, this only skips conversion on <em>read</em>, not writing.</p>
  
  <pre><code>self.skip_time_zone_conversion_for_attributes = [:start_time]
  </code></pre>
  
  <h3>2. Change the whole app configuration to use <code>config.default_timestamp :local</code></h3>
  
  <p>To do this, I set:</p>
  
  <p><strong>config/application.rb</strong></p>
  
  <pre><code>config.active_record.default_timezone = :local
  config.time_zone = 'UTC'
  </code></pre>
  
  <p><strong>app/model/event.rb</strong></p>
  
  <pre><code>...
  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
  ...
  </code></pre>
  
  <p>To be frank, I’m not sure what this is doing.. but not what I want.</p>
  
  <p>I thought it was working as my Brisbane event was stored as <code>2017-01-01 00:00</code> but when I created a new event for Sydney, it was stored as <code>2017-01-01 01:00</code>even though it displays as midnight correctly in the view.</p>
  
  <p>That being the case, I’m concerned that still have the same problem with the Sydney event that I’m trying to avoid.</p>
  
  <h3>3. Override the getter and setter for the model to store as integer</h3>
  
  <p>I’ve tried to also store the event start_time as an integer in the database.</p>
  
  <p>I tried doing this by monkey patching the Time class and adding a <code>before_validates</code> callback to do the conversion.</p>
  
  <p><strong>config/initializers/time.rb</strong></p>
  
  <pre><code>class Time
    def time_to_i
      self.strftime('%Y%m%d%H%M').to_i
    end
  end
  </code></pre>
  
  <p><strong>app/model/event.rb</strong></p>
  
  <pre><code>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
  </code></pre>
  
  <h2>Ideal Solution</h2>
  
  <p>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.</p>
  
  <p>Second best, I’d settle for the integer option if I have to. </p>
  
  <p>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.</p>