New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

add time zone setting for relative date math in range filter/query #3729

Closed
tmkujala opened this Issue Sep 18, 2013 · 4 comments

Comments

Projects
None yet
6 participants
@tmkujala

tmkujala commented Sep 18, 2013

Hi,

When using a range filter/query and using relative date math to round, it's not always preferable to have the resulting date be in UTC. Adding some way to specify the time zone of the relative date would remedy this. Ideally this could either be an explicit offset or the name of a time zone recognized by Joda-Time time.

For example, say I am in the America/New_York time zone and I want to schedule a query to run every day at 2 AM that gets back all documents from yesterday, without hard coding any dates or time zones. In it's current form, you can define a range filter/query like this:

"range" : {
  "_timestamp" : {
    "gte" : "now/d-1d",
    "lt" : "now/d"
  }
}

Unfortunately since ElasticSearch defaults to the UTC timezone, this would only find documents from yesterday in the UTC time zone, and not America/New_York like we want.

This can be worked around by either adding an absolute timestamp that's calculated at the application layer or adding the current time zone offset to the range in the query. However, it would be much easier to specify the intended time zone as part of the range definition, similar to the way the date histogram facet works.

Perhaps a syntax similar to now/d-1d||<tz> would work. The ||<tz> would be optional and <tz> would follow the same rules as the pre_zone and post_zone settings for the date histogram facet.

@samcday

This comment has been minimized.

Show comment
Hide comment
@samcday

samcday May 23, 2014

(+1) for this. The date range syntax is awesome, but I unfortunately had to switch away from it and calculate dates in my application, as the results I'm looking for are sensitive to timezone.

samcday commented May 23, 2014

(+1) for this. The date range syntax is awesome, but I unfortunately had to switch away from it and calculate dates in my application, as the results I'm looking for are sensitive to timezone.

@pschultz

This comment has been minimized.

Show comment
Hide comment
@pschultz

pschultz Jun 18, 2014

We would also very much like to see support for this. Please note that in the use case of the OP the timezone offset would not be good enough since it may change over the course of the year (DST).

pschultz commented Jun 18, 2014

We would also very much like to see support for this. Please note that in the use case of the OP the timezone offset would not be good enough since it may change over the course of the year (DST).

@ankitajain90

This comment has been minimized.

Show comment
Hide comment
@ankitajain90

ankitajain90 Jul 17, 2014

  1. How can I define timezone format in ranges ?
    ranges: [{ from :now - 1d/d , to: now}]
    now gives me UTC time. I need time in IST format.
  2. can I define my ranges with now and time hardcoded to 1300hrs

{ from : yesterday (1pm) to : today 1pm}

ankitajain90 commented Jul 17, 2014

  1. How can I define timezone format in ranges ?
    ranges: [{ from :now - 1d/d , to: now}]
    now gives me UTC time. I need time in IST format.
  2. can I define my ranges with now and time hardcoded to 1300hrs

{ from : yesterday (1pm) to : today 1pm}

@dadoonet dadoonet self-assigned this Jul 31, 2014

@dadoonet dadoonet removed the adoptme label Jul 31, 2014

dadoonet added a commit to dadoonet/elasticsearch that referenced this issue Jul 31, 2014

Search: add time zone setting for relative date math in range filter/…
…query

Filters and Queries now supports `time_zone` parameter which defines which time zone should be applied to the query or filter to convert it to UTC time based value.

Closes #3729.

@dadoonet dadoonet added the v1.4.0 label Jul 31, 2014

@dadoonet dadoonet closed this in 873a45e Aug 4, 2014

dadoonet added a commit that referenced this issue Aug 4, 2014

Search: add time zone setting for relative date math in range filter/…
…query

Filters and Queries now supports `time_zone` parameter which defines which time zone should be applied to the query or filter to convert it to UTC time based value.

When applied on `date` fields the `range` filter and queries accept also a `time_zone` parameter.

The `time_zone` parameter will be applied to your input lower and upper bounds and will move them to UTC time based date:

[source,js]
--------------------------------------------------
{
    "constant_score": {
        "filter": {
            "range" : {
                "born" : {
                    "gte": "2012-01-01",
                    "lte": "now",
                    "time_zone": "+1:00"
                }
            }
        }
    }
}

{
    "range" : {
        "born" : {
            "gte": "2012-01-01",
            "lte": "now",
            "time_zone": "+1:00"
        }
    }
}
--------------------------------------------------

In the above examples, `gte` will be actually moved to `2011-12-31T23:00:00` UTC date.

NOTE: if you give a date with a timezone explicitly defined and use the `time_zone` parameter, `time_zone` will be
ignored. For example, setting `from` to `2012-01-01T00:00:00+01:00` with `"time_zone":"+10:00"` will still use `+01:00` time zone.

Closes #3729.

(cherry picked from commit 73a7a43)

@dadoonet dadoonet added v2.0.0 and removed v1.4.0 labels Aug 4, 2014

@dadoonet

This comment has been minimized.

Show comment
Hide comment
@dadoonet

dadoonet Aug 4, 2014

Member

FYI, a new option will be available in 1.4.0 with PR #7113

Member

dadoonet commented Aug 4, 2014

FYI, a new option will be available in 1.4.0 with PR #7113

dadoonet added a commit that referenced this issue Sep 8, 2014

Search: add time zone setting for relative date math in range filter/…
…query

Filters and Queries now supports `time_zone` parameter which defines which time zone should be applied to the query or filter to convert it to UTC time based value.

When applied on `date` fields the `range` filter and queries accept also a `time_zone` parameter.

The `time_zone` parameter will be applied to your input lower and upper bounds and will move them to UTC time based date:

[source,js]
--------------------------------------------------
{
    "constant_score": {
        "filter": {
            "range" : {
                "born" : {
                    "gte": "2012-01-01",
                    "lte": "now",
                    "time_zone": "+1:00"
                }
            }
        }
    }
}

{
    "range" : {
        "born" : {
            "gte": "2012-01-01",
            "lte": "now",
            "time_zone": "+1:00"
        }
    }
}
--------------------------------------------------

In the above examples, `gte` will be actually moved to `2011-12-31T23:00:00` UTC date.

NOTE: if you give a date with a timezone explicitly defined and use the `time_zone` parameter, `time_zone` will be
ignored. For example, setting `from` to `2012-01-01T00:00:00+01:00` with `"time_zone":"+10:00"` will still use `+01:00` time zone.

Closes #3729.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment