When using UTC time zone handling on a date field, the date/time is not converted to the site's (or user's) time zone when viewing the data. Either the documentation or the code is wrong here. As per the documentation regarding time zone handling for the date field:

When entering data into the field, the data entered is assumed to be in UTC time zone. When the data is saved to the database, it is converted to UTC (e.g. no conversion necessary). When retrieved from the database, the data is converted to the Site's time zone for annonymous users or the User's time zone for logged in users when User-configurable time zones is enabled.

(see http://drupal.org/node/1455578 ).

The data is saved correctly. However, when retrieving the data, date.module loads the time zone to display the data in as follows (around line 200-210):

<?php
function date_formatter_process(...) {
  ...
 
$timezone = isset($item['timezone']) ? $item['timezone'] : '';
 
$timezone = date_get_timezone($field['settings']['tz_handling'], $timezone);
  ...
?>

In this case, $item['timezone'] is set to UTC, but also date_get_timezone() will force a time zone of 'UTC' because of the handling setting. That's correct when getting the input and saving the value, but when displaying the value again it should be converted to the time zone date_default_timezone() returns -- if the documentation is correct, that is.

Files: 
CommentFileSizeAuthor
#14 date-wrong_default_tz-1885270-14.patch475 bytesJvE
FAILED: [[SimpleTest]]: [MySQL] 4,286 pass(es), 980 fail(s), and 129 exception(s).
[ View ]
#2 Screen Shot 2013-01-19 at 19.50.11.png45.58 KBStoraH
#1 view.png94.93 KBosman
#1 devel__field_date.png135 KBosman
#1 node_edit__field_date.png64.64 KBosman

Comments

Priority:Normal» Major
StatusFileSize
new64.64 KB
new135 KB
new94.93 KB

Marking this major, since the problem causes the Views display wrong content, especially when Calendar module's Day template is used, or in any view where Date is used as contextual filter, and granularity is set to Day.

I have a "Class" content-type with a field_date Date field. In the view, I use Calendar's view template, specifically the Day view.

All the listed entries' field_date timezone information is ignored. Which would cause displaying previous day's nodes to be displayed before the day's nodes.

For example:
9 Jan 2013 7:30pm event is displayed on 10 Jan
13 Jan 2013 4:15pm to 5:30pm event is displayed on 14 Jan (See attached screenshots for this one)
13 Jan 2013 6:30pm event is displayed on 14 Jan

Seems like the date value is saved correctly, node view displays the date correctly and respects date field's timezone settings.

However the Views cannot display the correct list of entries for the selected day.

This effects all the calendar views and normal table display views as well.

Is there any fix in progress? Or what it involves to correct it for the time being?

Thanks,

Possibly related issues:
#1693306: Repeated Calendar Entries after 6:59 p.m. show on the next day on a DAY view
#1260380: Events for times after 6pm show up on following day in Day view.

StatusFileSize
new45.58 KB

I ran in to the same problem. Did some testing and for me it's only when I use "Date (Unix timestamp)" I have the problem with the time zone.

My time zone is +1 and for example, when I save "Fri, 19 Jan 2013" as a Unix timestamp it saves to the DB as 1358550000 (2013-01-18 23:00:00).
It's saved as the day before at 23:00 wish I guess is right?
When using the date field as FILTER CRITERIA or a CONTEXTUAL FILTERS it runs the mysql function FROM_UNIXTIME() on the timestamp wish returns a value as 'YYYY-MM-DD HH:MM:SS' in this case 2013-01-18 23:00:00 and then DATE_FORMAT(2013-01-18 23:00:00), '%Y-%m-%d') and then compare it to 2013-01-19 which dosen't work. (I'm attaching the views query)

I'm gonna keep trying to get this to work and hope this info helps.

Same problem here using ISO dates and UTC handling. One would expect it to save as UTC (which it does) but for it to display based on the users timezone setting.

This field:

field_start_time (Array, 1 element)
    und (Array, 1 element)
        0 (Array, 4 elements)
            value (String, 19 characters ) 2013-01-22T12:10:29
            timezone (String, 3 characters ) UTC
            timezone_db (String, 3 characters ) UTC
            date_type (String, 4 characters ) date | (Callback) date();

