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?
Matt Johnson-Pint :
A few things:\n\n\nMySQL 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.\nHow 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:\n\n\nA 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.\nIf 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.\nIf 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.\n\nThe 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.\n",
2019-04-04T18:25:09