Several date math questions

Elastic Stack Elasticsearch
1

I am having trouble with some date math. I read through the docs but couldn't get things working right.

How does “/d” work? Does "now-1M/d" get me the start of this month, or should I use "now-1M" to get the start of this month? (Docs didn't help me much here.)

I want to get hits beginning on the first of the current month.

To get hits from the beginning of today, I would just mirror the correct query with "d" instead of "M"?

I am also having trouble just getting hits from yesterday.

Would this (partial) query get hits from the beginning of yesterday until the end of yesterday?

"filter": {
			"bool": {
				"must": [
					{ "range": { "@timestamp" : { "gte" : "now-2d/d" }}},
					{ "range": { "@timestamp" : { "lte" : "now-1d/d" }}}
				]
			}
		},

I also tried this (partial) query to get only the data the beginning of yesterday until the end of yesterday:

"filter": {
			"bool": {
				"must": [
					{ "range": { "@timestamp" : { "gte" : "now-1d/d" }}},
  					{ "range": { "@timestamp" : { "lt" : "now/d" }}}
				]
			}
		},

Which seemed to work better than the above query, but it is retrieving data from 2 days ago (so if it is the 16th, it is retrieving hits from the 14th. I only want data from 00:00:00 on the 15th through 23:59:59 on the 15th (or through the next 00:00:00, whatever is easiest; I am not picky about the second lost).

Finally, how would I get hits from whatever day yesterday was, last year? (If today is the 8/16 of 2015 then I would want hits from the 8/15 of 2014.)

Many thanks for any help in clearing these up for me.

2

After trying numerous things for several hours, I eventually started working with a new index and manually testing data. My mistake was that I could not tell that a search was not returning all results. Once I added my size constraints and then began testing date math variations again along with time zone configurations, I found a few that worked to get all data from yesterday until the end of yesterday.

The relevant part of the query:

"bool": {
  	"must": [
  		{ "range": { 
  			"timestamp" : { 
    				"gte": "now-1d/d",
                                    "lt": "now/d"
  		]
  }

With my (limited) dataset, all of the following date math expressions gave the same amount of hits and appeared to limit hits from 00:00:00 yesterday to the end of yesterday at 23:59:59. Are each of these equivalent expressions?

"gte": "now-1d/d",   "lt": "now/d"

"gte": "now/d-1d",   "lt": "now/d"

"gte": "now/d-1d/d",   "lt": "now/d"

To simply clarify, I am still not sure what /d does exactly. Does it just round down to the beginning of a time period? Or does it round down (backward) to the nearest day?

If it is 12:01:00 currently

now/d = 00:00:00
now-1h/d = 11:00:00
now+1h/d = 12:00:00

Is this interpretation on the right track?

Is there any further documentation on /d’s behavior other than what is mentioned in the link in my post above?

3

I can't work out if there are any question from your first post which you still need answers to but I have tried to answer the questions in your second post below. If you still have questions from your first post that you would like answered (or have other questions on date math) let me know.

the /X operator (where X is a time period like d) will round back in time to the nearest start of that time period. So /d will round back to the start of the day, whereas /M would round back to the start of the month.

So if it is 2015-08-15 12:01:00 currently:

now/d = 2015-08-15 00:00:00
now-1h/d = (2015-08-15 11:01:00)/d = 2015-08-15 00:00:00
now+1h/d = (2015-08-15 13:01:00)/d = 2015-08-15 00:00:00
now-13h/d = (2015-08-14 23:01:00)/d = 2015-08-14 00:00:00

now/d-1h = (2015-08-15 00:00:00) - 1h = 2015-08-14 23:00:00
now/d+1h = (2015-08-15 00:00:00) + 1h = 2015-08-15 01:00:00
now/d-13h = (2015-08-15 00:00:00) - 13h = 2015-08-14 11:00:00

Does that make sense?

To pick a couple of the questions from your first post:

try this:

"filter": {
			"bool": {
				"must": [
					{ "range": { "@timestamp" : { "gte" : "now/M" }}}
				]
			}
		},

If now is 2015-08-18 11:00:00 then:

now/M = 2015-08-01 00:00:00

So the above query will match everything from 1st August 2015 onwards

Your second example should work here:

"filter": {
			"bool": {
				"must": [
					{ "range": { "@timestamp" : { "gte" : "now-1d/d" }}},
					{ "range": { "@timestamp" : { "lt" : "now/d" }}}
				]
			}
		},

Because if now is 2015-08-18 11:00:00 then:

now-1d/d = (2015-08-17 11:00:00)/d = 2015-08-17 00:00:00
now/d = 2015-08-18 00:00:00

So the problem here could be timezones (timezones are hard). When you ingest your data does it contain a timezone? What is the timezone of your server? What is the timezones of the results when they are displayed (could you show a snippet of some of the erroneous hits?)?

HTH

4 Likes
4

Thank you for clarifying things for me, Colin. What you said does make sense. (And after re-working through some parts of my post that I now realize were likely vague.) After your explanation of the rounding syntax, and working out a few more issues for myself, I was able to convert the majority of ~700 SQL-style queries to Elasticsearch queries to return the same set of data.

I have a few remaining concerns.

What is the native time zone and format that an Elasticsearch date field is converted into? My date field includes a timezone, but I must still specify the time zone when searching in Elasticsearch. I have been assuming it is UTC (time zone offset 00:00), but I’d like to confirm.

I use an index mapping for my date field that includes a timezone ID which I parse with Joda’s “ZZZ” format. Different documents have different time zones associated with them. (America/Los_Angeles, America/Vancouver, and so on.) The field itself retains the original date I supplied with the time zone while searching. Is there a way to see a date or timestamp field how Elasticsearch sees it? (As whatever time format a custom date field is converted to by Elasticsearch, such as its interpretation of UTC or milliseconds since the Unix epoch, etc.)

I am also having problems with getting weekly data, such as last week’s data, because I am not sure how to specify that that Sunday is the beginning of the week over Joda’s default Monday.

I tried this:

"range": {
   "timestamp" : {
       “gte”: “now/w-1d”,
        "lt": "now"
   }
}

"gte": "now/w-1d" (read as: round to the beginning of this week (Monday) and subtract one day) may generally work unless it is currently Sunday, because then a week would be Sunday to Sunday instead of Sunday to Saturday.

I have read that for aggregations, “offset” can be used to move everything back a day ( https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-datehistogram-aggregation.html#_offset ). Is something similar available for simple bool range queries? I tried using "offset" but was given a query error.

Alternatively, is there a setting (global or otherwise) that I can adjust to make the start of a week treated in the standard US locale way? (Sunday as the first day of the week.)

5

All dates are indexed as a long field with a value which is the epoch time in milliseconds, so effectively the dates are converted to UTC.

If you have enough memory to load the date field into the field data cache then you could use the 'field_data fields' feature to see the indexed value, but I would only advise using that as a debugging option here and not as your final solution.

Would the following work instead?

"range": {
   "timestamp" : {
       “gte”: “now/w-1d”,
        "lt": "(now+1w)/w - 1d"
   }
}

This should find the beginning of the current week for the start of the range and the beginning of the next week for the end of the range.

As far as I know there isn't an offset for range queries since you have complete control over the start and end of the range. In the date_histogram aggregation the offset parameter is used to adjust where the buckets start from but this doesn't really apply when using a single filter.

I don't think there is, sorry.

6

Thanks again Colin. You’ve been very helpful to me. It's too bad there aren't any quick fixes for the US week issue, but you've helped me with the tools needed to try to further debug time issues as they arise. I’ll post again if I encounter anymore issues.

One note is that I had to modify the date math from "(now+1w)/w - 1d" to "now+1w/w-1d" because Elasticsearch wouldn’t accept it (gave an error that said that it could not parse the date math). Maybe it was the parentheses? Hopefully this modification won't have an unanticipated impact on the query.

7

Hi, It is possible to get custom start of the week using datemath in elasticsearch(v2.3):

weekdays = ["monday", "tuesday", "wednesday", "thursday", "friday", "saturday", "sunday"]
offset = weekdays.indexOf(startDayOfWeek)

this_week ::=
  gte = "now-<offset>d/w+<offset>d"
  lte = "now-<offset>d/w+<offset>d"

last_week ::=
  gte = "now-<offset>d/w+<offset>d-w"
  lt  = "now-<offset>d/w+<offset>d"

For example if start_of_week should be 'sunday'. then filters for this week and last week are as follows:

this_week::=
gte = "now-6d/w+6d"
lte = "now-6d/w+6d-w"

last_week:==
  gte = "now-6d/w+6d-w"
  lt  = "now-6d/w+6d"
3 Likes