With this users timezone:
timezone (String, 16 characters ) Europe/Stockholm

Should see Tuesday, January 22, 2013 - 13:10 but instead sees Tuesday, January 22, 2013 - 12:10.

Priority:Major» Critical

It seems to be screwed up in a lot of ways!

I now tried with ISO dates and sites timezone handling which is set to UTC. If saving the date as a user with +1 timezone it gets saved as:

field_test_date_2 (Array, 1 element)
    und (Array, 1 element)
        0 (Array, 4 elements)
            value (String, 19 characters ) 2013-01-23T22:15:00
            timezone (String, 16 characters ) Europe/Stockholm
            timezone_db (String, 3 characters ) UTC
            date_type (String, 4 characters ) date | (Callback) date();

Which is wrong according to the docs as this should be saved in the sites timezone (i.e. it should ignore the user). Re-saving it as a user with UTC timezone setting saves it as UTC:

field_test_date_2 (Array, 1 element)
    und (Array, 1 element)
        0 (Array, 4 elements)
            value (String, 19 characters ) 2013-01-23T23:15:00
            timezone (String, 3 characters ) UTC
            timezone_db (String, 3 characters ) UTC
            date_type (String, 4 characters ) date | (Callback) date();

The one that seems to work as expected is when using users timezone. It does the necessary conversions and saves it as UTC and any other user displaying it will see it in their timezone.

I am changing this to critical so that others will hopefully see it because this essentially renders major parts of using date unusable.

Here are some tests done with the standard date, iso dates and unix dates and using all the different forms of handling. The expected results (correct/incorrect) are based on the docs at http://drupal.org/node/1455578 with the exception that "2. Dates time zone" is expected no conversion. Incorrect results in bold. ?? means im not sure (for example non-conversion dont convert the times but do show the users timezone which is wrong in my opinion).

1. Sites TZ is UTC, user adding the node has TZ UTC, fields with their own TZ handling is UTC.

Viewing result for user with UTC timezone

date site: 2013-01-23 10:00:45 UTC
date date: 2013-01-23 10:00:45 UTC
date user: 2013-01-23 10:00:45 UTC
date utc: 2013-01-23 10:00:45 UTC
date non: 2013-01-23 10:00:45 UTC
iso site: 2013-01-23 10:00:45 UTC
iso date: 2013-01-23 10:00:45 UTC
iso user: 2013-01-23 10:00:45 UTC
iso utc: 2013-01-23 10:00:45 UTC
iso non: 2013-01-23 10:00:45 UTC
unix site: 2013-01-23 10:00:45 UTC
unix date: 2013-01-23 10:00:45 UTC
unix user: 2013-01-23 10:00:45 UTC
unix utc: 2013-01-23 10:00:45 UTC
unix non: 2013-01-23 10:00:45 UTC

Viewing result for user with +1 timezone

date site: 2013-01-23 11:00:45 Europe/Stockholm
date date: 2013-01-23 10:00:45 UTC
date user: 2013-01-23 11:00:45 Europe/Stockholm
date utc: 2013-01-23 10:00:45 UTC
date non: 2013-01-23 10:00:45 Europe/Stockholm ??
iso site: 2013-01-23 11:00:45 Europe/Stockholm
iso date: 2013-01-23 10:00:45 UTC
iso user: 2013-01-23 11:00:45 Europe/Stockholm
iso utc: 2013-01-23 10:00:45 UTC
iso non: 2013-01-23 10:00:45 Europe/Stockholm ??
unix site: 2013-01-23 11:00:45 Europe/Stockholm
unix date: 2013-01-23 10:00:45 UTC
unix user: 2013-01-23 11:00:45 Europe/Stockholm
unix utc: 2013-01-23 10:00:45 UTC
unix non: 2013-01-23 11:00:45 Europe/Stockholm

2. Sites TZ is UTC, user adding the node has TZ UTC, fields with their own TZ handling is Stockholm.

Viewing result for user with UTC timezone

