I'm still going bananas trying to do something that seems simple, but clearly isn't: Using a content type with a Date CCK type, I want to make a block that lists the events that occur between the beginning of the current calendar day, and the end of the current calendar day tonight at midnight, local time.

I've tried just about every damn combination of "today" and "now" imaginable, but the generated SQL as displayed in the Block preview in the Views UI admin page inevitably thinks that I _really_ want the events between now and 24 hours from now, as well:

SELECT node.nid AS nid,
   node.title AS node_title,
   node_data_field_event_start.field_event_start_value AS node_data_field_event_start_field_event_start_value
 FROM node node 
 INNER JOIN term_node term_node ON node.vid = term_node.vid
 LEFT JOIN content_type_hillel_calendar_event node_data_field_event_start ON node.vid = node_data_field_event_start.vid
 WHERE ((node.type in ('hillel_calendar_event')) AND (term_node.tid = 41))
    AND ((DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(node_data_field_event_start.field_event_start_value), 'UTC', 'America/Los_Angeles'), '%Y-%m-%d\T%H') >= '2009-04-04<b>T17</b>' AND DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(node_data_field_event_start.field_event_start_value), 'UTC', 'America/Los_Angeles'), '%Y-%m-%d\T%H') <= '2009-04-05<b>T17</b>'))
   ORDER BY node_data_field_event_start_field_event_start_value DESC

Note the items in bold (T17). I am specifying "today 12:00AM" and "tomorrow 12:00AM". But it's 5:50PM right now. This is significant, because it turns out that the hour in the query gets pulled from the hour in local time.

I'm not sure if this is a bug or not, but truly, it is annoying as all hell.

I pulled the 6x-2.x from CVS earlier today, and am currently using PHP 5.2.9, date_php4 now deactivated. Results were the same running 5.1.x on CentOS 5 with date_php4 active.

How can I do this? CanI do this?

CommentFileSizeAuthor
#2 424006.patch1.04 KBTorenware

Comments

Torenware’s picture

Torenware commented

In general, does date_api support strings that strtotime() would accept and handle correctly, or if it doesn't: what subset will it support?

Torenware’s picture

Torenware commented
Assigned: Unassigned » Torenware
Category: support » bug
Status: Active » Needs review
StatusFileSize
new 424006.patch1.04 KB

OK, it looks like it is indeed a bug, and I have a fix for it. Patch is attached, made against the 6.x-2.x branch.

Problem was the use of date_modify() to play around with the string "now", in the hope that the remaining string would be a valid relative string for date_modify(). Often, this was not so.

Solution is to let date_create() do what it does best, and let it use strtotime() style strings to indicate any relative date string these functions handle. Using 'today 12AM' and 'tomorrow 12AM' as my strings, now the following SQL is generated (at 8:30PM local time):

SELECT node.nid AS nid,
   node.title AS node_title,
   node_data_field_event_start.field_event_start_value AS node_data_field_event_start_field_event_start_value
 FROM node node 
 INNER JOIN term_node term_node ON node.vid = term_node.vid
 LEFT JOIN content_type_hillel_calendar_event node_data_field_event_start ON node.vid = node_data_field_event_start.vid
 WHERE ((node.type in ('hillel_calendar_event')) AND (term_node.tid = 41))
    AND ((DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(node_data_field_event_start.field_event_start_value), 'UTC', 'America/Los_Angeles'), '%Y-%m-%d\T%H') >= '2009-04-04T00' AND DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(node_data_field_event_start.field_event_start_value), 'UTC', 'America/Los_Angeles'), '%Y-%m-%d\T%H') <= '2009-04-05T00'))
   ORDER BY node_data_field_event_start_field_event_start_value DESC

This looks right to me.

pdcarto’s picture

pdcarto commented

I patched my copy of date_api_filter_handler.inc (from Date 6.x-2.x-dev 2009-04-01) by hand, flushed cache, and updated the date filter in my view, trying both "now" and "today 12AM".

I got no change - the where clause still is missing from the query.

Torenware’s picture

Torenware commented

I've seen the absence of the WHERE clause as well, but this patch does not fix that problem. But I'm pretty sure that a recent fix of Karen's will fix that.

I pulled from the DRUPAL-6--2 branch on 3 April. I'm not sure if there have been fixes between 1 April and 3 April that are relevant to your problem, but there may be.

karens’s picture

karens commented
Status: Needs review » Needs work

This won't work in PHP4. There are lots of nice things that the native date_create will do that we can't replicate reliably in PHP4. So fixing this in a way that will work in PHP4 will take some thought. So I'm going to play around with your patch to see if I can find a PHP4-safe solution.

karens’s picture

karens commented

And there was an issue somewhere about the problem of defining what 'Now' means, because there are times when you want it to mean 'midnight today' and times when you want it to mean 'right this second' and maybe times when you want it to mean 'this month' or 'this year'. I've been digging through the issue queue and I can't find that other issue (it was a pretty old one). If it pops up, one of these is a duplicate.

karens’s picture

karens commented
Status: Needs work » Fixed

OK, I added a switch, so in versions >= PHP 5.2 we pass the option directly to date_create() for super flexibility like in your patch, earlier versions get clunkier options, although I did switch to using strtotime there for a little more flexibility. I also added an example to the text for the PHP5.2+ version that indicates you can use '12AM' to set it to the beginning of the day.

Status: Fixed » Closed (fixed)

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