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.)