No offset value for repeating dates [#1440184]

If a node is saved with a repeating date, the database stores "0" as the offset value. Non-repeating dates correctly store the offset of the selected timezone.

I'm currently using the offset value in SQL to correct for timezones - maybe I should be doing this differently anyway?

Here's some sample SQL from an "events" content type, used to determine the number of events taking place in each month, in case anyone can suggest a better method.

 SELECT DATE_FORMAT(TIMESTAMPADD(SECOND,field_event_date_offset,STR_TO_DATE(field_event_date_value, '%Y-%m-%dT%T')), '%m') AS month, COUNT(MONTH(TIMESTAMPADD(SECOND,field_event_date_offset,STR_TO_DATE(field_event_date_value, '%Y-%m-%dT%T')))) as count
            FROM {node} node LEFT JOIN {field_data_field_event_date} field_data_field_event_date ON node.nid = field_data_field_event_date.entity_id
            WHERE node.type = 'event_content_type' AND node.status = 1 AND YEAR(TIMESTAMPADD(SECOND,field_event_date_offset,STR_TO_DATE(field_event_date_value, '%Y-%m-%dT%T'))) = :display_year
            GROUP BY month ORDER BY month ASC

Comment	File	Size	Author
#2	repeater problems.png	24.19 KB	kpaxman

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Comment #2

kpaxman CreditAttribution: kpaxman commented 17 April 2012 at 19:24

Status:

Postponed (maintainer needs more info)

» Active

File	Size
repeater problems.png	24.19 KB

Sorry, let me try again. I can confirm this is still happening with the current dev build.

We use a custom-written PHP block to display summaries of the number of events by year, and then by month within the year. We found that events happening late at night at the end of the month were appearing in the summaries as part of the following month. We later determined that this was because the times weren't being adjusted for our offset for GMT.

We adjusted the code to use the field_event_date_offset value to adjust the stored time for our time zone. This worked for "normal" events, but not for "repeated" events. Investigation of the database shows that "normal" events store an offset value, but "repeated" events just store "0".

Attached is a screen shot showing what happens with repeated events on our site. Note that the event repeats twice in April and 3 times in May, but the block (which uses the SQL from my previous comment) shows there being 1 April event and 4 May events, because it is unable to correct for the time zone offset because the repeated event stores "0" as the field_event_date_offset value.

What we believe should happen is that "repeated" events should store the same offset value that "normal" events store.

However, maybe using the offset value is the wrong way to do this, and there's a way to get the correct local time using SQL?

Comment #3

kpaxman CreditAttribution: kpaxman commented 26 February 2014 at 15:40

Issue summary:

View changes

Here's an attempt to further simplify what's happening, in the hopes that there can be some movement on this issue.

Steps to recreate:

Create a content type with a date field that allows repeating dates
Create 2 pieces of content, one that has a repeating date, and one that does not
Check the database to see what has been written, for example, for a field named event_date run the query "SELECT field_event_date_value, field_event_date_offset, field_event_date_rrule FROM field_data_field_event_date"
Note that the date_offset is "0" for events that repeat, but properly set for events that do not.