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):

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.

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

osman’s picture

Priority: Normal » Major
FileSize
64.64 KB
135 KB
94.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.

StoraH’s picture

FileSize
45.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.

7wonders’s picture

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.

7wonders’s picture

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.

7wonders’s picture

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

7wonders’s picture

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

7wonders’s picture

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.

7wonders’s picture

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 ??

haffmans’s picture

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.

osman’s picture

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,

haffmans’s picture

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.

milovan’s picture

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.

sylwester’s picture

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.

JvE’s picture

Status: Active » Needs review
FileSize
475 bytes

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:

  $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):

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.

Exploratus’s picture

Big problem for me, by views are shifting by 4 hours, for date fields where I dont want to display time. Causing unwanted changes.

JvE’s picture

Status: Needs work » Needs review

Status: Needs review » Needs work

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

mareksal’s picture

Hello,

I would like to suggest a patch for the issue.
Unfortunately since yesterday I've waited for a 'not a spammer' role.. with no result so far.
Could anyone speed up the process of 'verifying' me ;) ?

Cheers

Marek

mareksal’s picture

The problem is in the function date_get_timezone.
Once the timezone handling is set to UTC, the timezone returned is 'UTC' instead of (according to documentation), returning either site's timezone, or the user's timezone:

function date_get_timezone($handling, $timezone = '') {
       $timezone = !empty($timezone) ? $timezone : date_default_timezone();
       break;
     case 'utc':
       $timezone = 'UTC';
       break;
     default:
       $timezone = date_default_timezone();
   }

the case 'utc' should be merged with default

I would like to ask for testing the patch

mareksal’s picture

Status: Needs work » Needs review

Status: Needs review » Needs work

The last submitted patch, 20: date-fixForUtcShow-1885270-19.patch, failed testing.

Eduardo Alvarez’s picture

Version: 7.x-2.6 » 7.x-2.8

I'm still experimenting this issue on version 7.x-2.8

Date is saved into database in UTC format but when displaying Date module is not taking into account the site's timezone or user's timezone and is just printing the date in UTC format.

Example:

Date saved in database:
2014-10-16 12:00:00

Date in view mode on a UTC+2 website (CEST):
2014-10-16 12:00:00 (should be 2014-10-16 14:00:00)

Anyone else is experimenting the same?

Eduardo Alvarez’s picture

I’m assuming a website set up with UTC as time zone handling for the Date field used in the example, and the Region of the website set as UTC+2 region (Europe/Zurich)
I assume the following content nodes exist on the website

(please note that since the timezone handling mode is set to UTC the data must be entered in UTC format)
entity_type | Title | Date field
node | Test Date 1 | 2014-09-27 15:00 (Note summer time period selected, +2 in Zurich timezone)
node | Test Date 2 | 2014-11-21 15:00 (Note winter time period selected, +1 in Zurich timezone)

The date data stored on the database was as expected in the same format we introduced (UTC) so the database date field contains:
for Test Date 1 -> 2014-09-27 15:00:00
for Test Date 2 -> 2014-11-21 15:00:00

But there is a problem when viewing the date, since the date is not converted from UTC to the Site’s timezone/User’s timezone as explained in the Documentation, https://www.drupal.org/node/1455578 but displayed directly on UTC format.

Date field on view:
for Test Date 1 -> 2014-09-27 15:00:00
for Test Date 2 -> 2014-11-21 15:00:00

while it should be (for Europe/Zurich timezone):
for Test Date 1 -> 2014-09-27 17:00:00
for Test Date 2 -> 2014-11-21 16:00:00

The source of the issue is explained in the first post of this topic, and this patch tries to solve this problem. Basically what happens is that the tz_handling of the $field is ‘utc’ so when the DateObject is created it uses the timezone_db (utc) on the constructor and then converts it using the $timezone variable that takes its last value from $field[‘settings’][’tz_handling’] while it should take the value from the date_default_timezone()

This patch tries to solve that by checking if the tz_handling of the field is set and equals to utc to retain the value from date_default_timezone and not override it with the tz_handling setting.

Eduardo Alvarez’s picture

Status: Needs work » Needs review
azovsky’s picture

Experienced the same problem on a fresh site configuration.

But this was resolved by setting the time-zone for the field e.g.
/admin/structure/types/manage/[content_type]/fields/[field_date_NAME]
"Time zone handling" set as "site's time zone".

podarok’s picture

Version: 7.x-2.8 » 7.x-2.x-dev
Status: Needs review » Fixed

#24 commited. Thanks!

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.

DamienMcKenna’s picture

I believe this may conflict with the goals of #998076: Problem with timezone handling (caused by date_get_timezone_db returning only UTC).

FYI it also ought to have been improved to match the Drupal coding standards, but the offending lines will probably be removed by #998076 anyway.

Abbass’s picture

@podarok (#29) could you confirm that this patch is not conflicting with (https://www.drupal.org/node/998076) as #30 said? as You have approved both patches or anybody else it does not matter, I too see timezone handling miss behavior when selected date field settings is anything else than UTC, as this current patch (on this page) seems to only address timezone handling when the date field setting is UTC

philsward’s picture

I can only assume this did not get ported over to D8?

I'm struggling with some views date filters based on "Authored On" and looking at the timestamps, it appears as though they are pulling from the UTC, not the site date/timezone. It's making it impossible to show content that was "created yesterday".

Given the following query:

ELECT node_field_data.created AS node_field_data_created, node_field_data.nid AS nid
FROM
{node_field_data} node_field_data
INNER JOIN {node__field_lake_reference} node__field_lake_reference ON node_field_data.nid = node__field_lake_reference.entity_id AND node__field_lake_reference.deleted = '0'
WHERE ((node__field_lake_reference.field_lake_reference_target_id = '6')) AND ((node_field_data.status = '1') AND (node_field_data.type IN ('fishing_report')) AND ((node_field_data.created BETWEEN 1567702604-151200 AND 1567702604-64801)))
ORDER BY node_field_data_created DESC

Today is 9/5/2019. The filter is set to show everything authored yesterday which should be 9/4/2019. However the timestamp:

1567702604 Is equivalent to: 09/05/2019 @ 4:56pm (UTC) which translates to 09/05/2019 @ 10:56pm -600 (Chicago)

I guess the question is... are there any known issues regarding this problem on D8 I can follow?

Update: #2647292: Date/time Views filter tries strotime() relative to Unix epoch