We are using a CCK date field in several node types with granularity: day, with UI using the popup calendar, and using a views filter to select a particular day to display in a list.

After I upgraded from rc5 to rc6, nothing changed according to users when they entered data, but to display articles using views, I had to set the filter in views back one day.
For example, an article with a CCK date field marked Dec. 8 will only display if the view is set to Dec. 7, although if you edit the article node it says it is Dec. 8.

Using phpmyadmin I went into the database and found nodes created before the upgrade had a CCK date field set to a unix date of midnight for the correct date, such as 1227571200 for Tue, 25 Nov 2008 00:00:00.
However after the upgrade new nodes have the unix dates is set for 11:00 pm on the day before such as 1228777200 for Sun, 07 Dec 2008 23:00:00 (when it should be Mon, 08 Dec 2008 00:00:00)

After finding this problem on production, I reproduced it on our test server, then downloaded the latest dev release and reproduced it on test again.

Other notes:
Granularity is to the day.
Running Drupal 6.6 with PHP Version 5.2.4-2ubuntu5.3. Modules include date, dateAPI, Date popup, Date copy, and Date timezone.
My server timezone is set to CET (GMT +1). In Drupal, my europe time zone is set to Europe/Prague with user-configurable time zones disabled. This might be relevant as GMT is exactly 1 hour behind us (the same amount the dates are off) but I guess it might be unconnected.

Also, this is my first bug report -- please let me know how it should be improved.
Thanks!

Comments

brynp’s picture

brynp commented
Priority: Normal » Critical

After reading the description of the Priority values, I think this should issue should probably be marked critical.

brynp’s picture

brynp commented

Just tried with the latest dev release but the database continues to store the wrong unix timestamp in the same way.

brynp’s picture

brynp commented

Tried with latest release (dec. 18) with same result.

brynp’s picture

brynp commented
Status: Active » Fixed

Issue resolved, thanks to this comment here: http://drupal.org/node/345512#comment-1171519

In short -- if you are using a CCK date field with granularity set to the day, make sure your default time zone is set to blank; otherwise it will store the date as UMT + or - your local timezone, which will change the day backward one day if you're in Europe, Asia or Africa. It can be confusing because the date will continue to display correctly in the node with your CCK field, but it will not in other circumstances (such as in a filter in views).

I don't know enough to say if this is worth changing, so I'm marking it to fixed since it is resolved for me.

ceejayoz’s picture

ceejayoz commented
Status: Fixed » Active

Unacceptable fix. Setting the default time zone to blank or UTC means my post dates are off.

People need both their CCK date fields *and* their non-CCK date data to be consistent.

karens’s picture

karens commented
Status: Active » Postponed (maintainer needs more info)

The problem is that older versions of the module were storing the wrong value -- dates without times should have been stored as local dates, not UTC dates. This has since been corrected and the system will correctly store the local date. So everything from here on should work correctly without playing any games with your timezone settings. You should find that the date being stored is the local time, and all the Views handling will expect it to be local time and do no further timezone conversion when bringing it out of the db.

If you see anything that looks like it is not behaving this way, that would be a bug. If it is displayed wrong in Views somewhere, I need an example of a place where the value is correct in the node but wrong in Views. I'll need an export of the view so I can see exactly how it's set up.

karens’s picture

karens commented
Status: Postponed (maintainer needs more info) » Closed (won't fix)

No response in the last month, lots of changes since it was posted, no idea if it still applies to the latest code, and not enough information to replicate the issue if it is still a problem in the latest code.