Incorrect setting of db_timezone when date cck field set to "No time zone conversion"

I am going to mark this as "won't fix" due to its age and the fact that d5.x is going the way of the dodo...
If there is still interest in this, feel free to re-open and it might be able to get committed.

Version 5.2 is critical bugfix only, no other support, no new features.

I think this affects the D6 version.

I have a date field with Granularity of Year and Month only and Time zone handling none.

The problem is that I would select a date of 2010/09 and on display it would be 2010/08.

From what I can tell by adding in some debug output in date_formatter_process() it is because my timezone is Africa/Johannesburg and when dates are being rendered it renders with a offset of 7200.

So 2010/09 becomes 2010-08-31 22:00:00.

I manually applied the date_api_db_timezone.patch and this seemed to fix it. I don't think the views patch still applies.

I don't know how this would impact on other timezone related functions.

Ok, the views patch also seem to still be applicable in date_api.views.inc, date_views_set_timezone().

Ok, I have found the same problem to exist in date_get_timezone() in date_api.module.

As for me, i fixed it with the following patch:

--- date_api.module.orig        2010-08-13 18:36:31.000000000 +0400
+++ date_api.module     2010-11-07 14:10:19.000000000 +0300
@@ -1763,6 +1763,7 @@
       $timezone = !empty($timezone) ? $timezone : date_default_timezone_name();
       break;
     case ('utc'):
+    case ('none'):
       $timezone = 'UTC';
       break;
     default:
@@ -1777,7 +1778,7 @@
 function date_get_timezone_db($handling, $timezone = '') {
   switch ($handling) {
     case ('none'):
-      $timezone = date_default_timezone_name();
+      $timezone = 'UTC';
       break;
     default:
       $timezone = 'UTC';
--- date_api.views.inc.orig     2010-08-12 18:13:59.000000000 +0400
+++ date_api.views.inc  2010-11-07 14:08:34.000000000 +0300
@@ -191,7 +191,7 @@
       $date_handler->offset_field = $field['offset_field'];
       break;
     case 'none':
-      $date_handler->db_timezone = date_default_timezone_name();
+      $date_handler->db_timezone = 'UTC';
       $date_handler->local_timezone = date_default_timezone_name();
       break;
     case 'utc':

The changes in #7 fixed an issue for me where multiple users in different timezones editing the same node resulted in the date getting changed. CCK date field with YMD granularity and no timezone handling.

I think this is still a problem in 7.x

@NaX I've been looking into this in D7 and I can't tell if this is a D7 issue or not. Have you seen this in D7? If so, please provide steps to recreate the issue.

I described how I found this bug in #4. From what I can tell you do the following to reproduce. Please note that I have not looked at this for a while so I am working from memory.

1. Create a date field with Granularity of Year and Month only and Time zone handling none
2. Set your default site time zone to a + time zone EG: East of GMT/UTC
3. Create a node and compare date displayed and value saved

I have not tested this with a - time zone EG: US time zones.
I also don't know what the server time zone setup will have on this kind of problem.

My understanding is that date values should always be saved as UTC and then offset with time zones on display. Additionally when a none time zone is selected it should use UTC, but I might be wrong.

The date is stored in the database with no timezone adjustment. If it was saved as UTC it would be getting a timezone adjustment, which should not happen. To keep the formatters from trying to adjust it *back* from UTC when displaying the date, the 'timezone' information is set to the site timezone. That is just a trick to keep the same value in the display, it has nothing to do with the value that is stored.

I don't think there is anything actionable to do here because it works as designed.

@KarenS
Ok, so you need a little more proof. I am going to try one last time to convince you there is a problem. I have run a few tests to try and reproduce this problem. I did the following.

I added three fields to a content type. Each field had Granularity of Year and Month, Time zone handling none and used the Select list widget.

Each field had a different type:

Field 1 = Datetime
Field 2 = Date
Field 3 = Datestamp

I saved a node setting each field to Feb 2012

On display the fields where formatted as follows.

Site Default time zone = Africa/Johannesburg
Field 1 = February 2012
Field 2 = February 2012
Field 3 = January 2012

It looks like there is a problem but it is localised to Datestamp only.

Next I changed the time zone to UTC. The display was the same but this time the edit form for Field 3 was Jan 2012. If I re-saved it with Feb 2012 it was correct on display and edit form.

Next I changed the time zone to America/Los_Angeles. The display was still Feb 2012 for field 3 but again the edit form changed to Jan 2012. I re-saved again setting field 3 to Feb 2012 and everything was correct on display and on the edit form.

Next I changed the time zone to Asia/Hong_Kong. All was correct until I re-saved the node. Now display shows Field 3 to be January 2012 again but edit form shows Feb 2012.

So I believe there is a problem but with only one field type and maybe the fixes/patches here incorrectly fix the problem. The other two field types never showed any signs of any problem only Datestamp.

The problem in #13 has nothing to do with the timezone conversion setting, it is a problem specific to dates that have a year or a year and month and use the timestamp. There was a similar problem in the D6 version but D7 is different and it has re-emerged. The problem is that the timestamp stores a date that is supposed to be accurate to the second. Using it to store a date that has only a month and a year means we have to create dummy values for all the missing date parts, then make sure those dummy values don't accidentally move it into the wrong period. It makes much more sense to store it as an ISO date.

Talking about this as a 'timezone' problem is a red herring, it has nothing to do with the timezone. This is a completely different problem. And we don't want to switch an existing issue which *was* talking about the timezone to now be about something different. Especially an issue from the D5 version.

So we need a new issue for this that more accurately describes the problem.

The timezone issue is fixed and works as designed. Please open a new issues with the details in #13.

I dont want waist your time but, I am sorry but I could not accept that this is not a time zone issue so I did some more digging and actually found the problem in date_convert. One thing I forgot to mention last time is that I am still on D6.

I believe the assumption of date_create() function assuming UTC for timestamps is incorrect. I believe it uses the current set time zone as returned by date_default_timezone_get() to offset the date but sets the $obj->timezone value to UTC or +00:00 depending on if the second value is passed in or not.

The current work around works when the passed in time zone is the same as the set time zone but when they are different then the date is offset incorrectly.

The attached patch fixes the problem for me in D6 by first converting to a formatted string using date("Y-m-d H:i:s").

EG:
Using the previous example of 1 FEB 2012 using current code of (date_create("@$date", timezone_open('UTC'));) with field set to no time zone.
Expected value = 2012-02-01 00:00:00

Passed in $tz (or default value) = 'UTC' and date_default_timezone_get() = 'Asia/Hong_Kong'
$obj->date = 2012-01-31 16:00:00

Passed in $tz (or default value) = 'UTC' and date_default_timezone_get() = 'Africa/Johannesburg'
$obj->date = 2012-01-31 22:00:00

Passed in $tz (or default value) = 'UTC' and date_default_timezone_get() = 'Africa/Johannesburg'
$obj->date = 2012-02-01 05:00:00

I was also able to reproduce this behaviour of date_create() in a plain PHP script outside of Drupal.

If you still feel I am wrong close the issue again as per your previous comment.

The code in D6 and D7 is TOTALLY different. This is totally useless as an actionable issue. Now you have taken a closed D5 issue, moved it between D5, D6, and D7 (all of which have totally different code).

This is not any longer actionable and I'm not going to try to sort it out. You had what might be some useful information about a D7 issue in one comment, that would be useful if moved to a D7 issue. You have what might be useful information about a D6 issue in another comment, but it's different than what was reported in the original issue. But this issue that tries to cover lots of topics across every version is a won't fix.

@KarenS
I have always appreciated and respected the great work you have done in the community and the really useful modules you have developed and maintained and you can run your projects any way you want, but after a condescending reply like that don't expect me to every spend a few hours of my time trying to debug an issue and submit a patch to any of your projects again :(

And for your information, it does not matter if you use date_create() as in D5 and D6 or sub class DateTime as in D7. Its all the same code at the end of the day handling the timestamps because it all uses DateTime. So shoot me for thinking its all related to the same issue and want to try and keep all the discussions and attempts at finding the problem together.

Ref:
http://drupalcode.org/project/date.git/blob/refs/heads/5.x-2.x:/date_api...
http://drupalcode.org/project/date.git/blob/refs/heads/6.x-2.x:/date_api...
http://drupalcode.org/project/date.git/blob/refs/heads/7.x-2.x:/date_api...

That is not a condescending reply. I have been spending hundreds of hours trying to keep the issue queue organized, figure out which bugs go on which versions, which are still relevant, which are still bugs, keep people from throwing everything but the bathroom sink into every issue, keep people from moving issues from one version to another. Hundreds and hundreds and hundreds of hours.

And whatever I say apparently it sounds like I am shooting people down.

I just want one topic per issue, one version per issue. When you add in a ton of information that applies to another topic or another version I have an issue that has become unmanageable.

I'm sorry you took offense. I don't know any way to manage hundreds of issues without responding very quickly as I go through them. I am just as frustrated as you, probably more. I get yelled at all the time and no one is happy with anything I say or do. I want to quit all the time, I only do this because people depend on it.

@KarenS
Ok, I will accept that as an apology and we will agree to disagree on this issue. I think every thing here is relevant to the same issue just different symptoms of the same problem, you seem to think its different. I know many devs are very busy and I expect a lot of short replies and answers and that is fine. If you want to know what irritated me in this case and what provoked me was how you cut the dominant poster in a issue (me) off at the knees and told them they were wrong without giving any sort of real feedback. I don't know why you want a different issue per a Drupal version, for me you ether clutter the issue queue or the issue, I would rather clutter the issue than the queue, but its your project and I will follow your lead. It would be nice if each project had its own project guidelines as this sort of thing does seem to change from one project to the next.

I will take back what I said and will create a new issue related to my last patch. As a heads up I will be referencing all the issues I think this patch relates to.