Hi,

I create a Date CCK field named "field_date_event" and use it for two fields in my input form "Event Start" and "Event End". It works fine except that if I enter the same date and time in both fields it stores wrong value to its table "content_field_date_event".

For example if I enter 'Aug 03 2009' and Time is '12:00AM' into both fields, the data stored in the table will become:
field_date_event_value = 2009-08-02 16:00:00
field_date_event_value2 = 2009-08-02 16:00:00

So i'm not sure if it is a bug or just a way Date module handles the case but it clearly causes my Views search function could not perform correctly. Everytime if I enter 'Aug 03 2009' to search for the node, it returns empty result.

Any idea?

Thanks a lot,

Comments

coderintherye’s picture

coderintherye commented
Version: 6.x-2.4 » 6.x-2.6

I am experiencing this same issue and it is extremely frustrating to try to debug. The thing I noticed is that in addition to giving me those 16:00:00 dates as the OP describes, if I just create a date with a default value of "Now" it is pushing the date 24 hours in the database. However, the cck field and all other displays are showing the date correctly, it is only the database value that is 24 hours ahead.

I did a fresh install just to make sure and was able to reproduce the problem. I tested on to completely different systems.

System 1:
D6: 6.19
Linux: Red Hat Enterprise Linux 5
MySQL: 5.0.77
PHP: 5.2.13
Date: 6.x-2.6

System 2:
D6: 6.19
Linux: Ubuntu 9.10
MySQL: 5.1.37
PHP: 5.2.10
Date: 6.x-2.6

Note that I checked the time zone settings (as well as user time zone both on/off), database settings, and system time settings, and all appear correctly. Database timezone was checked using curdate() and curtime() functions in MySQL. I also do date creation in a custom module, and those are all correct, until they get put into a Date CCK field, which is when they get munged.

Steps to reproduce.

  1. Install fresh Drupal
  2. Install CCK module
  3. Install Date module
  4. Create a custom content type
  5. Add a date field with format of "date"
  6. Set the From date default value to be "Now"
  7. Set the To date default value to be "Blank"
  8. Set the To date to be "Optional"
  9. Create a new node with the new content type
  10. Note: It will automatically provide the date set to now
  11. Save the node, then come back to edit it
  12. Low and behold we now have a To Date that is set?! But how did this get here, we have set default to "blank" and to date as "optional
  13. Check the date in the node it will appear like: 2010-12-10 21:46
  14. Check the date in the database it will appear like: 2010-12-11T21:45
  15. Note the above, we have in the database "11" but "10" in the node

Unfortunately, as of yet, I can not declare if this is a CCK issue, a date issue, or a Drupal issue (or perhaps even a mysql or php issue). I hope with the above information someone is able to reproduce this issue and provide more data.

coderintherye’s picture

coderintherye commented

Ok well, now I have narrowed this down. If time zone settings for the date field itself are left to default or set to "Site's time zone", even though the site's time zone is set correctly and one can verify that, date is still munging it.

Solution is to set to "No time zone conversion"

coderintherye’s picture

coderintherye commented

Now that I thought about this, for what's it worth, these may be two separate issues, though I experienced them both on the same site and new sites.

I believe what is happening on the existing site, and probably for the OP, is that the site's time zone was initially not set. I believe I may have then set the time zone and then started experiencing this behavior.

I would still consider that a bug for date, because it should properly handle if the user changes the sitewide timezone. But alas, I don't have the energy to go back and try testing all the permutations.

choster’s picture

choster commented
Priority: Critical » Major

In fairness, as soon as one installs or updates Date, there is a message presented to check the site's timezone settings. The alternative to not setting a timezone is to set a default one, which would also be incorrect for most people, so I'm not sure what we could ask the developer to do.

coderintherye’s picture

coderintherye commented

Yeah, not sure how this got set to critical as I never changed the priority and it was originally set to major.

However, in addition to the time zone setting change piece, there is still a bug, that is reproducible using the steps above, just not sure if I want to hijack this original issue or start a new one. I'm not sure how the OP arrived at their problem, so it's tough to say if it belongs here, but probably best if I move this to a new issue.

arlinsandbulte’s picture

arlinsandbulte commented
Status: Active » Closed (works as designed)

There are 2 different issues here, and I think everything being discussed here is actually "by design".

Issue #1: Value stored in database is different than what was entered.
This is "by design" because ALL dates are always converted to UTC when stored to the database. When retrieved for display, they are then converted back depending on the date field's settings and the current user's time zone settings. See this handbook page for details on how this all works: http://drupal.org/node/767182

Issue #2: If the default value for the To Date is set to blank, it is stored with the same value as the From Date.
This is by design right now, but there has been lots of confusion over that and there is some discussion about changing that. The Date module requires the To Date to have a value for various reasons.
There are two issues regarding this, one for D6 and one for D7.
D6 issue: #523218: Default value for To date: Blank should stay blank
D7 issue: #874322: To Date & All Day Date Handling

My #4 comment on the D7 issue (http://drupal.org/node/874322#comment-3317858) provides a nice summary of the way things work now and some options about how it could be changed for the better.

coderintherye’s picture

coderintherye commented

Thanks, I think that does summarize the issue and that the follow up should be in #523218 as you suggest.