Scripted fields ISO Week


(Carlos Vega Moreno) #1

Hi, I would like to get the ISO week from the timestamp. I tried using Python but is not possible to import modules on the inline scripts (not sure why). Also, I tried with lucene expressions which would be something like this:

floor(((doc["timestamp"].getDayOfMonth() + (doc["timestamp"].getMonth() == 1 ? 31 : 0) + (doc["timestamp"].getMonth() == 2 ? 59 : 0) + (doc["timestamp"].getMonth() == 3 ? 90 : 0) + (doc["timestamp"].getMonth() == 4 ? 120: 0) + (doc["timestamp"].getMonth() == 5 ? 151: 0) + (doc["timestamp"].getMonth() == 6 ? 181: 0) + (doc["timestamp"].getMonth() == 7 ? 212: 0) + (doc["timestamp"].getMonth() == 8 ? 243: 0) + (doc["timestamp"].getMonth() == 9 ? 273: 0) + (doc["timestamp"].getMonth() == 10 ? 304: 0) + (doc["timestamp"].getMonth() == 11 ? 334: 0) + (doc["timestamp"].getMonth() >= 2 && (((doc["timestamp"].getYear() % 4 == 0) && (doc["timestamp"].getYear() % 100 != 0)) || (doc["timestamp"].getYear() % 400 == 0)) ? 1 : 0)) - ((doc["timestamp"].getDayOfMonth()+doc["timestamp"].getMonth()+doc["timestamp"].getYear()+floor(doc["timestamp"].getYear()/4))%7) + 11) / 7)

based on this article formula.

Is there any better way to do this? Thanks


(Carlos Vega Moreno) #2

I also tried different langs like groovy using this:

{"lang":"groovy", "script": "c=Calendar.getInstance(); c.setMinimalDaysInFirstWeek(4); c.setFirstDayOfWeek(Calendar.MONDAY); c.setTime(new Date((long) doc['fecha']*1000)); c.get(Calendar.WEEK_OF_YEAR);" }

but it didn't work :frowning:


(Lee Drengenberg) #3

Hi Carlos,
I tried your first solution and it did appear to work for me (I didn't closely check the date to ISO week calculation). But of course Kibana is sending all that in the query which doesn't seem very optimal.

I would think a better solution would to get the ISO week added as the data is ingested into Elasticsearch if that's possible. Are you using logstash, or a beat?

Lee


(Carlos Vega Moreno) #4

Hi, thanks for your answer. Of course it works (not as precise as the formula, lacks some checks), and of course, is not optimal at all. Actually, the right solution would be this:
floor( (doc['timestamp'].date.dayOfYear - doc['timestamp'].date.dayOfWeek + 10) / 7) = week of the year.
with some other checks too for some weird cases. But these expressions are only valid in elastic 5 alpha

Yes, processing this during log collection would be the right solution, but what if you have a whole year of data with dozens of different metrics? Change production data is more dangerous than apply some scripts to numeric fields on sporadic demand. The mere existence of this scripted fields is owing to the human nature, which is not able to predict the query workflow whims of the final user. Hence these scripted fields. Would be perfect if they were able to run client side.

But there is one part of my original question that have to be answered yet, why is there a python support if is not possible to import modules? or is it? how? Would be nice to have all of this well documented with some examples. Why there is support for groovy scripts but not for some modules such as Calendar with is format method that uses SimpleDateFormat to convert dates to different formats such as WEEK_OF_YEAR_FIELD ? </end of this long question>

Greetings and thanks again,

Carlos Vega


(Glen Smith) #5

I'll move this to Elasticsearch, as is appropriate for the prevailing question.


(Paulo Henrique Ph) #6

Hi @Carlos_Vega_Moreno,

Im wondering if you came up with a solution for that...

I have the very same user case here.

Cheers


(system) #7