Databases
StackOverflow2013
Postsdbo
POIs it always a good idea to store time in UTC or is this the case where storing in local time is better?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIs it always a good idea to store time in UTC or is this the case where storing in local time is better?
    primarykey
    data
    text
    <p>Generally, it is a best practice to store time in UTC and as mentioned in <a href="https://stackoverflow.com/questions/2532729/daylight-saving-time-and-timezone-best-practices/">here</a> and <a href="https://stackoverflow.com/a/5667869/19917">here</a></p> <p>Suppose there is a re-occurring event let's say end time which is always at the same local time let's say 17:00 regardless of whether there is Daylight saving is on or off for that time zone. And also there is a requirement not to change the time manually when DST turns ON or OFF for particular time zone. It is also a requirement that whenever end time is asked by any other systems through API (i.e. GetEndTimeByEvent) it always sends the end time in UTC format.</p> <p><strong>Approach 1:</strong> If it is decided <strong>to store in UTC</strong> it can be stored in the database table as below. </p> <pre><code>Event UTCEndTime ===================== ABC 07:00:00 MNO 06:00:00 PQR 04:00:00 </code></pre> <p>For the first event ABC, end time in UTC is 07:00 am which if converted to display from UTC to local time on 1-July-2012 it will result into 17:00 local time and if converted on 10-Oct-2012 (the date when DST is ON for the time zone) then will result into 6 pm which is not correct end time.</p> <p>One possible way I could think is to store DST time in the additional column and using that time when the timezone has DST ON.</p> <p><strong>Approach 2:</strong> However, if it is <strong>stored as Local time</strong> as below for example for event ABC it will be always 17:00 on any date as there is no conversion to from UTC to local time. </p> <pre><code>Event LocalEndTime ======================= ABC 17:00:00 MNO 16:00:00 PQR 14:00:00 </code></pre> <p>And an application layer converts local time to UTC time to send to other systems through (API GetEndTimeByEvent).</p> <p><strong>Is this still a good idea to store the time in UTC in this case? If yes then how to get a constant local time?</strong></p> <p>Related Questions: <a href="https://stackoverflow.com/questions/1127781/is-there-ever-a-good-reason-to-store-time-not-in-utc?rq=1">Is there ever a good reason to store time not in UTC?</a></p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USSun
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POIs it always a good idea to store time in UTC or is this the case where storing in local time is better?
      1. USSun
      1. VTBountyStart
    2. VO
      singulars
      1. POIs it always a good idea to store time in UTC or is this the case where storing in local time is better?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POIs it always a good idea to store time in UTC or is this the case where storing in local time is better?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWill the program be used only for such a kind of events? Will it need to serve events occurring at the time of DST change? Should the program handle changing timezones?
      singulars
      1. POIs it always a good idea to store time in UTC or is this the case where storing in local time is better?
      1. USMichał Górny
    2. CO@Michal Yes, all the events are always local time (i.e. 4PM or 9PM etc...) of particular local timezone irrespective to DST is ON or OFF. Lets say a particular event "MNO" happens in London 4PM local time on everyday of the irrespective DST is on or OFF.
      singulars
      1. POIs it always a good idea to store time in UTC or is this the case where storing in local time is better?
      1. USSun
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload