Solr: org.apache.solr.common.SolrException: Invalid Date String:

Question

I am new to solr and this is my first attempt at indexing solr data, I am getting the following exception while indexing,

org.apache.solr.common.SolrException: Invalid Date String:'2011-01-07' at org.apache.solr.schema.DateField.parseMath(DateField.java:165) at org.apache.solr.schema.TrieDateField.createField(TrieDateField.java:169) at org.apache.solr.schema.SchemaField.createField(SchemaField.java:98) at org.apache.solr.update.DocumentBuilder.addField(DocumentBuilder.java:204) at org.apache.solr.update.DocumentBuilder.toDocument(DocumentBuilder.java:277)

I understand from reading some articles that Solr stores time only in UTC, this is the query i am trying to index,

Select id,text,'language',links,tweetType,source,location, bio,url,utcOffset,timeZone,frenCnt,createdAt,createdOnGMT,createdOnServerTime,follCnt,favCnt,totStatusCnt,usrCrtDate,humanSentiment,replied,replyMsg,classified,locationDetail, geonameid,country,continent,placeLongitude,placeLatitude,listedCnt,hashtag,mentions,senderInfScr, createdOnGMTDate,DATE_FORMAT(CONVERT_TZ(createdOnGMTDate,'+00:00','+05:30'),'%Y-%m-%d') as IST,DATE_FORMAT(CONVERT_TZ(createdOnGMTDate,'+00:00','+01:00'),'%Y-%m-%d') as ECT,DATE_FORMAT(CONVERT_TZ(createdOnGMTDate,'+00:00','+02:00'),'%Y-%m-%d') as EET,DATE_FORMAT(CONVERT_TZ(createdOnGMTDate,'+00:00','+03:30'),'%Y-%m-%d') as MET,sign(classified) as sentiment from

Why i am doing this timezone conversion is because i need to group results by the user timezone. How can i achieve this?

Regards, Rohit

Shermanshermie

Answer 1

Date faceting is entirely driven by query params, so if we index your events using the "true" time that they happend at (formatted as a string in UTC) you can then select your date ranges using whatever timezone offset is specified by your user at query time as a UTC offset.

facet.range = dateField
facet.range.start = 2011-01-01T00:00:00Z+${useroffset}MINUTES
facet.range.gap = +1DAY

This would return result in the users timezone and there is actually no need to timezone conversion the query and indexing that column separately.

Regards, Rohit

Credit For Answer: Chris Hostetter (Solr User Group )

Shermanshermie

Why do you answer (and greet) yourself when I gave the correct answer to your exception? Moreover, this answer is entirely unrelated to the exception in your own question! – Optimistic

I see you copied Chris Hostetter's answer in solr-user here, but that answer was to a different question... – Optimistic

"Why i am doing this timezone conversion is because i need to group results by the user timezone. How can i achieve this?" the second part of my question was related to this.. sorry was not clear may be – Shermanshermie

Answer 2

Solr dates must be in the form 1995-12-31T23:59:59Z. You're only giving the date part, but not the time.

See the DateField javadocs for more details.

Optimistic

Thanks for the response, actually what we need to achieve is see group by results based on dates like, 2011-01-01 23 2011-01-02 14 2011-01-03 40 2011-01-04 10 Now the records in my table run into millions, grouping the result based on UTC date would not produce the right result since the result should be grouped on users timezone. Is there anyway we can achieve this in Solr? – Shermanshermie

@rohitgu: you need to give Solr dates in this format for indexing. There is no other way. So shift everything to UTC, and when you get back results shift back to whatever timezone is needed. – Optimistic