You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@jackrabbit.apache.org by Jean-Baptiste Bellet <jb...@goojet.com> on 2008/06/30 18:11:08 UTC
Birthdate query
Hi,
I'm using jackrabbit and I'm trying to execute a query in order to
retrieve 'users', registered in JCR, who have a birthdate corresponding
to the current date.
For example if a user was born on june the 30th, 1992 I want to compare
the user's birthdate with june the 30th (and without the year wich is
not important in this case). I have tried a lot of queries, but without
success.
Any idea on how to perform such a query?
Thanks a lot,
jb
This one works:
//user[@birthDate = '1992-06-30T14:36:24.292+01:00']
These do not work:
//user[substring(@birthDate, 1, 1) = '1']
//user[substring(fn:string(@birthDate), 1, 1) = '1']
//user[concat(substring(@birthDate, 6, 2), substring(@birthDate, 9, 2))
= '0630']
//user[fn:month-from-dateTime(@birthDate) = 6]
//user[fn:month-from-dateTime(./@birthDate/value()) = 6]
Re: Birthdate query
Posted by Sébastien Launay <se...@anyware-tech.com>.
Hi Jean-Baptiste,
These basics operations are defined in XPath but not mandatory
in implementations of JCR 1.0, nor JCR 2.0.
The spec of JCR says:
XPath 2.0 forms the basis of the querying syntax in level 1. All
compliant repository implementations must support this search
syntax. However, implementations that use a relational database as
an underlying datastore will typically be limited in the range of
XPath queries that they can efficiently support.
I think, in Jackrabbit these type of operations are expensive because
all lucene term corresponding to the target property needed to be
fetched in order to compute the match in java (like jcr:like('%foo%bar%'))
whereas = or < or > operator are very efficient thanks to index sorting.
I do not know the road map as i am not part of the Jackrabbit team.
But, you still can fill an issue on jira [1] or try to implements this
on your
own and submit a patch which will be much welcome.
I think that specific date operations (like in many RDBMS [2]) must be
implemented for this instead of standard string operation.
And by changing the way the date properties are stored an efficient
implementation may be written.
[1] https://issues.apache.org/jira/browse/JCR
[2] http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
--
Sébastien
Jean-Baptiste Bellet a écrit :
> Hi Sébastien,
>
> Thanks for your response.
> So, what you cleary said is Jackrabbit isn't able to make some of this
> basics operations over the Date format. Do you know if this is on the
> road map for futur version ? Because this type of operation could be
> very usefull ... Not only for my case of course ! If someone has
> another solution ... :)
> Thanks to all the team.
>
> jb
>
> Sébastien Launay wrote:
>> Sébastien Launay a écrit :
>>> Hi JB,
>>>
>>> None of the functions you describre are currently supported by
>>> Jackrabbit.
>>> A date property is stored in the lucene index using ISO8601 format for
>>> using < and > operator efficiency.
>> My mistake, in the fact that the date property is stored in ISO8601.
>> Indeed these properties are stored in a fixed length string suitable
>> for indexing and sorting which makes more sense :) :
>> http://fisheye6.atlassian.com/browse/jackrabbit/trunk/jackrabbit-core/src/main/java/org/apache/jackrabbit/core/query/lucene/DateField.java?r=532464
>>
>>>
>>> The most efficient way IMHO is to have another date property which will
>>> contain the same the value but with a same fixed year.
>>> The drawback is that you must maintain the integrity of the two
>>> fields at
>>> each update.
>>>
>>> But i think the duplicity is worth the effort hence it will consume
>>> more disk.
>>>
>>> Note that you will need to use
>>> xs:datetime('XXXX-06-30T00:00:00.000+00:00')
>>> with operator >= and <.
>>>
>>> You can also use a string property with month and day concatenated
>>> ("06/30")
>>> but the query will not be so fine grained (users born on the same
>>> hour :)).
>>>
>>> Best regards,
>>>
>>> Sébastien.
>>>
>>> Jean-Baptiste Bellet a écrit :
>>>> Hi,
>>>>
>>>> I'm using jackrabbit and I'm trying to execute a query in order to
>>>> retrieve 'users', registered in JCR, who have a birthdate
>>>> corresponding to the current date.
>>>> For example if a user was born on june the 30th, 1992 I want to
>>>> compare the user's birthdate with june the 30th (and without the
>>>> year wich is not important in this case). I have tried a lot of
>>>> queries, but without success.
>>>> Any idea on how to perform such a query?
>>>> Thanks a lot,
>>>> jb
>>>>
>>>> This one works:
>>>> //user[@birthDate = '1992-06-30T14:36:24.292+01:00']
>>>> These do not work:
>>>> //user[substring(@birthDate, 1, 1) = '1']
>>>> //user[substring(fn:string(@birthDate), 1, 1) = '1']
>>>> //user[concat(substring(@birthDate, 6, 2),
>>>> substring(@birthDate, 9, 2)) = '0630']
>>>> //user[fn:month-from-dateTime(@birthDate) = 6]
>>>> //user[fn:month-from-dateTime(./@birthDate/value()) = 6]
--
Sébastien LAUNAY
Solutions Web & Ametys
ANYWARE TECHNOLOGIES
Tél : +33 (0)5 61 00 06 40
Fax : +33 (0)5 61 00 51 46
http://www.anyware-tech.com
http://www.ametys.fr http://www.ametys.org
Re: Birthdate query
Posted by Jean-Baptiste Bellet <jb...@goojet.com>.
Hi Sébastien,
Thanks for your response.
So, what you cleary said is Jackrabbit isn't able to make some of this
basics operations over the Date format. Do you know if this is on the
road map for futur version ? Because this type of operation could be
very usefull ... Not only for my case of course ! If someone has another
solution ... :)
Thanks to all the team.
jb
Sébastien Launay wrote:
> Sébastien Launay a écrit :
>> Hi JB,
>>
>> None of the functions you describre are currently supported by
>> Jackrabbit.
>> A date property is stored in the lucene index using ISO8601 format for
>> using < and > operator efficiency.
> My mistake, in the fact that the date property is stored in ISO8601.
> Indeed these properties are stored in a fixed length string suitable
> for indexing and sorting which makes more sense :) :
> http://fisheye6.atlassian.com/browse/jackrabbit/trunk/jackrabbit-core/src/main/java/org/apache/jackrabbit/core/query/lucene/DateField.java?r=532464
>
>>
>> The most efficient way IMHO is to have another date property which will
>> contain the same the value but with a same fixed year.
>> The drawback is that you must maintain the integrity of the two fields at
>> each update.
>>
>> But i think the duplicity is worth the effort hence it will consume
>> more disk.
>>
>> Note that you will need to use
>> xs:datetime('XXXX-06-30T00:00:00.000+00:00')
>> with operator >= and <.
>>
>> You can also use a string property with month and day concatenated
>> ("06/30")
>> but the query will not be so fine grained (users born on the same hour
>> :)).
>>
>> Best regards,
>>
>> Sébastien.
>>
>> Jean-Baptiste Bellet a écrit :
>>> Hi,
>>>
>>> I'm using jackrabbit and I'm trying to execute a query in order to
>>> retrieve 'users', registered in JCR, who have a birthdate
>>> corresponding to the current date.
>>> For example if a user was born on june the 30th, 1992 I want to
>>> compare the user's birthdate with june the 30th (and without the year
>>> wich is not important in this case). I have tried a lot of queries,
>>> but without success.
>>> Any idea on how to perform such a query?
>>> Thanks a lot,
>>> jb
>>>
>>> This one works:
>>> //user[@birthDate = '1992-06-30T14:36:24.292+01:00']
>>> These do not work:
>>> //user[substring(@birthDate, 1, 1) = '1']
>>> //user[substring(fn:string(@birthDate), 1, 1) = '1']
>>> //user[concat(substring(@birthDate, 6, 2), substring(@birthDate,
>>> 9, 2)) = '0630']
>>> //user[fn:month-from-dateTime(@birthDate) = 6]
>>> //user[fn:month-from-dateTime(./@birthDate/value()) = 6]
Re: Birthdate query
Posted by Sébastien Launay <se...@anyware-tech.com>.
Sébastien Launay a écrit :
> Hi JB,
>
> None of the functions you describre are currently supported by
> Jackrabbit.
> A date property is stored in the lucene index using ISO8601 format for
> using < and > operator efficiency.
My mistake, in the fact that the date property is stored in ISO8601.
Indeed these properties are stored in a fixed length string suitable
for indexing and sorting which makes more sense :) :
http://fisheye6.atlassian.com/browse/jackrabbit/trunk/jackrabbit-core/src/main/java/org/apache/jackrabbit/core/query/lucene/DateField.java?r=532464
>
> The most efficient way IMHO is to have another date property which will
> contain the same the value but with a same fixed year.
> The drawback is that you must maintain the integrity of the two fields at
> each update.
>
> But i think the duplicity is worth the effort hence it will consume
> more disk.
>
> Note that you will need to use
> xs:datetime('XXXX-06-30T00:00:00.000+00:00')
> with operator >= and <.
>
> You can also use a string property with month and day concatenated
> ("06/30")
> but the query will not be so fine grained (users born on the same hour
> :)).
>
> Best regards,
>
> Sébastien.
>
> Jean-Baptiste Bellet a écrit :
>> Hi,
>>
>> I'm using jackrabbit and I'm trying to execute a query in order to
>> retrieve 'users', registered in JCR, who have a birthdate
>> corresponding to the current date.
>> For example if a user was born on june the 30th, 1992 I want to
>> compare the user's birthdate with june the 30th (and without the year
>> wich is not important in this case). I have tried a lot of queries,
>> but without success.
>> Any idea on how to perform such a query?
>> Thanks a lot,
>> jb
>>
>> This one works:
>> //user[@birthDate = '1992-06-30T14:36:24.292+01:00']
>> These do not work:
>> //user[substring(@birthDate, 1, 1) = '1']
>> //user[substring(fn:string(@birthDate), 1, 1) = '1']
>> //user[concat(substring(@birthDate, 6, 2), substring(@birthDate,
>> 9, 2)) = '0630']
>> //user[fn:month-from-dateTime(@birthDate) = 6]
>> //user[fn:month-from-dateTime(./@birthDate/value()) = 6]
Re: Birthdate query
Posted by Sébastien Launay <se...@anyware-tech.com>.
Hi JB,
None of the functions you describre are currently supported by Jackrabbit.
A date property is stored in the lucene index using ISO8601 format for
using < and > operator efficiency.
The most efficient way IMHO is to have another date property which will
contain the same the value but with a same fixed year.
The drawback is that you must maintain the integrity of the two fields at
each update.
But i think the duplicity is worth the effort hence it will consume more
disk.
Note that you will need to use xs:datetime('XXXX-06-30T00:00:00.000+00:00')
with operator >= and <.
You can also use a string property with month and day concatenated ("06/30")
but the query will not be so fine grained (users born on the same hour :)).
Best regards,
Sébastien.
Jean-Baptiste Bellet a écrit :
> Hi,
>
> I'm using jackrabbit and I'm trying to execute a query in order to
> retrieve 'users', registered in JCR, who have a birthdate
> corresponding to the current date.
> For example if a user was born on june the 30th, 1992 I want to
> compare the user's birthdate with june the 30th (and without the year
> wich is not important in this case). I have tried a lot of queries,
> but without success.
> Any idea on how to perform such a query?
> Thanks a lot,
> jb
>
> This one works:
> //user[@birthDate = '1992-06-30T14:36:24.292+01:00']
> These do not work:
> //user[substring(@birthDate, 1, 1) = '1']
> //user[substring(fn:string(@birthDate), 1, 1) = '1']
> //user[concat(substring(@birthDate, 6, 2), substring(@birthDate,
> 9, 2)) = '0630']
> //user[fn:month-from-dateTime(@birthDate) = 6]
> //user[fn:month-from-dateTime(./@birthDate/value()) = 6]