date site: 2013-01-23 10:00:30 UTC
date date: 2013-01-23 10:00:30 Europe/Stockholm
date user: 2013-01-23 10:00:30 UTC
date utc: 2013-01-23 10:00:30 UTC
date non: 2013-01-23 10:00:30 UTC
iso site: 2013-01-23 10:00:30 UTC
iso date: 2013-01-23 10:00:30 Europe/Stockholm
iso user: 2013-01-23 10:00:30 UTC
iso utc: 2013-01-23 10:00:30 UTC
iso non: 2013-01-23 10:00:30 UTC
unix site: 2013-01-23 10:00:30 UTC
unix date: 2013-01-23 10:00:30 Europe/Stockholm
unix user: 2013-01-23 10:00:30 UTC
unix utc: 2013-01-23 10:00:30 UTC
unix non: 2013-01-23 10:00:30 UTC

Viewing result for user with +1 timezone

date site: 2013-01-23 11:00:30 Europe/Stockholm
date date: 2013-01-23 10:00:30 Europe/Stockholm
date user: 2013-01-23 11:00:30 Europe/Stockholm
date utc: 2013-01-23 10:00:30 UTC
date non: 2013-01-23 10:00:30 Europe/Stockholm ??
iso site: 2013-01-23 11:00:30 Europe/Stockholm
iso date: 2013-01-23 10:00:30 Europe/Stockholm
iso user: 2013-01-23 11:00:30 Europe/Stockholm
iso utc: 2013-01-23 10:00:30 UTC
iso non: 2013-01-23 10:00:30 Europe/Stockholm ??
unix site: 2013-01-23 11:00:30 Europe/Stockholm
unix date: 2013-01-23 10:00:30 Europe/Stockholm
unix user: 2013-01-23 11:00:30 Europe/Stockholm
unix utc: 2013-01-23 10:00:30 UTC
unix non: 2013-01-23 11:00:30 Europe/Stockholm

I will try a few more but I think its obvious that the docs are wrong for UTC and that unix non conversion is not working.

Ok, now it gets interesting! Site handling is actually assuming that the entered amount is the users timezone which is not what its supposed to do according to docs.

3. Sites TZ is UTC, user adding the node has TZ +1, fields with their own TZ handling is UTC. All dates were entered as 12:15.

Viewing result for user with UTC timezone

date site: 2013-01-23 11:15:15 UTC
date date: 2013-01-23 12:15:15 UTC
date user: 2013-01-23 11:15:15 UTC
date utc: 2013-01-23 12:15:15 UTC
date non: 2013-01-23 12:15:15 UTC ??
iso site: 2013-01-23 11:15:15 UTC
iso date: 2013-01-23 12:15:15 UTC
iso user: 2013-01-23 11:15:15 UTC
iso utc: 2013-01-23 12:15:15 UTC
iso non: 2013-01-23 12:15:15 UTC ??
unix site: 2013-01-23 11:15:15 UTC
unix date: 2013-01-23 12:15:15 UTC
unix user: 2013-01-23 11:15:15 UTC
unix utc: 2013-01-23 12:15:15 UTC
unix non: 2013-01-23 11:15:15 UTC

Viewing result for user with +1 timezone

date site: 2013-01-23 12:15:15 Europe/Stockholm
date date: 2013-01-23 12:15:15 UTC
date user: 2013-01-23 12:15:15 Europe/Stockholm
date utc: 2013-01-23 12:15:15 UTC
date non: 2013-01-23 12:15:15 Europe/Stockholm ??
iso site: 2013-01-23 12:15:15 Europe/Stockholm
iso date: 2013-01-23 12:15:15 UTC
iso user: 2013-01-23 12:15:15 Europe/Stockholm
iso utc: 2013-01-23 12:15:15 UTC
iso non: 2013-01-23 12:15:15 Europe/Stockholm
unix site: 2013-01-23 12:15:15 Europe/Stockholm
unix date: 2013-01-23 12:15:15 UTC
unix user: 2013-01-23 12:15:15 Europe/Stockholm
unix utc: 2013-01-23 12:15:15 UTC
unix non: 2013-01-23 12:15:15 Europe/Stockholm ??

Priority:Critical» Normal

This issue seems to be becoming a bit of a catch-all for various time zone conversion issues with Date. I assume that this isn't too helpful to the developer, there's simply too little overview.

