Tag: consistency

How did the Sunday go?

Yesterday Europe switched the daylight saving time. Though for most people thats a great change (you can sleep one hour longer, you’re not late for one day in whole year, etc), thats a data nightmare…

  • Same time happens twice
  • Time is not sequential value
  • Criteria for time gets fuzzy

So, if you are using local time, instead of UTC, your data may hit unique constraints (if anyone has unique constraints on datetimes, of course ;-), or simply you won’t be able to detect which moment of time it refers to (bad for audit trails!)

The fun part within MySQL is that DATETIME is time-zone agnostic (it just stores presentation values), whereas TIMESTAMP is not (it stores data in UTC and presents it based on session timezone), so the behaviors for these will be different.

How different? DATETIME will just have non-sequential lossy information, and TIMESTAMP will have… depends. If implicit TIMESTAMP default values are used, it will have correct UTC-based data. If NOW() or literals are inserted, that will provide with lossy presentation-time based values. In order for database to actually understand what literal value means, it would ned an offset included (“hh:mm:ss+hhmm”) in the string (though MySQL currently does not support this notation). NOW() behavior is probably a bug.

Storing time correctly opens another can of worms – user-provided time criteria cannot be converted into UTC, and therefore a lossy match is done. To complicate things even more, as indexes are in sequential time, but data matching is done based on non-sequential time, data returned will be different based on data access method (see Bug#38455).

So, the elegant fix for timezone support inside MySQL would be extending the presentation/literal format to support specified offsets, fixing datetime/timestamp code in multiple places – and that would quite some work to fix one hour per year.

Timezone support is my old interest, there’s lots of complexity and different implications, but most of people just don’t get to that – and may lose their data consistency.

So, how did your Sunday go?