Date/Time overhaul

[adam-buckley]

Since inception, Cmfive has had an uphill battle with dates.

This issue is a proposal to fix what is broken/not up to scratch - once and for all.

The Problem

The problem is due to the timezones set on both the web server and the database server, and how they interpret times from the users requests (via the browser). The root of the issue is that we're assuming the timezone without specifying it from the browser, which in our case is always Australia/Sydney.

The Solution

The solution involves multiple steps:

• Remove the conversion of MySQL Datetime's to UNIX epoch timestamps when retrieving from the database, code here
  • Fix any pieces of code that is expecting an epoch timestamp for dates
• Change deployment targets in Boilerplate to change timezones to UTC
• Change frontend elements to include the user's timezone
  • This may also include some UI elements to indicate what timezone dates are being displayed in
  • After this the default conversions should kick in - this will be important to test thoroughly
• Create a migration to move any times assumed to be in Australia/Sydney - to UTC
• Develop migration plans for any production instances

[careck]

I like this in principle, however if we're no longer converting MySQL datetime to unix timestamp, how are we going to handle dates and times in PHP code? What does the MySQL driver natively return for a datetime field?

[adam-buckley]

I like this in principle, however if we're no longer converting MySQL datetime to unix timestamp, how are we going to handle dates and times in PHP code? What does the MySQL driver natively return for a datetime field?

It returns a string in a predictable format as far as I know, which means we can load them into PHP's Date library via the constructor

(Edit: which is how we should be handling dates!)

[iceaxeliehne]

I like this in principle, however if we're no longer converting MySQL datetime to unix timestamp, how are we going to handle dates and times in PHP code? What does the MySQL driver natively return for a datetime field?

It returns a string in a predictable format as far as I know, which means we can load them into PHP's Date library via the constructor

(Edit: which is how we should be handling dates!)

Hey, I think timestamps are actually best to use as they are timezone agnostic. I don't like how saving and retrieving dates and times are asymmetrical, you need to provide a date string to save but get a timestamp when you retrieve. I think cmfive should require a timestamp to save datetimes and should return the very same timestamp when you retrieve. this would enforce timezone relative data be removed from datetimes before they are stored. Timezones should only be relevant when a user wants to view or filter datetime data.

Unless, you want "dt" variables to be php DateTime objects however I'm not sure this is good because this will apply some timezone to the date that will need to be checked or changed when formatting. This is fine if as has been the case, there is only one relevant timezone but my usecase involves displaying multiple dates and times across multiple timezones on the one screen. I would prefer to have dt variables be timestamps and to only apply a timezone when I need to format for display.

[adam-buckley]

Hey, I think timestamps are actually best to use as they are timezone agnostic. I don't like how saving and retrieving dates and times are asymmetrical, you need to provide a date string to save but get a timestamp when you retrieve. I think cmfive should require a timestamp to save datetimes and should return the very same timestamp when you retrieve. this would enforce timezone relative data be removed from datetimes before they are stored. Timezones should only be relevant when a user wants to view or filter datetime data.

Unless, you want "dt" variables to be php DateTime objects however I'm not sure this is good because this will apply some timezone to the date that will need to be checked or changed when formatting. This is fine if as has been the case, there is only one relevant timezone but my usecase involves displaying multiple dates and times across multiple timezones on the one screen. I would prefer to have dt variables be timestamps and to only apply a timezone when I need to format for display.

Retrieving dates are only in a unix timestamp because Cmfive requests it. We can easily save DateTime objects without a timezone (which means just using UTC), letting language and engine handle dates and times ensures we don't need to worry about the many nuances of dealing with time.

As long as the server and database are in UTC no random timezone conversions will occur unless necessary. Conversion should occur either at the display layer in Cmfive or in the browser.

Either way I'm quite firmly of the conviction (although I'm open to counter arguments) that we shouldn't use unix timestamps, and here's why:

• MySQL date operations can be reliably done, e.g. adding a date duration to a timestamp doesn't take into account leap seconds etc
  • The same is true for PHP
• Unix timestamps are 32bit and subject to the 2038 problem
• MySQL 8.0.19 added support for timezone offsets which means native timezone support
• Reading timestamps at the database level is impossible

[iceaxeliehne]

Retrieving dates are only in a unix timestamp because Cmfive requests it. We can easily save DateTime objects without a timezone (which means just using UTC), letting language and engine handle dates and times ensures we don't need to worry about the many nuances of dealing with time.

As long as the server and database are in UTC no random timezone conversions will occur unless necessary. Conversion should occur either at the display layer in Cmfive or in the browser.

Either way I'm quite firmly of the conviction (although I'm open to counter arguments) that we shouldn't use unix timestamps, and here's why:

• MySQL date operations can be reliably done, e.g. adding a ~date~ duration to a timestamp doesn't take into account leap seconds etc

  • The same is true for PHP
• Unix timestamps are 32bit and subject to the 2038 problem
• MySQL 8.0.19 added support for timezone offsets which means native timezone support
• Reading timestamps at the database level is impossible

2038 Problem!!!! cool. We should not use timestamps anywhere. say no more.

The biggest risk I can see is that php DateTime can default to UTC in some situations and this may be hard to debug sometimes. We will be leaning on developers to ensure that user input dates (which will be relative to a specific timezone) are correctly having their timezone applied so that converting to UTC on save occurs correctly.