May I suggest to stick this issue to the original problem, which involves time zone handling during the rendering (viewing only) of a date field when handling is set to UTC - which is either a documentation OR a coding error. Storage in that case seems to be correct.

Other distinct parts of the code that I can identify are:
- Timezone handling when saving an input value for date fields
- Timezone handling when viewing (rendering) a date field, with other settings than 'UTC' - this should assume saved values are as documented
- Timezone handling when building Views queries (especially regarding date boundaries; this too should assume saved values are as documented)

Different problems with different parts of the code should probably also be dealt with in separate issues. I don't have the time to investigate this much further myself - definitely not beyond the original issue - but I suggest first checking if values are saved correctly to the database (i.e. as documented) and then verify conversion for rendering/viewing and views queries.

As for the original problem: I myself don't think it's really a major problem. Considering only the rendering of UTC-handled fields, I almost assume the documentation is incorrect, but I would like some verification in that area. Therefore, resetting the priority to normal.

In my experience, date storage seems to be correct. Though I agree the documentation may need an update regarding Timezone handling.

However, Date's View integration is not respecting the stored date value's timezone information. Instead it treats the stored date value as it was in UTC. Thus, filtering "wrong" content, see my notes in comment #1.

IMHO, one solution could be extending Views integration to offer (perhaps it would be a relation) an option of which timezone should be taken in consideration: Site's, user's or date's...

I am not trying to divert to discussion and make it a feature request. But again IMHO, since the Date's View integration is not handling to timezone correctly, output becomes invalid hence useless. Hence the priority was tagged as major.

Please do correct me if I am missing something essential or obvious.

Thanks,

I understand your issue is major and I agree it breaks the Views integration of Date; it just looks a lot like it's a different issue than what's described in the opening post - a different component even, as I suppose that problem lies with the Views Filter or Argument (not Date Field). Hence I suggest opening a new bug report and/or feature request for the problem you're describing so it can be dealt with separately.

I managed to do "fix" for converting UTC to the site timezone with this code:

$tz = new DateTimeZone(date_default_timezone_get());
$offset = $tz->getOffset(new DateTime($item['value']));
$full_date_1 = new DateTime($item['value']);
$start_stamp = strtotime($item['value']) + $offset;

Hope that it helps.

But since I use also views on some parts of the website to display date and time, I am interested if any progress happened in Date and Views problem in this topic.

Imagine scenario.
content type with 20 field; initial data entry and user hits save. time saved to db correctly. Now when you edit newly created node, even though that time was saved to db correctly, now on the edit node page time is rendered in UTC.
Suppose that you edit other fields and hit save. Now the incorrect time is saved to the db! and if you repeat this step 10 times, for me, UK based, the db time is 10h different from the correct time! this surely is very very critical.

Status:Active» Needs review
StatusFileSize
new475 bytes
FAILED: [[SimpleTest]]: [MySQL] 4,286 pass(es), 980 fail(s), and 129 exception(s).
[ View ]

I found a gotcha that can probably explain some of the strange results.

At /admin/config/regional/settings you see the result of this code:

<?php
  $form
['timezone']['date_default_timezone'] = array(
   
'#type' => 'select',
   
'#title' => t('Default time zone'),
   
'#default_value' => variable_get('date_default_timezone', date_default_timezone_get()),
   
'#options' => $zones,
  );
?>

The date module uses the following code to get the timezone when using date_formatter_process() (for displaying):
<?php
function date_default_timezone($check_user = TRUE) {
  global
$user;
  if (
$check_user && variable_get('configurable_timezones', 1) && !empty($user->timezone)) {
    return
$user->timezone;
  }
  else {
   
$default = variable_get('date_default_timezone', '');
    return empty(
$default) ? 'UTC' : $default;
  }
}
?>

This means that if you've never saved that system settings form (and why would you, it's already showing the correct timezone) then the system variable 'date_default_timezone' will not be set.
This causes the date module to use UTC.

I've attached a patch that makes the Date module also use date_default_timezone_get() if the variable is empty and to only fall back on UTC when the function result is empty.

Status:Needs review» Needs work

The last submitted patch, 14: date-wrong_default_tz-1885270-14.patch, failed testing.