51939

How should I store date/time objects in SQL?

I've had this question for a long time. The problem is this: most SQL servers I've worked with (namely MySQL) don't store timezone information with dates, so I assume they simply store dates as relative to the server's local timezone. This creates an interesting problem in the case where I'll have to migrate servers to different timezones, or if I create a cluster with servers spread out over different datacenters, or if I need to properly translate date/times into local values.

For example, if I persist a date like 2011-08-12 12:00:00 GMT-7, it is persisted as 2011-08-12 12:00:00. If I have an event which happens across the world at a specific time, I have to assume that my server is storing dates in GMT-0700, (let's not even add daylight savings time into the mix) then translate them into dates depending on each user's local timezone. If I have multiple servers storing dates in their own timezones, all of this fails miserably.

For frameworks like Hibernate and Django, how do they deal with this problem, if at all? Am I missing something, or is this a significant problem?

Answer1:

As I see it, the best choices are:

    <li>Convert all times to UTC when storing them in the database, and localize them from UTC for display</li> <li>Store the UTC offset in minutes (at least one modern time zone is a multiple of ten minutes offset from UTC) in a separate column from the date/time</li> <li>Store the timestamp as a string</li> </ul>

    In my current project we encountered this issue (we use Postgres) and decided to store all times in UTC and convert as needed in the application. No separate storage of the time zone for us. We also decided that all client-server interaction using timestamps would be in UTC, and that local time zones would ONLY be considered for user interaction. This has worked out well so far.

    Since you tagged this question with Django, I'll add that the pytz module is extremely useful for dealing with locale timezone conversion.

    Answer2:

    Your answer for MySQL lies on this page MySQL Server Time Zone Support

    Basically MySQL offers automatic timezone support for any fields that use UTC (timestamp) field but not for fields that don't (date, time, and datetime fields). For UTC fields you can set the timezone from the client using SET time_zone = timezone;. For nonUTC fields you must calculate it yourself.

    Answer3:

    You are so right I've often run into this and tend to look the TZ up and store it in a static "VARS" table so at least I can move it later.

    Side notes:

      <li>Interesting the DATETIME manual doesn't even mention it</li> <li>It is affected by NOW() and CURTIME() as shown here</li> </ul>

      HTH

Recommend

  • Add two hours to a date within given hours that rollover to the next timeframe
  • I keep getting java.io.NotSerializableException: java.io.ObjectOutputStream
  • Adding list of data in Firebase
  • connect to Neo4j through Android - impossible task?
  • Convert java Date to dbf FoxPro datetime format
  • Javascript find all text except those in tag
  • How to get the time of a given city?
  • Guess timezone name for custom date
  • Facebook Events and timezones, how to convert UTC datetime to what facebook expects?
  • ocaml llvm kaleidoscope tutorial “Unbound module LlvmExecutionEngine”
  • In java, what time is the start of the day?
  • Unix timestamp to iso 8601 time format
  • Parse a very large CSV dataset
  • How can I programatically convert .xls and .csv files to .xlsx?
  • rename javascript object in order to use a php array
  • Using python to determine if a timestamp is under daylight savings time
  • Boost Spirit Qi Attribute Propagation
  • C++ development on linux Code::Blocks, EMACS or GVIM [closed]
  • JQuery form validation and submit scripts conflict
  • ASPNetCore MVC Routing Let Server Handle Specific Route
  • npm 5.4.1 install/uninstall all failing
  • jQuery ready not fired after rails link_to is clicked
  • d3 v4 drag and drop with TypeScript
  • JQuery Internet Explorer and ajaxstop
  • OpenGL 3.3 on Mac OSX El Capitan with LWJGL
  • Yii2: Config params vs. const/define
  • Algorithm for a smudge tool?
  • Repeat a vertical line on every page in Report Builder / SSRS
  • Android screen density dpi vs ppi
  • Opengl-es onTouchEvents problem or a draw problem? [closed]
  • Bug in WPF DataGrid
  • Running a C# exe file
  • Javascript convert timezone issue
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • Linking SubReports Without LinkChild/LinkMaster
  • Authorize attributes not working in MVC 4
  • Busy indicator not showing up in wpf window [duplicate]
  • Python/Django TangoWithDjango Models and Databases
  • java string with new operator and a literal
  • Net Present Value in Excel for Grouped Recurring CF