Rowboat: compare unix timestamp field with date problem

433 Posts

michelle84 Reply #1, 12 years, 7 months ago

Hi! I'm trying to use Rowboat to do a search in the mxcalendars table to display events which match a search term. This is working, but I only want to show events that are in the future. I tried al kinds of variations of:

[[!Rowboat?
   &table=`modx_mxcalendars_events`
   &tpl=`search.calender.list.item`
   &columns=`{"id":"","title":"","description":"","startdate":"","enddate":""}`
   &limit=`10`
   &where=`{"title:LIKE":"%event%","AND:startdate:>=":"[[!today]]"}`
   &sortBy=`startdate`
]]

And the snippet today is:

<?php
return time();

When I add startdate to the where filter no results are displayed anymore. Without it, it works. No errors in the error log.

Maybe the problem is that the 'startdate' field in the mxcalendar table is an INT field, with unix timestamp values like this one: 1395564300 (23-3-2014 09:45:00). How can I compare this the right way?

And a second question: how do I sort by startdate, this being a unix timestamp?

--
EDIT:
I turned on debug mode for Rowboat and I notice that no results are returned when I add the if startdate is greater than today where clause, but when I change '>=' to '!=' it does work. Somehow the dates can't be compared apparently. I thought this was the way to do this, especially because this syntax is also advised for getResources and I found out that the publishedon, createdon etc fields are also INT fields in the database with a unix timestamp, so the situation is the same. I really hope someone can help me out!

MODX Revo 2.2.10
Rowboat 1.1.0

--
EDIT 2:
I tested Rowboat with the site_content table and the publishedon field instead. Also not working with '>='. And I now find other forum posts of people who can't get this operator to work. Is that the problem, does Rowboat not support this kind of comparison? [ed. note: michelle84 last edited this post 12 years, 7 months ago.]

http://www.vierkante-meter.nl

5,888 Posts

Bruno17 Reply #2, 12 years, 7 months ago

does migxLoopCollection work?

[[!migxLoopCollection?
   &packageName=`mxcalendars`
   &classname=`mxCalendarEvents`
   &tpl=`search.calender.list.item`
   &limit=`10`
   &where=`{"title:LIKE":"%event%","AND:startdate:>=":"[[!today]]"}`
   &sortConfig=`[{"sortby":"startdate"}]`
]]

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

433 Posts

michelle84 Reply #3, 12 years, 7 months ago

Hi Bruno, thanks for your suggestion! Hadn't thought of using migxLoopCollection.
I just tested and unfortunately it doesn't work with the startdate where clause.. If I leave it out it does work...

http://www.vierkante-meter.nl

5,888 Posts

Bruno17 Reply #4, 12 years, 7 months ago

not sure, if this does make a difference:

&where=`{"title:LIKE":"%event%","AND:startdate:>=":[[!today]]}`

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

5,888 Posts

Bruno17 Reply #5, 12 years, 7 months ago

the first one should work. See:
http://www.revo222.webcmsolutions.de/75/mxcalendar.html

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

24,544 Posts

BobRay Reply #6, 12 years, 7 months ago

If startdate is a date field and is being retrieved with get(), it may come back as a formatted time string rather than a timestamp. This may be relevant: http://bobsguides.com/blog.html/2013/09/18/displaying-modx-date-fields/.

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

433 Posts

michelle84 Reply #7, 12 years, 7 months ago

Thanks Bruno and BobRay for helping me!
Bruno, if the first one works for you I must be doing something wrong. I'll try again and let you know what happens.
Bob: the startdate is a INT(20) field and has a timestamp in it... but maybe it is converted somehow.. I'll check that out too! Thanks!

http://www.vierkante-meter.nl

5,888 Posts

Bruno17 Reply #8, 12 years, 7 months ago

in the migxLoopCollection - snippet there is

//$c->prepare();echo $c->toSql();

when you uncomment it, what do you get?
And does that mysql-query work, when you try it with phpMyAdmin?

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

24,544 Posts

BobRay Reply #9, 12 years, 7 months ago

Quote from: michelle84 at Feb 14, 2014, 01:30 PM

Thanks Bruno and BobRay for helping me!
Bob: the startdate is a INT(20) field and has a timestamp in it... but maybe it is converted somehow.. I'll check that out too! Thanks!

That looks like it will return a timestamp, but it may depend on the type in the schema or whether the get() method has been overridden. If you can display startdate with a tag, it might tell you.

It's also possible that your today snippet is not being parsed in time to be used in the tag. Calling it uncached delays the processing, but of course you need it uncached. Since the outer snippet is also uncached, it should be OK, but I've learned not to take anything for granted when it comes to tag processing.

One option might be to have your uncached today snippet set a placeholder and use that placeholder (cached) in the tag.

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

433 Posts

michelle84 Reply #10, 12 years, 7 months ago

I didn't have time to look at this issue the last couple of days, but back to it now. Unfortunately I haven't been able to resolve this yet.

I first tested again with Bruno's first suggestion for migxLoopCollection, but there are still no results.
When I uncomment the line from the snippet I get this:

SELECT `mxCalendarEvents`.`id`, `mxCalendarEvents`.`title`, `mxCalendarEvents`.`description`, `mxCalendarEvents`.`content`, `mxCalendarEvents`.`categoryid`, `mxCalendarEvents`.`link`, `mxCalendarEvents`.`linkrel`, `mxCalendarEvents`.`linktarget`, `mxCalendarEvents`.`location_name`, `mxCalendarEvents`.`location_address`, `mxCalendarEvents`.`map`, `mxCalendarEvents`.`allday`, `mxCalendarEvents`.`startdate`, `mxCalendarEvents`.`enddate`, `mxCalendarEvents`.`repeating`, `mxCalendarEvents`.`repeattype`, `mxCalendarEvents`.`repeaton`, `mxCalendarEvents`.`repeatfrequency`, `mxCalendarEvents`.`repeatenddate`, `mxCalendarEvents`.`repeatdates`, `mxCalendarEvents`.`source`, `mxCalendarEvents`.`feeds_id`, `mxCalendarEvents`.`feeds_uid`, `mxCalendarEvents`.`lastedit`, `mxCalendarEvents`.`context`, `mxCalendarEvents`.`calendar_id`, `mxCalendarEvents`.`form_chunk`, `mxCalendarEvents`.`createdon`, `mxCalendarEvents`.`createdby`, `mxCalendarEvents`.`editedon`, `mxCalendarEvents`.`editedby`, `mxCalendarEvents`.`active` FROM `modx_mxcalendars_events` AS `mxCalendarEvents` WHERE ( `mxCalendarEvents`.`title` LIKE '%event%' AND `mxCalendarEvents`.`startdate` >= 1392822558 ) ORDER BY startdate ASC

When I use the placeholder [[+startdate]] in the row snippet (and leave out the 'where' clause that's causing problems) it outputs things like 1390725900. Is that what you meant Bob?

I also tried to have the uncached today snippet set a placeholder and use that. This is also not working, no results.. And the sql is the same as above.

I have no idea what's wrong! I also verified that there are results in the future with the word 'event' in the title so there should be results. Hope you notice anything strange about the query!

EDIT:
Forgot to answer your last question Bruno, when I try this SQL in phpmyadmin it does return the right results! [ed. note: michelle84 last edited this post 12 years, 7 months ago.]

http://www.vierkante-meter.nl