title: Best practices with saving datetime & timezone info in database when data is dependant on datetime
body: <p>There were quite a few questions about saving datetime &amp; timezones info in DB but more on the overall level. Here I'd like to address a specific case.</p>
  
  <p><strong>System specs</strong></p>
  
  <ul>
  <li>We have an Orders system database</li>
  <li>It is a multi-tenant system where tenants can use arbitrary timezone (it is arbitrary but single timezone per tenant, saved in Tenants table once and never changes)</li>
  </ul>
  
  <p><strong>Business rule needed to be covered in DB</strong></p>
  
  <ul>
  <li>When tenant places an Order into the system, <strong>order number gets computed based on their local datetime</strong> (its not literally a number but some kind of an identifier like <code>ORDR-13432-Year-Month-Day</code>). Precise calculation is not important for the moment, it's just important that it's dependant on tenants local datetime</li>
  <li>We also do want to be able to select all Orders, on the system level, placed between some UTC datetimes regardless of the tenant (for general system statistics/reporting)</li>
  </ul>
  
  <p><strong>Our initial idea</strong></p>
  
  <ul>
  <li>Our initial idea was to save UTC datetime across whole DB and, of course, keep tenants timezone offset relative to UTC and have application that consumes DB always convert datetimes to UTC so that DB itself always operate with UTC.</li>
  </ul>
  
  <p><strong>Approach 1</strong></p>
  
  <ul>
  <li><p>Saving local tenants datetime would be nice per tenant but then we have problem with queries like: </p>
  
  <pre><code>SELECT * FROM ORDERS WHERE OrderDateTime BETWEEN UTCDateTime1 AND UTCDateTime2
  </code></pre></li>
  </ul>
  
  <p>It's problematic because <code>OrderDateTime</code> in this query means different moment in time, based on tenant. Of course, this query might include join to <code>Tenants</code> table to get local datetime offset which would then calculate <code>OrderDateTime</code> on the fly to make adjustments. It's possible, but not sure if it's a good way to do it?</p>
  
  <p><strong>Approach 2</strong></p>
  
  <ul>
  <li>On the other hand, when saving UTC datetime, then when we do calculation of OrderNumber since the day/month/year in UTC might differ from the one in local datetime</li>
  </ul>
  
  <p>Let's take extreme example; let's say tenant is 6 hours ahead of UTC and his local datetime is <code>2017-01-01 02:00</code>. 
  UTC would be <code>2016-12-31 20:00</code>. Order placed at that moment should get OrderNumber <code>'ORDR-13432-2017-1-1'</code> but if saving UTC it would get <code>ORDR-13432-2016-12-31</code>.</p>
  
  <p>In this case, at the moment of creating Order in DB, we should get UTC datetime, tenants offset and compile OrderNumber based on recalculated tenants localtime but still save DateTime column in UTC.</p>
  
  <p><strong>Questions</strong></p>
  
  <ol>
  <li>What is the preferred way of handling this kind of situation?</li>
  <li>Is there a nice solution with saving UTC datetimes because that one would be pretty nice for us because of the system-level reporting?</li>
  <li>If going with saving UTC, is Approach 2) good way to handle those cases or is there some better/recommended way?</li>
  </ol>
  
  <p><strong>[UPDATE]</strong></p>
  
  <p>Based on comments from Gerard Ashton and Hugo:</p>
  
  <p>Initial question was not clear with respect to the detail if tenant can change timezone or not and what happens if political authority changes the timezone properties or some terirory's timezone. 
  Of course that is of an extreme importance, but it is not in the center of this question . We might address that in a separate question.</p>
  
  <p>For the sake of this question, lets assume tenant will not change location. Timezone properties or timezone itself for that location might change and those changes will be handled in the system separately from this question.</p>