Getting date query to work


(coys) #1

Hello, I have dates which are in the MM/dd/YYYY format and I indexed
them as
curl -XPUT 'http://localhost:9200/collectionName/startDate/_mapping' -
d '{
"indexName": {
"properties" : {
"calendar.startDate" : {"type":"date", "format":"MM/dd/YYYY" }
}
}
}
'curl -XPUT 'http://localhost:9200/collectionName/endDate/_mapping' -d
'{
"indexName": {
"properties" : {
"calendar.endDate" : {"type":"date", "format":"MM/dd/YYYY" }
}
}
}
'
Can I index a date in the MM/dd/YYYY format as described and I am
doing it the correct way? Or should I store it as YYYY-mm-dd?

Also, my query looks like this
{"must":
[
{ "bool":
{
"must": [ {
"range": {
"calendar.startDate": { "gte":
"7/11/2012" }
}
},
{
"range": {
"calendar.endDate": { "lte":
"8/11/2012" }
}
}
]
}
}
]
}
Right now I have this query but its not returning anything. Thanks!


(Igor Motov) #2

If I understand you correctly, you are indexing records with type
startDate, that contain fields calendar.startDate, then you are indexing
records with type endDate, that contain fields calendar.endDate, and then
you are issuing a query for a record that contains a startDate field
greater than or equal to 7/11/2012 and an endData field less than or equal
to 8/11/2012 at the same time. According to your mapping such record
cannot exist. So, I don't think I quite understand what you are trying
to achieve here. Could you give an example of your data and expected
results?

On Wednesday, July 11, 2012 12:44:26 PM UTC-4, coys wrote:

Hello, I have dates which are in the MM/dd/YYYY format and I indexed
them as
curl -XPUT 'http://localhost:9200/collectionName/startDate/_mapping' -
d '{
"indexName": {
"properties" : {
"calendar.startDate" : {"type":"date", "format":"MM/dd/YYYY" }
}
}
}
'curl -XPUT 'http://localhost:9200/collectionName/endDate/_mapping' -d
'{
"indexName": {
"properties" : {
"calendar.endDate" : {"type":"date", "format":"MM/dd/YYYY" }
}
}
}
'
Can I index a date in the MM/dd/YYYY format as described and I am
doing it the correct way? Or should I store it as YYYY-mm-dd?

Also, my query looks like this
{"must":
[
{ "bool":
{
"must": [ {
"range": {
"calendar.startDate": { "gte":
"7/11/2012" }
}
},
{
"range": {
"calendar.endDate": { "lte":
"8/11/2012" }
}
}
]
}
}
]
}
Right now I have this query but its not returning anything. Thanks!


(coys) #3

Hi Igor, every document that I create in the collection has a start
date and end date. I want to find a list of documents currently valid
-- eg valid documents say > startDate and < endDate
documents valid from 7/11/2012 to 8/11/2012
This was my initial mapping,
"calendar" : {
"dynamic" : "true",
"properties" : {
"endDate" : {
"type" : "string"
},
"startDate" : {
"type" : "string"
}
}
}
and to be able to search on date field I created these 2 mappings,
names of mappings startDate and endDate on calendar.startDate and
calendar.endDate
"startDate" : {
"properties" : {
"calendar.startDate" : {
"type" : "date",
"format" : "MM/dd/yyyy"
}
}
},
"endDate" : {
"properties" : {
"calendar.endDate" : {
"type" : "date",
"format" : "MM/dd/yyyy"
}
}
}


(Igor Motov) #4

Something like this https://gist.github.com/9d67c5c2b047a962dcf5 ?

On Wednesday, July 11, 2012 4:45:16 PM UTC-4, coys wrote:

Hi Igor, every document that I create in the collection has a start
date and end date. I want to find a list of documents currently valid
-- eg valid documents say > startDate and < endDate
documents valid from 7/11/2012 to 8/11/2012
This was my initial mapping,
"calendar" : {
"dynamic" : "true",
"properties" : {
"endDate" : {
"type" : "string"
},
"startDate" : {
"type" : "string"
}
}
}
and to be able to search on date field I created these 2 mappings,
names of mappings startDate and endDate on calendar.startDate and
calendar.endDate
"startDate" : {
"properties" : {
"calendar.startDate" : {
"type" : "date",
"format" : "MM/dd/yyyy"
}
}
},
"endDate" : {
"properties" : {
"calendar.endDate" : {
"type" : "date",
"format" : "MM/dd/yyyy"
}
}
}


(coys) #5

Hi Igor, thanks a lot for the example. However, when I try to re-map
the field as a date its giving me a mergemapper exception. I tried,
curl -XPUT 'http://localhost:9200/plansCollection/plans/_mapping' -d
'{
"plans": {
"properties" : {
"calendar" : {
"dynamic" : "true",
"properties" :{
"startDate" : {"type":"date", "format":"MM/dd/yyyy" },
"endDate" : {"type":"date", "format":"MM/dd/yyyy" }
}
}
}
}
}
'
where index name = plansCollection and index type is plans
It gave me {"error":"MergeMappingException[Merge failed with failures
{[mapper [calendar.startDate] of different type, current_type
[string], merged_type [date], mapper [calendar.endDate] of different
type, current_type [string], merged_type [date]]}]"
Is there a way I can fix the mapping now so that the above query would
work?

I also tried /plansCollection/planA/ .. but the date query would not
work with that. thanks!


(Igor Motov) #6

You cannot change type of a field once it's created. You will have to
delete the index or at least the mapping (type) that you need to change and
reindex your data.

On Wednesday, July 11, 2012 6:22:02 PM UTC-4, coys wrote:

Hi Igor, thanks a lot for the example. However, when I try to re-map
the field as a date its giving me a mergemapper exception. I tried,
curl -XPUT 'http://localhost:9200/plansCollection/plans/_mapping' -d
'{
"plans": {
"properties" : {
"calendar" : {
"dynamic" : "true",
"properties" :{
"startDate" : {"type":"date", "format":"MM/dd/yyyy" },
"endDate" : {"type":"date", "format":"MM/dd/yyyy" }
}
}
}
}
}
'
where index name = plansCollection and index type is plans
It gave me {"error":"MergeMappingException[Merge failed with failures
{[mapper [calendar.startDate] of different type, current_type
[string], merged_type [date], mapper [calendar.endDate] of different
type, current_type [string], merged_type [date]]}]"
Is there a way I can fix the mapping now so that the above query would
work?

I also tried /plansCollection/planA/ .. but the date query would not
work with that. thanks!


(coys) #7

Thanks a lot!


(system) #8