1

I'm currently working on a PHP/mySQL web app where we store dates as unix timestamps in UNSIGNED INT(10) columns. Whenever we need to display dates in the web view we take that number and parse it with moment.js.

While one of my colleagues had doubts about this way of solving the task (he preferred storing dates as "YYYY-MM-DD hh:mm:ss" VARCHARs), so far we had zero problems.

I recently read that the european union is moving forward about abolishing daylight saving times. Will this affect my webapp in any way based on the particular way I'm storing dates?

Claudio Bonifazi
  • 17
  • 6

2 Answers2

1

A few things:

  • MySQL has standard data types built in for dates and times. They are DATE, DATETIME and TIMESTAMP. You can read about them more in the MySQL documentation here. You should choose from one of these types instead of storing integers or varchars.

  • How this relates to DST is very dependent on context. There is no one correct way to store all dates and times. Any advice to "always store in UTC" is shortsigted, and should be discouraged. Instead, think about the context of what the date and time represent. To elaborate:

    • A Unix Timestamp is always based on UTC and thus doesn't have anything to do with DST or other effects of time zones. It is a good way to represent the timestamp when something occurs in the present or has occurred in the past. In MySQL, the TIMESTAMP type aligns nicely with this concept.

    • If you are storing the scheduled time of an event in the future, then the local date and time are much more important contextually. In MySQL, you would store this in a DATETIME type. If you are dealing with multiple time zones, then you would also need the time zone identifier of that event (such as America/New_York), which you can store in a VARCHAR. In this case, DST is very dependent on the underlying rules associated with that time zone. MySQL has functions like CONVERT_TZ that understand these identifiers and use either the underlying OS time zone data or its own time zone tables to understand if DST is in effect.

    • If you are working with whole dates, such as birth dates, anniversary dates, hire dates, or summarizing data by a given business day, then you need to keep that as a date without a time or time zone. It's the same as if you were looking at a date square on a paper calendar. MySQL has the DATE type for this. DST is not relevant in this case, other than determining what date a point in time belongs to. For example, when we ask what day is "today", we are also considering time and time zone including DST - but once we state "April 4th 2019" then all that information is removed.

  • The last part of your question relates to how the underlying time zone data is implemented, and how it would be updated in the event of DST abolition in the EU. For this, I'll refer you to this answer, which explains the IANA time zone database and directly addresses the current EU concern.

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
0

I'm gonna come out and say no it shouldn't matter. I'm no expert, but I store dates as UNIX timestamps as well, and if you're generating your 10-digit timestamps something like:

getSecondsSinceEpoch = ((date) => Math.floor(date.getTime()/1000));

(or anything relying on Date.prototype.getTime()), then you're storing a universal date that doesn't care about timezones, which is good because if the user logs in from a different timezone, you're not returning varchars that are based on where they used to be. The same applies to when Daylight Savings Time changes the clock in their timezone (or fails to do so after being abolished.)

To get the correct human-readable string for the user's current location, Javascript relies on a timezoneOffset, which is not part of the date object but instead answers the question "how many timezones away from London is this browser right now?" (except in minutes instead of hours, and after accounting for Daylight Savings Time.) Presumably, the user's device will have become aware of the change, as will their browser, and your script will be able to act accordingly. (If their device is using the wrong timezone, your app won't be the only one that misbehaves until they get it sorted out.)

As I said, I'm not an expert on this stuff, so it's possible that I've convinced myself of something that's not true about the way that Javascript handles dates, but as far as my current understanding goes, I think your solution should be fine. I hope if I'm mistaken, some other nerd will chime in forthwith and let us both know.

Cat
  • 4,141
  • 2
  • 10
  • 18
  • Better later than never, I added a clarification about getTimezoneOffset. – Cat Apr 02 '19 at 14:02
  • This doesn't really answer the questions asked. In the future, please try to specifically focus on what was asked. Also, there's a few things conflated in your explanation, but the main one to point out is that the offset relates to minutes away from UTC (Coordinated Universal Time) for a specific point in time, not a count of time zones away from London. Remember - London has DST also. They call it British Summer Time (BST). – Matt Johnson-Pint Apr 04 '19 at 18:10
  • Thanks, Matt. Can you clarify the "for a specific point in time" bit about the offset and let us know what else I conflated? – Cat Apr 04 '19 at 18:29
  • `.getTimezoneOffset()` is a function that applies to a particular instance of a `Date` object - which represents a unique point in time (UTC based). Thus, the offset returned is for *that* point in time. `new Date().getTimezoneOffset()` gives the *current* time zone offset. It says nothing about what the offset was in the past or what it will be in the future. This applies for both daylight saving time transitions, or transitions related to changes in standard time. See "Time Zone != Offset" in [the timezone tag wiki](https://stackoverflow.com/tags/timezone/info). – Matt Johnson-Pint Apr 04 '19 at 18:32
  • As far as whether I answered the question, I have to disagree with you about that. Claudio wrote, "I recently read that the european union is moving forward about abolishing daylight saving times. Will this affect my webapp in any way based on the particular way I'm storing dates?", to which I gave a clear "no" and then tried to explain my reasoning (and to clarify that I wasn't 100% sure.) I'm not sure what would have been a more "real" answer. – Cat Apr 04 '19 at 18:32
  • The other thing conflated was that the human-readable string is based only in part by the offset, but also by the localized version of the time zone segment name in effect at that time. For example "Pacific Standard Time" vs "Pacific Daylight Time" in English, or "heure normale du Pacifique" vs "heure avancée du Pacifique" in French - they are driven primarily by the time zone `America/Los_Angeles`, and then the corresponding CLDR localization strings. (But again, not that important really.) Cheers. :D – Matt Johnson-Pint Apr 04 '19 at 18:35
  • Thanks, that's a good reference. I get 'offset' a little better now. – Cat Apr 04 '19 at 18:36
  • Sure, the format itself isn't relevant to the question of DST or not. It's more important that if unix timestamps (or MySQL `TIMESTAMP` types) are being used, that the conversion from local time to UTC is done with updated time zone data - which would include the EU change when/if applicable. Likewise, if stored as `VARCHAR` or `DATETIME`, then if stored as *local* time, then DST would matter with regard to reading the value back out and converting it to UTC. Again, updated time zone data is really the only concern relevant to the EU change. (The question was poorly asked, but ok.) – Matt Johnson-Pint Apr 04 '19 at 18:38