Querying record contained in RMP collection between 2 dates

[somesh.m...@gmail.com]

Hi,

I have a collection with the following columns: class_name, class_admin, class_start_date and class_end_date.

Need to query this collection in a process output variable freemarker script to read the class_name for which the current date falls between the class_start_date and class_end_date. For this I wrote the following code (please see below), but it does not seem to work. Does anybody have a working sample of this genre of RMP Collection queries? (Just FYI - I tried load_object as well, that does not seem to work as well)

_____________________________________________________________________________

<#assign todaysdate = get_date(now(),"yyyy-MM-dd HH:mm:ss")>

<#assign my_pattern>
{"${todaysdate}": {"$gte":"class_start_date"}, "${todaysdate}": {"$lt":"class_end_date"}}
</#assign>
<#list list_objects(my_pattern,"my_collection",0,1) as x>
<#assign class_name=x.class_name>
</#list>
${class_name}

_____________________________________________________________________________

Thanks!

[Gareth Thiveux]

Hi Somesh,

You were not far from having it right, you've just inverted the keys and values in the pattern JSON. The field from the collection must be the left-hand side argument/key :

<#assign todaysdate = get_date(now(),"yyyy-MM-dd HH:mm:ss")>

<#assign my_pattern>

    {"class_start_date":{"$lt":"${todaysdate}"}, "class_end_date":{"$gte":"${todaysdate}"}}
</#assign>

<#assign class_name = load_object(my_pattern,"my_collection").class_name>
${class_name}

Anyway, if you want to compare dates, you shouldn't use the string format of dates but timestamps.

Regards,

Gareth THIVEUX

Pre-Sales Consultant / Consultant Avant-Vente

 Fujitsu RunMyProcess

22 rue Chauchat, 75009 Paris - France (GMT+1)

website : Fujitsu RunMyProcess

     

Fujitsu RunMyProcess user? Please add a review on GoogleApps Marketplace

Afin de contribuer au respect de l'environnement, merci de n'imprimer ce message qu'en cas de nécessité.
Be environmentally friendly: do not print this email unless it is entirely necessary.

2013/8/12 <somesh.m...@gmail.com>

--
Fujitsu - RunMyProcess
---
You received this message because you are subscribed to the Google Groups "RunMyProcess Support Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to supportforum...@runmyprocess.com.
To post to this group, send email to suppor...@runmyprocess.com.
Visit this group at http://groups.google.com/a/runmyprocess.com/group/supportforum/.
To view this discussion on the web visit https://groups.google.com/a/runmyprocess.com/d/msgid/supportforum/d9dfdcd8-26cb-4cf3-878f-a3e60c5b6f38%40runmyprocess.com?hl=en.
For more options, visit https://groups.google.com/a/runmyprocess.com/groups/opt_out.

[somesh.m...@gmail.com]

Thanks for catching that Gareth.

For the other suggestion that you have to replace the string comparison with an actual timestamp, how do I proceed with that? I replaced the the get_date and now I am using the following snippet to generate todaysdate (without touching anything else in my script) and the query seems to be working fine:

<#assign todaysdate = now("yyyy-MM-dd HH:mm:ss")>

Thanks!

[Gareth Thiveux]

Hi,

In order to get current date/time under the timestamp format you shall use timestamp("SECOND")
<#assign todays_ts = timestamp("SECOND")>

But class_start_date and class_end_date would need to be stored as timestamps too within the collection to allow the comparison.


Regards,

Gareth THIVEUX

Pre-Sales Consultant / Consultant Avant-Vente

 Fujitsu RunMyProcess

22 rue Chauchat, 75009 Paris - France (GMT+1)

website : Fujitsu RunMyProcess

     

Fujitsu RunMyProcess user? Please add a review on GoogleApps Marketplace

Afin de contribuer au respect de l'environnement, merci de n'imprimer ce message qu'en cas de nécessité.
Be environmentally friendly: do not print this email unless it is entirely necessary.

2013/8/13 <somesh.m...@gmail.com>

Thanks!

--
Fujitsu - RunMyProcess
---
You received this message because you are subscribed to the Google Groups "RunMyProcess Support Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to supportforum...@runmyprocess.com.
To post to this group, send email to suppor...@runmyprocess.com.
Visit this group at http://groups.google.com/a/runmyprocess.com/group/supportforum/.

To view this discussion on the web visit https://groups.google.com/a/runmyprocess.com/d/msgid/supportforum/9d4eae78-23f4-429e-9b72-eddd89466d6e%40runmyprocess.com?hl=en.

[somesh.m...@gmail.com]

Thanks Gareth, I updated my implementation per your suggestions, which is the right approach!

Appreciate your help again!