Handling 1d date_histogram queries that span Daylight Saving Time (Nov 6th 2 am)

(Michael Parmeley) #1

I store all my documents with date/times in GMT. Then at query time I use the "time_zone" property of a date_histogram to choose which timezone to localize it to (we have users in multiple time zones).

This works great; however, I have ran into a problem for queries that span the daylight saving time change on Nov 6th and use 1d intervals. The problem arises because on Nov 6 at 2am the timezone offset changes e.g. on Nov 6th at 1 am the US Central timezone is GMT-0500, at Nov 6th at 2am the US Central timezone is GMT-0600. Since you can only choose one time_zone for the query everything past Nov 6th is one hour off i.e. days appear to start at 11 pm the previous day. And a 30 day Nov 1-Nov 30 query using 1d intervals gets 31 buckets, where the 31st bucket is the first hour of Dec 1st.

In reality Nov 1st - Nov 6th at 2am should be queried with time_zone offset -0500 and after Nov 6th at 2 am the time_zone offset should change to -0600.

How have other people handled this?

Handling Time Zones and Daylight saving time
(Mark Walkom) #2

ES doesn't care about this, it leaves it to the client. So doing that yourself is cleanest and best :slight_smile:

(Michael Parmeley) #3

Actually your response isn't true at all. If you use a raw offset (e.g -0500) in the "time_zone" parameter (or "pre_zone" in earlier versions of ES) then no daylight saving time is taken into account. However, if you use a timezone ID that supports daylight saving time (e.g. "US/Central") then ES does indeed take into account time changes if they occur during the span of your query.

(got my answer by opening a support request to Elastic).

(Mark Walkom) #4

Oh, there you go! :smiley:

(system) #5

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.