Sorry... I am lost here.

I have a content type (courses) with a date field for an event.
I am trying to show a list of nodes with a date >=now

I have all of this in place but nothing displays. If I reverse it to be <=now everything displays even the dates outside of the range.

Here is my SQL:

SELECT node.nid AS nid,
   node_data_field_course_date.field_course_date_value AS node_data_field_course_date_field_course_date_value,
   node.type AS node_type,
   node_data_field_course_date.field_course_date_value2 AS node_data_field_course_date_field_course_date_value2,
   node_data_field_course_date.nid AS node_data_field_course_date_nid,
   node.title AS node_title
 FROM node node 
 LEFT JOIN content_type_courses node_data_field_course_date ON node.vid = node_data_field_course_date.vid
 WHERE (node.type in ('courses')) AND (node.status <> 0) AND (DATE_FORMAT(FROM_UNIXTIME(node_data_field_course_date.field_course_date_value), '%Y-%m-%d') >= '2008-07-28')
   ORDER BY node_data_field_course_date_field_course_date_value ASC

I am lost... can you export a working filter based on datefiled showing only dates after today. I am in a bind. Thanks!

Comments

mroswell’s picture

mroswell commented

There's a nice date range views filter for 5. I hope it upgrades.

neofactor’s picture

neofactor commented

Date feature Under Drupal 5 with original views? That will be nice. Perhaps I jumped to Views2 and D6 too soon.

merlinofchaos’s picture

merlinofchaos commented

The SQL that view creates is correct for what you're trying to accomplish. Note this part:

(DATE_FORMAT(FROM_UNIXTIME(node_data_field_course_date.field_course_date_value), '%Y-%m-%d') >= '2008-07-28')

That should be pulling up nodes whose course date is >= 7-28-2008.

I would doublecheck your data, because you appear to be getting the view you've asked for.

neofactor’s picture

neofactor commented

I know... that is what is driving me crazy.
I will run some more tests.

{time passes}

I just played with filters against node updated date and it worked as expected. SO the issue MUST be related t dateffeld... I will do some manual query tests to see what this CCK field is using for dates. Must be messed up somehow.

{Further investigation}
Looks like node changed is formatted like this: 1215382015
And my DateField is formatted like this: 2008-10-18T00:00:00

When I run a modify verison of the query:

SELECT DATE_FORMAT(FROM_UNIXTIME(field_course_date_value), '%Y-%m-%d')
FROM content_type_courses

I get the following date returned no matter what the date is in the database: 1969-12-31

Reading about this: 1969-12-31 and the function FROM_UNIXTIME() indicates the function incorrectly applies localtime correction.

This is either an issue of Views not knowing how to handle the date.. r datefield not being in the database right.

Thoughts form some serious UNIX MySQL core people?

Please reference this cross post... I think the bug is on datefield and not in Views.
http://drupal.org/node/288374

lacitpo’s picture

lacitpo commented

I am having this same problem

merlinofchaos’s picture

merlinofchaos commented
Project: Views (for Drupal 7) » Date
Component: node data » Miscellaneous

Let's go back to the source. I don't know enough about date.module to provide any more help.

karens’s picture

karens commented
Status: Active » Closed (duplicate)

This was a bug in the beta3 version which has long since been fixed. I also just committed a complete rework of the filter code to use date widgets as selectors. There is another issue about date filters, so check that issue out for the current status -- http://drupal.org/node/287437.