In the case that a user is in EST (America/New_York) and saves an event as

Jan 14, 2010 10:00P

it will store in the database as

UTC 2010-01-15T03:00:00

Since the SQL query when pulling events for Jan 14 look for events that (when appropriately converted and formatted) equal 2010-01-14, this will not include the aforementioned event.

Is there something that I'm doing wrong? If not, I'm thinking that my best option would be to modify the created query to pull all events for the given date +- 12 hrs and then filter it out in PHP.

I may have passed this over while searching for existing issues (or it might be a calendar or views issue). If so, please let me know!

Thanks in advance,
Tim

Comments

timtrinidad’s picture

timtrinidad commented

Has no one else come across this problem?

timtrinidad’s picture

timtrinidad commented

In case my initial post wasn't clear, here's a summary:

The calendar pulls events for UTC midnight to UTC midnight on the requested day instead of from midnight to midnight on the user's timezone. Is there a fix, or something that I'm missing?

arlinsandbulte’s picture

arlinsandbulte commented

I am not an expert, but I am guessing you first need to convert your requested date into UTC. Then query the database. Then convert the result to the user's time for display.
I assume the date api would be of great use to you: http://drupal.org/node/287128

timtrinidad’s picture

timtrinidad commented

I am using the Date API (which is why I posted it to the Date API support queue).

The problem is that I'm trying to use the date/views adapters to select events for display in calendar. Date has an included argument that can select events from the database based on the supplied URL.

If I supply 2010-01-18, it will retrieve all events that happen on January 18, 2010 00:00-23:59 UTC rather than those that happen on January 18, 2010 00:00-23:59 [user's timezone].

timtrinidad’s picture

timtrinidad commented
Title: Database SELECT doesn't account for timezone » Date Views Query Ignores Timezone
Version: 6.x-2.3 » 6.x-2.x-dev

(Changing title in hopes of better response)

timtrinidad’s picture

timtrinidad commented
Title: Date Views Query Ignores Timezone » date_api_argument_handler query_fields not setting local_timezone

More info: The date_api_argument_handler has the following date_handler:

[date_handler] => date_sql_handler Object
        (
            [db_type] => mysqli
            [date_type] => datetime
            [db_timezone] => UTC
            [local_timezone] => America/New_York
            [db_timezone_field] => 
            [local_timezone_field] => 
            [offset_field] => 
            [granularity] => month
        )

but each of the fields in date_api_argument_handler->query_fields has the following date_handler:

[date_handler] => date_sql_handler Object
                        (
                            [db_type] => mysqli
                            [date_type] => date
                            [db_timezone] => UTC
                            [local_timezone] => UTC
                            [db_timezone_field] => 
                            [local_timezone_field] => 
                            [offset_field] => 
                            [granularity] => month
                        )

Note that the local_timezone on the fields is not the same as the main local_timezone. I'm still trying to debug, but anyone have any ideas why this is happening?

timtrinidad’s picture

timtrinidad commented
Status: Active » Closed (fixed)

I feel like an idiot. If anyone is tearing their hear out at this problem, you're most likely a module developer and have hook_date_api_fields defined.

I copied some code that returned an array with $values['tz_handling'] = 'utc'. Changing it to $values['tz_handling'] = '' (empty string) worked for me.