Problem with timezone handling (caused by date_get_timezone_db returning only UTC)

The UTC value is stored in the database. That is the expected behavior.

Regenerated patch

That doesn't make much sense. I have a site where people can store events from all over the world. What if I want to specify a time in Australia/Brisbane time zone? If that gets returned in UTC, the person entering it gets all confused at best.

So the user specified time zone should be entered into the database so it can be replayed properly.

If you want to store a UTC in the database, you should store an extra timestamp field value.

That's probably the best option so downstream (in views etc) I don't have to worry about time zone issues.

PS: currently you can change the timezone all you want, it is not stored at all. I have a field where people can enter a timezone, and that value isn't retained.

Forget my comment, all works with latest dev version, timezone is properly stored.

For me it seems that the value is stored properly in UTC, but there is something weird with the display.

My example (just downloaded latest dev-version today):

I have event with one field with a "Date's timezone" handling, so that I can test different timezones.
When I save the node in UTC, date value is saved in UTC and offset is 0.
When I save in other timezones, date value is always UTC, but offset changes to match the timezone.

My problem is that I always see the inputted hour, no matter which timezone I choose. Shouldn't it change to match my site's time zone (eg. when I save 19:00 in +0200, it should display 20:00 because my site's timezone is +0300)??

I realize that this issue is extremely old, but it seems like the right place to post this.

I'm the author of the Date iCal module, and I recently ended up digging through the Date API code as part of a bugfix for one of my users. In doing so, I discovered this date_get_timezone_db() function, and realized how broken it is. I did a lot of research to confirm that the fix in the patch I've attached is correct.

date_get_timezone_db() will now correctly return the actual timezone ID in which the date in the database is stored. The "Site's time zone", "User's time zone", and "UTC" options for the Time zone handling setting on Date fields all convert the date to UTC before storing it in the DB. But the "Date's time zone" and "No time zone conversion" settings don't store the date in UTC. My patch is the best way I could find to report the actual timezone of the stored date.

Unfortunately, this doesn't remedy the real problem, which is that the some of the Date code which calls date_get_timezone_db() doesn't pass a value for the $timezone argument when it should be doing so. Due to this problem, the "Date's time zone" setting is completely broken. But a patch that would fix that is out of scope for this particular issue, and I don't have time to go hunting through the Date codebase to fix all the calls to this function.

is it against 7.x-2.x?

Oh wow, I completely forgot to change the version number. Sorry about that.

I created this patch is against 7.x-2.7. I'll go see why it's failing against 7.x-2.x.

7: date-date_get_timezone_db-fix-998076-7.patch queued for re-testing.

:) thanks for the prompt reply @coredumperror. You may need to checkout 7.x-2.x as we have made some commits after 7.x-2.7 (http://drupalcode.org/project/date.git). give a try with 7.x-2.x. (Checkout instruction in Version control tab).

Hmmm, the patch seems to apply just fine against 7.x-2.x. From the base folder of the date repo, I'm using "patch -p1 < /path/to/patch" and I get no errors. It's patching a file in the date_api module, though, so maybe that's the issue?

git apply -v works, too.

Hmmm, it looks like it passed this time. Which is actually pretty surprising, since it fixes a significant bug (fields set for "Date's time zone" were being reported as being stored the DB as UTC, which was false). Considering how buggy the "Date's time zone" option seems to be because of the way other code in Date calls this function, I'd guess that the tests don't cover it very well.

7: date-date_get_timezone_db-fix-998076-7.patch queued for re-testing.

Looks good to me.

#7 commited
Thanks!

Unfortunately, #7 breaks every "Date's time zone" date display on my site.

In my case, these dates are actually stored in the database as UTC, so @coredumperror's conclusion here is not true -- at least for my site:

> But the "Date's time zone" and "No time zone conversion" settings don't store the date in UTC.

I've attached a patch that remedies the issue here, and that should do no damage for other circumstances, since date_get_timezone_db() is most often called with a $timezone value. Where it is not -- as for the "Date's time zone" date fields I'm seeing -- it will properly select UTC.

       if ($timezone == NULL) {
         // This shouldn't happen, since it's meaning is undefined. But we need
         // to fall back to *something* that's a legal timezone.
-        $timezone = date_default_timezone();
+        $timezone = 'UTC';
       }

Without this patch, the #7 solution would require modifying the date data stored in the database. My guess is that many people -- perhaps everybody -- using "Date's time zone" style date fields will experience this problem once they upgrade to 2.8. For this reason, I've upped the priority of this issue.

Please, create test case for this issue
You should create a test against Your possible bug with full step by step for ability to investigate this issue

Marked #2315709: 'All day' setting ignored/by passed in data format 7.9 as a duplicate

We encountered the above issue on our site exactly as elstudio mentions in #22.

We use 'date's time zone' and after updating from 2.7 to 2.8 all of our times (including all-day events) are displaying 1 hour out.

I can confirm that the patch in #22 fixes the issue for us.

What I've observed since installing 7.x-2.8 is that our 'date's time zone' date fields display OK, but when editing the node to which they're attached, the time is off by the Date fields offset to UTC (Australia/Sydney is +1000 so the time is shown 10 hours before what it should be).

Applying the patch at #22 remedies this for us

As mentioned in #23, this needs tests. However, the patch in #22 fixes the problem for our site. We recently upgraded to 2.8 because of the SA from 2.5.

Hi,

After doing some testing, I believe patch in #22 is wrong, because in the current state of the code, it assumes that all dates stored in the DB when a date field seeting is set to "date timezone" are stored in UTC (which is not the case given that the point of the "date timezone" setting is to allow the user to chose the timezone of the date at input time).

Here is my understanding of things: I believe that the whole problem comes from a confusion between the timezone and timezone_db variables.

• "timezone_db" is used, in the code, to represent the timezone in which the date is IN THE DATABASE (this means UTC for site, user and utc settings, but DIFFERS for 'date timezone' setting)
• "timezone" is used, in the code, to represent the timezone in which the date should be displayed to the end user (this means UTC for the utc setting, site timezone for site setting, user timezone for user and date settings)

Where it gets confusing with the 'date timezone' setting is that, in the database, the name of the column storing the timezone in the schema is 'timezone' and not 'timezone_db'. Therefore, in hook_field_load, when the date field is retrieved from the database, the 'timezone' column should be "transferred" to the timezone_db column, and when rendering the date, the 'timezone' column should simply be ignored.

The patch provided in #7 is going in the right direction, but doesn't fix the issue entirely, given that as it is mentioned, it doesn't rewrite the calls to date_get_timezone_db. The attached patch goes a bit further to try to retrieve and display the date in the right timezone.

I will try to provide a more complete patch with tests.

Changed status for testing

At least under date 7.x-2.7, fields stored as "Date timezone" in ISO format are stored in the database as UTC.

For example, the following dates were saved to the node as 2014-10-21 13:00 to 17:00 America/Los_Angeles (see screenshot attached https://www.drupal.org/files/issues/Screen%20Shot%202014-08-15%20at%2012...).

Notice that they are in fact stored as UTC in the database, as the following query output shows:

mysql> select field_expo_session_time_value, field_expo_session_time_value2, field_expo_session_time_timezone, field_expo_session_time_offset, field_expo_session_time_offset2 from field_data_field_expo_session_time where entity_id = 7166;
+-------------------------------+--------------------------------+----------------------------------+--------------------------------+---------------------------------+
| field_expo_session_time_value | field_expo_session_time_value2 | field_expo_session_time_timezone | field_expo_session_time_offset | field_expo_session_time_offset2 |
+-------------------------------+--------------------------------+----------------------------------+--------------------------------+---------------------------------+
| 2014-10-21T20:00:00 | 2014-10-22T00:00:00 | America/Los_Angeles | -25200 | -25200 |
+-------------------------------+--------------------------------+----------------------------------+--------------------------------+---------------------------------+
1 row in set (0.00 sec)

Could it be that "Date timezone" treats timezones differently for Unix timestamps and ISO format dates? The ISO format dates I'm seeing are all stored as UTC in the database.

I hear everybody about needing test cases, but I'm afraid I'm out of my depth here. Nothing I see in the existing date module test cases seems to address database storage. Can somebody point me in a helpful direction?

There is indeed a difference in the storage of dates with the setting "Date's Timezone" between 2.7 and 2.8: in 2.7, the dates were converted to UTC, while in 2.8, they are not anymore. This is quite worrying given that this means someone who was using 2.7 or earlier will suddenly get different storage behavior with 2.8.

In the following, the first line in the DB is a date stored with 2.7, the second one a date stored with 2.8:

mysql> SELECT * FROM field_data_field_date_iso_format;
+-------------+---------+---------+-----------+-------------+----------+-------+-----------------------------+--------------------------------+------------------------------+
| entity_type | bundle | deleted | entity_id | revision_id | language | delta | field_date_iso_format_value | field_date_iso_format_timezone | field_date_iso_format_offset |
+-------------+---------+---------+-----------+-------------+----------+-------+-----------------------------+--------------------------------+------------------------------+
| node | article | 0 | 1 | 1 | und | 0 | 2014-08-18T06:15:00 | Europe/Berlin | 7200 |
| node | article | 0 | 2 | 2 | und | 0 | 2014-08-18T08:15:00 | Europe/Berlin | 7200 |
+-------------+---------+---------+-----------+-------------+----------+-------+-----------------------------+--------------------------------+------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM field_data_field_date_normal;
+-------------+---------+---------+-----------+-------------+----------+-------+-------------------------+----------------------------+--------------------------+
| entity_type | bundle | deleted | entity_id | revision_id | language | delta | field_date_normal_value | field_date_normal_timezone | field_date_normal_offset |
+-------------+---------+---------+-----------+-------------+----------+-------+-------------------------+----------------------------+--------------------------+
| node | article | 0 | 1 | 1 | und | 0 | 2014-08-18 06:15:00 | Europe/Berlin | 7200 |
| node | article | 0 | 2 | 2 | und | 0 | 2014-08-18 08:15:00 | Europe/Berlin | 7200 |
+-------------+---------+---------+-----------+-------------+----------+-------+-------------------------+----------------------------+--------------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM field_data_field_date_timestamp;
+-------------+---------+---------+-----------+-------------+----------+-------+----------------------------+-------------------------------+-----------------------------+
| entity_type | bundle | deleted | entity_id | revision_id | language | delta | field_date_timestamp_value | field_date_timestamp_timezone | field_date_timestamp_offset |
+-------------+---------+---------+-----------+-------------+----------+-------+----------------------------+-------------------------------+-----------------------------+
| node | article | 0 | 1 | 1 | und | 0 | 1408342500 | Europe/Berlin | 7200 |
| node | article | 0 | 2 | 2 | und | 0 | 1408342500 | Europe/Berlin | 7200 |
+-------------+---------+---------+-----------+-------------+----------+-------+----------------------------+-------------------------------+-----------------------------+
2 rows in set (0.00 sec)

mysql>

EDIT: I actually found out that in 2.8, the dates are actually converted to whatever the user or site timezone is. So, if the timezone you set in the date field is the same as the site or user timezone, it will look like no conversion is being made, HOWEVER if you have your user timezone set to Europe/Berlin and you set the date field timezone to Asia/Hong Kong for example the date will be converted from Asia/Hong Kong to Europe/Berlin...

I think this should be critical since it will potentially cause loss of data - the way data is stored has changed in 2.8 and the change has not been handled correctly.

Here is a new patch which makes the edit form work (before dates in the edit form were converted, which wasn't right) and makes sure dates are NOT converted to UTC before being saved.

Summary of this issue (all of the below items concern date field with "date's timezone" setting set):

1. In date < 7.x-2.8 (or date 7.x-2.8 with patch in #22), dates were converted to UTC before being saved in the database
2. In date 7.x-2.8, dates are now converted to whatever the site or user timezone is set before being saved to the database
3. The correct behavior for SAVING these fields, as indicated in https://www.drupal.org/node/1455578, is NOT to convert dates (before they are saved in the database)
4. The correct behavior for DISPLAYING these fields, again as indicated in https://www.drupal.org/node/1455578 is to convert dates to whatever the user or site timezone is set to

I believe this patch fixes both 3 and 4, however it doesn't change the date fields saved using 7.x-2.7 from UTC to whatever their timezone should be. Therefore, here is what's missing to this patch:

1. Tests
2. Patch other parts of the date module calling date_get_timezone_db (such as date_repeat_field or date_views)
3. Write an upgrade code to convert the dates in the existing date fields from UTC to their correct timezone

New patch with points 1 and 3 of my last comment done (ie some tests and an upgrade script). Note that I haven't tested the upgrade script and that the patch in this issue: https://www.drupal.org/node/2323191 needs to be applied in order for the tests to pass (otherwise they will fail, but for wrong reasons).

Ok this patch should have it all. I don't expect it to pass testing as the testing should be fixed with this: https://www.drupal.org/node/2323191. However, I've tested the following:

• With date 7.x-2.7, the test I've added fails, while with date 7.x-2.8 and this patch, the test passes
• I've tested the upgrade script on a small database, and it worked, updating all of the dates from UTC to their timezone

It would be great however if we could get some testing from other people...

If I'm reading this correctly,the update script should only apply against date field with "date's timezone" setting set? Do any of the circumstances of 2.8 saving date data apply other scenarios? I've taken a stab at updating the issue summary. Let me know if I got anything wrong.

Yes, the update script should only apply to date fields with the date's timezone setting set. I don't think there should be any issue with date fields with other settings...

Hmm not sure why the testing failed now that https://www.drupal.org/node/2323191 has been applied... The tests pass on my local Drupal installation... If someone has an idea on this...

Started seeing this on a site after upgrading to Date 2.8. The patch in #34 seems to resolve the issue.

Three tests fail for me locally.

• Found the correct date for a date field using minute granularity with date timezone handling.
• Found the correct date for a datestamp field using minute granularity with date timezone handling.
• Found the correct date for a datetime field using minute granularity with date timezone handling.

All three fails have the $should_be set to Thu, 10/07/2010 - 04:30 to 05:30 while the page is displaying Wed, 10/06/2010 - 19:30 to 20:30.

+++ b/tests/date_timezone.test
@@ -83,6 +84,12 @@ class DateTimezoneTestCase extends DateFieldBasic {
+          $user->timezone = 'Europe/Berlin'; // UTC + 2

This may have something to do with the fails. The timezone is set, but the user object is never saved. However, adding user_save($user) after setting the timezone still doesn't fix the issue.

@jhedstrom Can you tell me which default timezone the site you use for testing has ?

It looks to be set to

date_default_timezone: 'America/Los_Angeles'

That's the main site though, I'm not sure what the simpletest testing site gets set to--perhaps that's the issue?

Ok let's try this...

Ok now it passed. The issue was that the test was being impacted by the date_default_timezone of the system. If you were running tests on a system with date_default_timezone set to UTC + 2, tests would pass, otherwise they wouldn't. Forcing, in the test, date_default_timezone to UTC + 2 through a variable_set makes the tests pass...

This fixes the issue, and tests are now all passing.

@guillaumev I'd like to ask you why you did only changes in 'minute' case?

I'd like also to ask you for performing simple test:

1- create blank Drupal page and set timezone to eg. Europe/Berline, enable Date module with your patch
2- modify the Article content to contain Date field (set to UTC timezone handling)
3- create an Article with date field
4- display the Article

does the date stored in Database is really UTC?
does the date retrieved from Database (when you display the Article) is being converted to your site's timezone?

please have a look at other issue: https://www.drupal.org/node/1885270

It looks similiar but your patch #34 didn't solve my problems

tl;dr: Does it make sense to split up the functionality of tz storage (widget settings) and tz display (formatter settings) to stop existing sites getting a nasty surprise on upgrading?

One note on applying #48. It does work for datetime 'date timezone' fields, st. the times are updated, and stored in the timezone of the field in the database in place of in UTC. Reading the discussion, documentation and intention of previous coders, it seems this has been a area with conflicting documentation/code.

So what also happens on applying this patch is what in actuality happens in the database and with the displayed value of fields that are in existing sites. This will have the effect of changing the date, which one can assume was configured to be correct by the working of the module in reality if not by documentation.

For example. Correct or incorrect, what is happening on existing sites now is:
a field is entered with 03:00 UTC+3;
it is stored as 00:00 with an offset for UTC+3;
it is then displayed depending on site timezone or user timezone - say this is UTC - as 03:00 with the timezone should you show it of UTC+3.

What will happen when sites upgrade is:
a field is, or was, entered with 03:00 UTC+3;
it is stored as 03:00 with an offset for UTC+3;
it is then displayed depending on site timezone or user timezone - say this is UTC again - as 00:00 UTC.

So the displayed date will change on live sites, probably not to the intended date. Now it may be correct, but possibly not what is desired for configured sites.

If however the functionality of what timezone the time is displayed in is added to the formatter, then the option can be added to maintain the present behaviour or other behaviour. Plus it might actually make it clearer:
Widget says source of TZ on input: user, site, date, utc, none; and storage in db: utc, date, none.
Formatter says what TZ to use on display: the one in the db (utc, date, none) or user/site (converted from whatever is stored in the db).

This way my site in the one example above can maintain present functionality by setting the formatter to formatter display timezone in db, not convert to user/site. If you run through the present options and how they change per site timezone there are lots of alternatives, but input&storage / display should make reproducing them simple and actually clear.

The latest patch, #48, didn't work at all for me. It changed the dates in the DB, but when I edit a node the dates on the edit page don't match what's displayed on the node. When I change the dates back and save, the node displays the wrong dates again. (I'm working at UTC-4.)

@Ken_g6 > Did you run the database updates after applying the patch ?

due to #53

I'm sure I applied DB updates, because I forgot to back up my (dev, not prod) database before I did, so I needed a fresh DB dump to undo the patch. I eventually had to revert to 2.7 to get dates working.

Just ran into this. Hrm.

The update hook needs to be run in a batch process (see the &$sandbox parts of hook_update_N). There are potentially a lot of records that could have this update applied, so for large sites this update hook could break them.

Also this assumes the fields are stored in sql storage without any kind of check to confirm what $field['storage']['type'] actually is, or that the field data table actually exists. Also this doesn't fix any revision table data.

Also the first part of the code could probably just use field_read_fields() instead of manually querying the database for field_config.

Rerolled.

Note: there were a few minor adjustments:

• date.field.inc / date_field_load() - fixed wrapping of the first comment.
• date.install / date_update_7005() - fixed the docbook comment; renamed the update script to 7200, per the hook_update_N() documentation. Also made several fixes per the Drupal coding standards.
• date.module / date_formatter_process() - updated to adjust to changes made in #1885270: UTC time zone handling shows UTC, not converted, date/time.
• date_timezone.test / DateTimezoneTestCase / dateForm() - wrapped the comment and minor coding standards fixes.

This results in the following errors when I run "drush test-run DateTimezoneTestCase":

• Starting test DateTimezoneTestCase. [ok]
• Timezone & Granularity 1023 passes, 2 fails, 0 exceptions, and 395 debug messages
• Test DateTimezoneTestCase->dateMultiValueForm() failed: Found the correct date for a datetime field using minute granularity with date timezone handling. in date/tests/date_timezone.test on line 145 [error]
• Test DateTimezoneTestCase->dateMultiValueForm() failed: Found the correct date for a datetime field using minute granularity with date timezone handling. in date/tests/date_timezone.test on line 145

The patch in #59 does not resolve the issues that Dave Reid mentioned in #58, it was an attempt at a quick reroll.

This version adds a db_update() call for the "field_revision . $field_name" table.

PS thanks to michellecox for testing the patch in #48 and realizing that the revision values weren't updated at all.

Despite the tests failing, #61 did fix the problem with my local testing where #48 did not due to it not handling revisions. Leaving at needs work because the tests need fixing but the patch does solve the problem.

Patch in #61 works for me as well.

Patch #61 has also resolved the issue for me.

Patch #61 works for me too. Can we fix the testing?

The patch isn't working for me unfortunately, but I have a sneaking suspicion that it's because the field in question is under the control of a Feature module...if that's the case, is there some way I can "unlock" this field to allow for the update to run on this field? I applied the patch, ran update db, everything went well, but in the DB, the blob on this field's config still has UTC as timezone_db. Thanks for any help.

Looking closer at the blob and the update_7200 code, it looks like it may be because i have the field set to tz_handling of "site" and the update only affected fields with "date" as the setting. Sorry if this is a dumb question, but why does the patch not update fields that use the Site's timezone and only those using Date's? Posting that blob here just in case that's mildly helpful.

a:8:{s:12:"entity_types";a:0:{}s:17:"field_permissions";a:1:{s:4:"type";s:1:"2";}s:12:"foreign keys";a:0:{}s:7:"indexes";a:0:{}s:8:"settings";a:7:{s:11:"granularity";a:6:{s:5:"month";s:5:"month";s:3:"day";s:3:"day";s:4:"hour";s:4:"hour";s:6:"minute";s:6:"minute";s:4:"year";s:4:"year";s:6:"second";i:0;}s:11:"tz_handling";s:4:"site";s:11:"timezone_db";s:3:"UTC";s:13:"cache_enabled";i:0;s:11:"cache_count";s:1:"4";s:6:"repeat";i:0;s:6:"todate";s:0:"";}s:12:"translatable";s:1:"0";s:7:"storage";a:5:{s:4:"type";s:17:"field_sql_storage";s:8:"settings";a:0:{}s:6:"module";s:17:"field_sql_storage";s:6:"active";s:1:"1";s:7:"details";a:1:{s:3:"sql";a:2:{s:18:"FIELD_LOAD_CURRENT";a:1:{s:32:"field_data_field_promo_date_time";a:1:{s:5:"value";s:27:"field_promo_date_time_value";}}s:19:"FIELD_LOAD_REVISION";a:1:{s:36:"field_revision_field_promo_date_time";a:1:{s:5:"value";s:27:"field_promo_date_time_value";}}}}}s:2:"id";s:3:"230";}

Updating issue summary

Updated the issue's list of remaining tasks.

I believe I've found a small issue with the patch in 61.

In date_formatter_process, two date objects are created and passed along to hook_date_formatter_dates_alter: one that is supposed to repesent the storage of the date in the database (the ['db']['object'] part of the array), and one that represents the localized date for display (the ['local']['object']). If I have a date field with a timezone specified that is different than the timezone it is going to be displayed in, the 'db' and 'local' objects should reflect that: with different times and timezones. However, after the db object is placed in the array, it is reused to calculate the local object, and it gets its timezone set to the display timezone. Since these are references to the same object, the db object gets contaminated with the display timezone.

The quick fix is simply to clone the date object before using it for the local date display. I will update this ticket with a new patch, but it needs a reroll first so it will take me some time.

Patch, as mentioned above.

Tackling the broken tests.

Took a stab at using a multi-pass update.

+++ b/date.install
@@ -204,3 +204,96 @@ function date_update_7004() {
+      if ($config['settings']['tz_handling'] == 'date') {
+        $field_name = $record['field_name'];
...
+  $query2 = db_select('field_data_' . $field_name, 'fd', array('fetch' => PDO::FETCH_ASSOC));

In the update hook, the patch assumes there is at least one date field where the tz_handling == 'date'. However, this is not necessarily the case. The will cause the update to result in an exception:
SQLSTATE[42S02]: Base table or view not found: 1146 Table '[..].field_data_' doesn't exist

Updating to account for the possibility that no fields need to be updated.

Major thanks to everyone working on this!! Usage stats show about 400k sites using the D7 version of Date and I can only imagine there's a whole lot of sites stuck using an insecure Date 2.7 until this is fixed.

@videographics, I hope by "insecure" you mean "broken" rather than "insecure"? :-O

@kenwest, I'd probably rest easier if it was only broken.

Unfortunately, insecure = insecure: SA-CONTRIB-2014-073- Date - Cross Site Scripting (XSS)

Lets try to get this to RTBC.

Does this need work to handle the problem raised by idebr in comment #81?

I believe #82 handles the issue raised in #81?

There's a "} else {" in the last patch that needs to be fixed.

Addressing the incorrectly formatted else.

... or I could not be an idiot? (Hopefully this works?)

patch #93 works for me: date timezone is correctly stored in timezone_db.

Batch field conversion did not triggered issues, but mine is not significant use case because I had no fields to convert (I've started from scratch just a new site...).

In field display visualization stored date timezone is converted to default time zone (user specific or site default) as expected. An OT question: If I had wanted to show original date timezone in visualization (regardless of default timezone, without any conversion), I would have to create a custom formatter for date field?

Thank you very much

Big thank you for the patch.
tested #93 for my application. New date entries are stored and displayed correctly. Batch field conversion changed all existing dates. For example: if my date was 07/28/15 6:30pm CDT. It became - 07/28/15 1:30pm CDT when displayed on nodes. Not a problem for new dates, but it's an issue for existing entries.

This update hook is giving us this error message:

Base table or view not found: 1146 Table '.field_data_' doesn't exist

The error happens when the query in this line is executed:

$query2 = db_select('field_data_' . $field_name, 'fd', array('fetch' => PDO::FETCH_ASSOC));

$field_name is empty, so it looks for a table {field_data_}, which doesn't exist.

Later, we get a divide-by-zero error here:

    $sandbox['#finished'] = ($sandbox['global_location'] / $sandbox['total']);

I think the problem is sites where there are no fields to update.

The attached patch fixes the issue, though there may be a better way to do this.

Alternatively it should just fail early if there's nothing to do.

Patch #97 fixes the issue on my side.
Currently it can't be applied to 7.x-2.9, so I'm obliged to currently use the DEV branch ...

Does this patch can be flagged as "Reviwed & tested by the community".

This is the same patch #97 ported to the current version 7.x-2.9.

Hope it helps.

If you have tested a patch that you did not make and it works, you can mark it RTBC.

If you want to upload a patch and not have the automatic tester test it, end the filename with "-do-not-test.patch".

Re-uploading patch in #97.

Sorry about that.

Just noting that the patch in #102 did apply to 7.x-2.9 but the patch in #97 applies fine with no errors.

RTBC for #101

#97 needs reroll

Re-roll of #97.

I applied #97 to the latest dev and it seemed to work initially, but the problem came back today -- the date is displayed on the frontend as Feb 15 2016, even though it is Feb 16 2016 on the node-edit screen. The date field has "day" granularity, so it doesn't have a "Use site's timezone" setting or anything like that. The displayed date is incorrect for both logged-in and anonymous users.

I believe the troublesome event was initially created as unpublished under date-2.9, and when it got published today under patched 2.x-dev, the date was off. My client's customer questioned it, I saw the problem with my own eyes... and when I cleared the cache the problem went away. So, now I can't test any other fixes as I can't reproduce the problem myself to begin with.

Not sure if this is relevant, but when I ran the database update after installing patched 2.x-dev, it said there were no date fields needing to be fixed.

Would this be a problem with having the "cache dates" checkbox checked for the field? I usually check it but to be honest I don't even know what it does.

BTW, in light of the statement that the D7 version of this module is not really maintained anymore...

Due to work on getting Date into Drupal 8 core, the D7 version is currently being minimally maintained. The maintainers will not actively work on features or bugs, but will check any issues that make it to RTBC status and apply the associated patches when appropriate.

The workaround we have gone with is to use date field for sorting only, and have a separate text field for displaying dates. It's not ideal but at least it is predictable. Hopefully this is a helpful idea for others struggling with this issue.

Rerolled.

The patch needs to clear entity caches, and the appropriate field caches too.

For what it's worth I also think that this is creating weirdness with newly saved items. I am finding some strange behaviour where the item stored in UTC is never really output properly as the site's timezone. I don't know if this patch would resolve that but I can test it out.

same as @ShaneOnABike

Rerolled.

So, one test failure, plus reports of ongoing problems :-(

@arzuga and @ShaneOnABike: Any update on what you're seeing? Are you running the latest patch or are you seeing problems with timezone and hoping the patch will solve it? Thanks.

Anyone else have any updates on how the patch(es) are working out for you?

Reroll.

I am pretty sure that the proposed resolution in the issue summary, wiz.:

1. The correct behavior for SAVING these fields, as indicated in https://www.drupal.org/node/1455578, is NOT to convert dates (before they are saved in the database)
2. The correct behavior for DISPLAYING these fields, again as indicated in https://www.drupal.org/node/1455578 is to convert dates to whatever the user or site timezone is set to

is wrong.

The page that describes the required behaviour is indeed https://www.drupal.org/node/1455578 - but this page does not say not to convert dates before they are saved in the datebase.
It describes 5 different options for time zone handling, and say that shall be converted upon saving if option 1 or 3 is chosen, and not converted if option 4 or 5 is choosen – with option 2 correctly being described as broken beyond repair.

This one needs work indeed.

This one needs to be fixed before the 2.11 release. Adding parent issue.

Removing this from the 2.11 release plan as that release is almost ready, while this needs some substantial work.

Sorry folks :(

Sorry folks :(

That's OK. I agree that this have to wait until later.

I've tried to fix it and it is horribly complex. Not breaking legacy installations looks impossible because some installations seem to depend on the present behavior. It may be that to fix this, we need to move to 7.x-3.x to make a clean break with all the (IMHO wrong) assumptions about handling of TZ embedded in 7.x-2.x.

Posting this in case it's useful for someone else. Please note that the attached patch is not related to previous patches, use at your own risk.
In our case we have a date field that uses "Date's time zone", it seems to store the time and timezone just fine but it's converting the time on display which should not happen according to the documentation (see below). In this case we chose to skip date_timezone_set in date_formatter_process for fields using "Date's time zone".
Using Date "7.x-2.x" revision "090d02e" with a couple patches, one of which is date-n998076-82.patch.
Many thanks to @gcb for his help on this.

https://www.drupal.org/node/767182

Date's time zone - With this option enabled, the date field adds a select box to explicitly specify the time zone for the date entered. When the date is saved to the database, it is not converted & the time zone information is saved with the date. When retrieved from the database, no conversion is performed and the date is displayed exactly as entered.

I think we'll need to start with expanded test coverage that documents how it all should work, and then fix bugs that show up.

That does sound like a good idea. In the meantime, this is a re-roll of #121.

Thanks @Liam.

As per comment #127, a major overhaul of Date's time zone handling is now underway in a separate branch 7.x-3.x. For details, see parent issue: #3012904: Plan for overhaul of time zone handling.

Proposed resolution
The correct behavior for SAVING these fields, as indicated in https://www.drupal.org/node/1455578, is NOT to convert dates (before they are saved in the database)
The correct behavior for DISPLAYING these fields, again as indicated in https://www.drupal.org/node/1455578 is to convert dates to whatever the user or site timezone is set to

Note for Dates (as opposed to date & time)

A date from one timezone can always be presented as two different dates if rendered in another timezone. It's simply impossible to apply any timezone logic to dates, it is guaranteed to be wrong sometimes. It should always be rendered exactly how it was entered.

The would be some information to gain from saving the entered timezone, but that would just help to narrow down the approx time the date represents

i.e 6/3/2018 PST is sometime tonight or tomorrow for us in AEST, but that's all the timezone can indicate.

I didn't review the patch (#130) but this doesn't resolve the issue with date timestamp datestamp field type, [Date (Unix timestamp)].

And based on the issue summary, if this is what the patch attempts to do, it will not resolve the issue.

i.e. actual example

If a user saves this 20km down the road in NSW, AU, the database saves a timestamp that translates to 00:00:00 GMT+11:00. If I save it in QLD, it's 00:00:00 GMT+10:00.

Using a particular date of 6/3/2018, if they enter it, it shows correctly to them, but to me it is 7/3/2018. If they view the one I save, that appears fine for me, it's also out by a day, 5/3/2018 for them.

It's clearly being rendered based on the users timezone, but even if saved as 00:00:00 LOCAL TIMEZONE, and even if rendered using the sites timezone, it still will produce inconstant results.

Here is a reroll which does not touch the tests. It has a few failures with UTC and date timezone handling. The tests at a glance look reasonable, so that suggests that the patch needs work.

In my twig template for Events I get the correct time when I render:
{{ content }}

or

{{ content.field_date_start }}

which is:
28/04/2019 - 1:11

(the 1.11 was entered in content as 1.11pm)

But I want to display the time separately from the date; so I put this in the twig:
{{ node.field_date_start.value|date("g:ia") }}

which displays as:
3:11am

This is off by 14hours. I'm assuming it has something to do with time zone?

But I don't want the time to change if people are entering content or viewing content in different time zones.
Example - someone creates content in Melbourne, Australia and someone else views it in London, England - the time should display the same regardless.

How do I enter that into a twig?

@auxiliaryjoel,
are you using a twig template with Drupal 7?

Hi gisle no I'm using twigs with Drupal 8.

@auxiliaryjoel wrote:

Hi gisle no I'm using twigs with Drupal 8.

Then you're in the wrong place. This is the queue for issues with the 7.x-2.x branch of the contributed Date module.

In Drupal 8, Date is in core and it looks like everything, including timezone handling, has been rewritten from scratch.

OK no problems thanks gisle, I will continue my search

*gulp*

Here is a reroll based on 7.x-2.x (#3150217b).

Here is a reroll based on 7.x-2.12.

#2898235: Date's Time Zone Handling doesn't keep time/zone static has overlapped with this one, so I'm resurrecting this Very Very Olde issue.

The extended test coverage from #2898235 was committed separately, so now we can focus on the actual bug. This combines the changes from #2898235 with the changes from #142.

I think we need to do a few things here:

• Provide a global option to control the logic, to avoid breaking existing sites.
• Make the update script optional, so sites don't have to do it and thus avoid inadvertently breaking their sites.
• Add test coverage for the update script.

WIP test coverage for the update script, and more comments.

The more I think on it the more I believe that the update script needs to be replaced with Drush commands, and maybe a UI for updating fields one at a time. We have ten years of usage on this module, with an API change eight years ago that caused massive inconsistencies in the data, and trying to automatically fix this data is a daunting undertaking. We'll need to do testing with a dev copy of the d.o site, and there are larger sites out there than d.o.

#149 Thank you for being cautious.

What this means is:
* Patch #145 resolves the problem.
* There are data problems on existing sites due to the inconsistency on how data was handled.
* More work is needed for fixing the existing inconsistent data.

Next steps:

• Document what the "date" timezone option means.
• Improve the UI around field timezone options, make the implications of each option more clear.
• Work out additional scenarios that might cause the field settings to be incorrect.
• Additional test coverage.
• Drush command to update data from UTC to the correct timezone.
• Test coverage for the Drush command.
• UI tool for updating data from UTC to the correct timezone.
• Test coverage for the UI tool.

I updated the Remaining Tasks on the issue summary.

Here is a reroll based on 7.x-2.14.

I created a MR with the patch from #145 (#153 was against a tagged release). I'll try to get familiar with the issue and see where or how I can help.

Running into #3251102: DateFieldTestBase::setup() fail setting up user/permission. Not sure why.

The test failures are probably because of #3194156: Patches and Merge Requests lead to different test results.

OK, all of the above brought the patch from #145 to a newer version that applies against the latest and also fixed some of the tests errors reported in the #3251102: DateFieldTestBase::setup() fail setting up user/permission issue.

We're in a position now to keep on working on the remaining items.

Rerolled after #3251102 was committed.

I swear I've done this before ;-)

The above fail can be fixed with this: https://git.drupalcode.org/project/date/-/merge_requests/16/diffs#7d300a...

I can work on it further tomorrow.

Are we still aiming to get all the remaining tasks in this issue? I’m happy to help to the best of my ability.

I've already committed the dependencies fix from the other issue. The test failure comes from a new test in DateUpdatesTestCase, one of the pages doesn't load as expected for some reason, though it is still returning a 200 HTTP status.

I think the todo list is reasonable?

Yup, sounds sensible. I’ll try to fix the test and work on some of the tasks.

Might ping you again if I need help or guidance.

@DamienMcKenna - I fixed the test that was failing, rebased the branch with the latest changes from 7.x-2.x, and also addressed, I think, the first two remaining tasks:

* Document what the "date" timezone option actually means.
* Improve the UI around field timezone options, make the implications of each option more clear.

I've done everything via the MR instead of patches. If you want to see a patch version of it just see this: https://git.drupalcode.org/project/date/-/merge_requests/16.diff

You might want to review the wording as I'm not fully familiar with the module's inner details so I may have missed something.

I've also got a question about the remaining tasks, namely the drush command and the UI tool for updating data. Isn't this what the update hook does? If that's the case, do we really need the command and the UI tool? Once somebody applies this update, then all the dates should be "correct", right?

Let me know your thoughts.

The latest merge request changes in patch format.

Here's the interdiff between 161 and 166.

This is a good step forwards for the UI.

Reviewing the changes, I think the wording here needs some refinement:

+  return array(
+    'options' => $options,
+    'description' => t("
+      - Site's time zone: Stores the date in UTC format and displays it using the site's time zone.
+      - Date's time zone: Stores and displays the date exactly as entered.
+      - User's time zone: Stores the date in UTC format and displays it using the user's time zone.
+      - UTC: Stores the date in UTC format and displays it in UTC format.
+      - No time zone conversion: Stores and display the date exactly as entered.
+    "),
+  );

The wording suggests that no timezone conversion was the same as the "Date's timezone" option, I believe there are limited scenarios where there's a difference.

I think it would be worth indicating which of these is the way Drupal 8+ store data.

Nitpick: the word is "timezone", not "time zone".

Not sure why, but MR and patch do not run the same amount of tests, among them, the new one.

The MR

11:28:16 Tests to be run:
11:28:16  - Date API (DateApiTestCase)
11:28:16  - Date API unit tests (DateApiUnitTestCase)
11:28:16  - Date Form test (DateFormTestCase)
11:28:16  - Date Now (DateNowUnitTestCase)
11:28:16  - Date Repeat Form (DateRepeatFormTestCase)
11:28:16  - Date Repeat (DateRepeatTestCase)
11:28:16  - Date Tools (DateToolsTestCase)

The patch

08:56:02 Tests to be run:
08:56:02  - Date All Day unit (DateAllDayUiTestCase)
08:56:02  - Date API (DateApiTestCase)
08:56:02  - Date API unit tests (DateApiUnitTestCase)
08:56:02  - Date Entity Metadata Wrappers (DateEmwTestCase)
08:56:02  - Date Field (DateFieldTestCase)
08:56:02  - Date Form test (DateFormTestCase)
08:56:02  - Date Migration (DateMigrateTestCase)
08:56:02  - Date Now (DateNowUnitTestCase)
08:56:02  - Date Popup (DatePopupFieldTestCase)
08:56:02  - Date Validation (DatePopupValidationTestCase)
08:56:02  - Date Views - Popup Test (DatePopupWithViewsTestCase)
08:56:02  - Date Repeat Field (DateRepeatFieldTestCase)
08:56:02  - Date Repeat Form (DateRepeatFormTestCase)
08:56:02  - Date Repeat (DateRepeatTestCase)
08:56:02  - Timezone & Granularity (DateTimezoneTestCase)
08:56:02  - Date Tools (DateToolsTestCase)
08:56:02  - Field UI (DateUiTestCase)
08:56:02  - Date Validation (DateValidationTestCase)
08:56:02  - Date views pager skipping test (ViewsPagerTestCase)
08:56:02  - Date updates (DateUpdatesTestCase)
08:56:02  - Date Views - Filter Test (DateViewsFilterTestCase)

Uploading patch format of latest changes to try to debug differences in CI run.

From slack conversation on March 6th 2023 (summarized):
- Fran: ...I have a question about the test that was created called DateUpdatesTestCase. It assumes that the module is still at the previous version, before applying the update hook, but that is not the case. At the time of loading/running the test, the update 7201 is already applied, so we cannot test the before and after, because there is no before in this case.
- Damien McKenna: Yeah, I realized that would be an issue when I wrote the test, which is why I ignored the schema version and manually executed the update script. That said, the update script (and the test) needs to be removed and replaced with a Drush command and/or admin page to manually process the fields.

Just chiming in that I will need to pull @fjgarlin away for some initiative related work for a little while, so if someone wants to attempt to resolve the items in #174 (or if there is a simpler solution to make it testable) that would be really helpful.

Note that the latest changes are in the MR: https://git.drupalcode.org/project/date/-/merge_requests/16
Attaching a patch matching the current state of the MR too.

Also see #171 as the tests that are run in the MR are a smaller set than the ones run in the patch.

Is this the same issue (exists with date 2.14 and 7.x-2.x-dev): when trying to add a node - I get both a node and a php error (using 7.4.33) - the same error
Although the error message says "invalid datetime format" I have found that the real problem is entering null for field_tags - and that if I put in any field_tags than the node can be created and the error is not thrown, so I am leaving it open in core. The problem is in not accepting a null or blank field, even though it is not required.

PDOException: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: 'autocreate' for column `uslowestprices_0`.`field_data_field_tags`.`field_tags_tid` at row 1: INSERT INTO {field_data_field_tags} (entity_type, entity_id, revision_id, bundle, delta, language, field_tags_tid) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6); Array ( [:db_insert_placeholder_0] => node [:db_insert_placeholder_1] => 77 [:db_insert_placeholder_2] => 77 [:db_insert_placeholder_3] => page [:db_insert_placeholder_4] => 0 [:db_insert_placeholder_5] => und [:db_insert_placeholder_6] => autocreate ) in field_sql_storage_field_storage_write() (line 622 of /data/disk/fast1/static/d771/modules/field/modules/field_sql_storage/field_sql_storage.module).

Is it worth considering removing the update hook and drush command altogether into a follow-up? Ie:

1. Without the update dates entered before 7.27 would be no worse off
2. Events created that are that old probably exact time matters little by now
3. Workarounds would then exist because of the new options available

It would make the merge easier right?

See the first part of this MR https://git.drupalcode.org/project/drupalorg/-/merge_requests/385/diffs done for #3164818: Timezone issue with community events.

Excerpt:

      // Capture current timezone.
      $script_tz = date_default_timezone_get();
      // Set timezone of the DB to calculate timestamps.
      date_default_timezone_set($event_dates['timezone_db']);      
      $timestamp1 = !empty($event_dates['value']) ? strtotime($event_dates['value']) : NULL;
      // Once the timestamp is correct, use the timezone in the field to render the date.
      $date1 = format_date($timestamp1, 'medium', '', $event_dates['timezone']);
      ...
      // Restore script timezone.
      date_default_timezone_set($script_tz);

Dates are stored using the database's timezone, so we should use that one before any call to strtotime. Once we have the correct timestamp, then we can calculate the rendering with the field's timezone.

Note sure if the above will be relevant to the final fix but just wanted to give some additional context of how we are "bandaiding" d.org