Dates are one day off

[kaffeeringe]

In one of my modules generated with Modulestudio, I use the DATE field. In the database there are the correct dates, but when I view the dates through Zikula, every date is one day early. So 2012-03-17 in the db becomes 16. Mar 2012 in the template.

I replaced Zikula's and Smarty's modifiers with the call for PHP's own function: http://de3.php.net/manual/de/function.date-format.php That one works right.

[craigh]

Time Zone problem?

[kaffeeringe]

Could be. But a whole day? For a field that only contains a date - not a DateTime? But for sure the error happens on the way through Zikula's DateUtil.

[craigh]

right - the date is probably something like 01-31-2011T00:00:00 then you subtract your TZ and end up with the previous day.

[kaffeeringe]

Sounds like a reason. But really: In which cases do you really need to change that the db says?

[craigh]

I can't speak to that, I was simply offering a possible explanation

[kaffeeringe]

You could speculate along with me ;-)

[craigh]

try changing your TZ to GMT and see if the problem goes away.

[rmburkhead]

Sorry if I'm resurrecting a zombie issue here. I'm not sure if @Guite is using the date/time data types in Most when you pick that particular data type, but I've found that using date/time data types at the database level has been problematic across several projects that I've worked on. I avoid using the database date/time data types like the plague.

The problem is, essentially, that the code is really dealing with three time zone settings: the OS's setting, PHP's setting, and the database server's setting. The OS and PHP tend to play nicely together, in that if you tell PHP specifically what time zone to deal with, then it ignores the OS setting, and works as you expect it to.

Database servers (I'm talking server software here, not hardware--so it doesn't matter if the db server is physically on the same box or not), especially MySQL are notoriously bad at dealing with time zones. Further complicating things, hosting providers are even more notorious for "forgetting" to make the database server's configured time zone match the OS time zone. The root of the problem with MySQL is that the date/time data types do not store a specified time zone. I believe that when a time zone is specified (e.g., 2011-12-20T21:54Z or 2011-12-20T14:54-07, both representing the same date/time), them MySQL will convert it internally to whatever time zone is configured for the server, and store the date/time without a zone specification. That usually works out well if PHP and the database server agree on what time zone they are operating with. If they don't agree, then some quite unexpected things can happen over on the PHP side. MySQL will happily give the date/time back to PHP as whatever it converted it to internally when it was stored. If PHP assumes that it is getting back what it originally stored, then everything gets confused. Date-only fields are especially troublesome, because they can vary by +/- 1 day, depending on the severity of the time zone mismatch, and whether the date to be stored is within the window that will cause it to be converted to another date in the database server's time zone.

I almost never use the database date/time data types in any projects I have control over. They just end up causing more problems than they are worth. Instead, I usually explicitly convert the date/time to UTC and either store an ISO 8601 string, or a Unix time stamp. If I need the original time zone back, then I also store that (usually as an Olson TZ database string in another field), so I can convert the time back to the original TZ (or I store it as an ISO 8601 without converting it to UTC, again with the Olson TZ name in another field). Avoiding the database date/time data types avoids a ton of headaches later on.

If one uses the database date/time types, then most problems can be avoided by 1) ensuring that PHP and MySQL agree on what time zone is in use, and 2) never, ever, ever using the date/time functions and/or date/time math available in SQL for any reason. If one ensures these two conditions, then MySQL will just be giving back the same date/time that it was originally given (since PHP and MySQL agree on the time zone in use). Doing date/time math and functions exclusively in PHP avoids all of the problems MySQL causes by its inconsistent ignorance of time zones. (Remember, MySQL doesn't store time zones in the fields, so functions and math all operate within the server's configured time zone.) It usually just ends up easier to never let MySQL touch a date/time as a date/time type at all, ever.

[Guite]

Converting to UTC and back seems to be what http://www.doctrine-project.org/docs/orm/2.0/en/cookbook/working-with-datetime.html#handling-different-timezones-with-the-datetime-type does also suggest.

[craigh]

some time ago, I was doing some background research on this issue and came across this:

http://drupalize.me/videos/dates-calendars-drupal-diwd-09

obviously, it is drupal specific, but they/she discuss the methodology they used and decisions they made (IIRC). I found it interesting and useful - but I've done nothing with it.

IMO, Zikula needs to completely revamp and standardize date and time handling.

/returns to hermitage

craig

On Dec 20, 2011, at 5:13 PM, Robert Burkhead wrote:

Sorry if I'm resurrecting a zombie issue here. I'm not sure if @Guite is using the date/time data types in Most when you pick that particular data type, but I've found that using date/time data types at the database level has been problematic across several projects that I've worked on. I avoid using the database date/time data types like the plague.

The problem is, essentially, that the code is really dealing with three time zone settings: the OS's setting, PHP's setting, and the database server's setting. The OS and PHP tend to play nicely together, in that if you tell PHP specifically what time zone to deal with, then it ignores the OS setting, and works as you expect it to.

Database servers (I'm talking server software here, not hardware--so it doesn't matter if the db server is physically on the same box or not), especially MySQL are notoriously bad at dealing with time zones. Further complicating things, hosting providers are even more notorious for "forgetting" to make the database server's configured time zone match the OS time zone. The root of the problem with MySQL is that the date/time data types do not store a specified time zone. I believe that when a time zone is specified (e.g., 2011-12-20T21:54Z or 2011-12-20T14:54-07, both representing the same date/time), them MySQL will convert it internally to whatever time zone is configured for the server, and store the date/time without a zone specification. That usually works out well if PHP and the database server agree on what time zone they are operating with. If they don't agree, then some quite unexpected things can happen over on the PHP side. MySQL will happily give the date/time ba ck to PHP as whatever it converted it to internally when it was stored. If PHP assumes that it is getting back what it originally stored, then everything gets confused. Date-only fields are especially troublesome, because they can vary by +/- 1 day, depending on the severity of the time zone mismatch, and whether the date to be stored is within the window that will cause it to be converted to another date in the database server's time zone.

I almost never use the database date/time data types in any projects I have control over. They just end up causing more problems than they are worth. Instead, I usually explicitly convert the date/time to UTC and either store an ISO 8601 string, or a Unix time stamp. If I need the original time zone back, then I also store that (usually as an Olson TZ database string in another field), so I can convert the time back to the original TZ (or I store it as an ISO 8601 without converting it to UTC, again with the Olson TZ name in another field). Avoiding the database date/time data types avoids a ton of headaches later on.

If one uses the database date/time types, then most problems can be avoided by 1) ensuring that PHP and MySQL agree on what time zone is in use, and 2) never, ever, ever using the date/time functions and/or date/time math available in SQL for any reason. If one ensures these two conditions, then MySQL will just be giving back the same date/time that it was originally given (since PHP and MySQL agree on the time zone in use). Doing date/time math and functions exclusively in PHP avoids all of the problems MySQL causes by its inconsistent ignorance of time zones. (Remember, MySQL doesn't store time zones in the fields, so functions and math all operate within the server's configured time zone.) It usually just ends up easier to never let MySQL touch a date/time as a date/time type at all, ever.

---

Reply to this email directly or view it on GitHub: https://github.com/zikula/core/issues/140#issuecomment-3226271

[rmburkhead]

Yes, that works out most of the time if the MySQL time zone is set to UTC, or if you ensure that you never use date/time functions or date/time math in queries. You can still run into problems when the MySQL time zone is misconfigured. For some projects I was forced to issue a SET time_zone = '+00:00' (or something like that) at the start of every session to force a poorly configured MySQL server (whose configuration was out of my control) to bend to my will.

[espaan]

So where does it lead to now? Avoid db date time usage and nothing else ?

[phaidon]

@drak @espaan @Guite @cmfcmf @craigh

Can we close it? It do not think this is a problem, which can be solved by zikula.

[craigh]

I vote to close.

[espaan]

I agree to close.