Working with timezones and DST

#1

Hi,

We are trying to handle local datetimes as our users could live in different timezones.

One basic need is to define a datetime object in the user timezone, given his timezone. The method suggested by @lpoirier only works with a fixed UTC offset (format hinted by Joda’s documentation):

DateTime.deserialize("2018-01-01T00:00:00+01:00") // works
DateTime.parse("12-21-2018 +12:00", "MM-dd-yyyy Z") // works
DateTime.parse("12-21-2018 America/Los_Angeles", "MM-dd-yyyy Z") // Missing time zone offset in DateTime string to parse (expected format Z)
DateTime.parse("12-21-2018 America/Los_Angeles", "MM-dd-yyyy z") // Invalid name 'America' in DateTime string to parse (expected format z)

Problem with the first 2 examples is that I’d need to deal with DST myself, ie. store some kind of tzinfo database to map Europe/Paris or America/Los_Angeles to +01:00 or +02:00 depending on the time of the year, which is cumbersome and imo not my application’s job.

Then, a related issue also pops up when manually creating timeseries. Take this basic snippet:

var spec = TSEvalSpec.make({
       start: DateTime("2017-01-01Z"),
       end: DateTime("2017-06-01Z"),
       interval: "HOUR"});

var ats = AsTimeseriesSpec.make({
    startPath: "start",
    endPath: "end",
    valuePath: "value"
});

// Just one random slot in the middle of the interval
var slots = [
   TimeSlotInt.make({
       start: DateTime("2017-03-25+01:00"),
       end: DateTime("2017-03-27T00:01+02:00"),
       value : 1
   })
];

var ts = Timeseries.fromObjs(ObjArry.fromArry(slots),
        spec, ats);

There are 151 days between 2017-01-01T00:00:00Z & 2017-06-01T00:00:00Z. As expected, previous snippet generates (ts.count()) 3624 slots = 151 * 24.

However in the Europe/Paris timezone, from 1st of Jan. to 1st of Jun. (2017-01-01T00:00:00+01:00 & 2017-06-01T00:00:00+02:00), there are only 3623 slots because of DST. If I change spec to use local times:

var spec = TSEvalSpec.make({
   start: DateTime("2017-01-01+01:00"), // manual offset for example sake, we are looking for timezones instead
   end: DateTime("2017-06-01+02:00"), 
   interval: "HOUR"});

I still get 3624 slots (this is wrong, and the resulting timestamps seem to have discarded the offset info). However, if I add the timezone to spec:

var spec = TSEvalSpec.make({
   start: DateTime("2017-01-01+01:00"),
   end: DateTime("2017-06-01+02:00"),
   timeZone: 'Europe/Paris',
   interval: "HOUR"});

I get the following exception: "Incorrect construction of timeseries meta. Length of field {} mismatch. expected=3623, actual=3624" which hints that C3 is trying to create a properly dimensioned timeseries but that there is a problem somewhere else.

What would you recommend to tackle those 2 issues?

Thanks

0 Likes

Daylight savings
#2

You can find documentation on DateTime by looking the JS section of the documentation. It should be visible at :your-server-url:/static/jsduck/index.html#!/api/DateTime

DateTime supports ISO 8601 dates. Offsets like “America/Los_Angeles” or “Europe/Paris” are not valid in ISO 8601, so they cannot be parsed. As you point out, formats like “America/Los_Angeles” are not a well-defined, so there is not an ISO standard to convert those to a date offset.

0 Likes

#3

Hi Seth,

Thanks for this link, it is useful indeed.

Text offsets are not part of the ISO8601 norm but they are absolutely well-defined and widely implemented on virtually every Unix system through tzinfo.

The doc I saw earlier mentioned that C3’s implementation used Joda, and while Joda’s DateTimeFormat is extremely similar to C3’s DateTime.parse it seems to have full support for those standardized timezones (see http://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html).

Therefore, do you mean that C3 stripped out this feature and provides no helper for DST-aware timezone conversion?

Thanks

0 Likes

#4

Hi Ierela,

The spec that you have here means, that one desires data from the start date to the end date in the desired time zone. The offsets as a part of the start & end date will be ignored and picked from the time zone that has been passed in. That is why, when you DID pass the timeZone in the spec, you still got 3624 points.

I would recommend writing the spec as follows.

var spec = TSEvalSpec.make({
   start: DateTime("2017-01-01T00:00:00"),
   end: DateTime("2017-06-01T00:00:00"),
   timeZone: 'Europe/Paris',
   interval: "HOUR"});

C3 has worked on adding support for time zones in v7.7 including what you are trying to do in the example above, and only had partial support in the versions before that.

Let me know if this is still occurring in v7.7 of the server and we should definitely file a ticket and get this fixed asap

1 Like

#5

Thanks Rohit for those explanations. Unfortunately we do not have a 7.7 environment so I can’t test it, therefore I guess we’ll have to drop support for UTC and timezones as of now and store local times instead.

However it is a bit disappointing that we have to pre-process our measurements just to change the timezone from UTC as I was hoping to perform any csv-related operation inside the C3 transform.

1 Like

#6

What are the qualities of the data as it’s loaded into the platform that enable me to leverage the above mentioned time zone features in 7.7?

  • Do I need to populate the timeZone field on the MeasurementSeries type?
  • Do I need to load my Measurement.start/end/timestamp fields with an offset?
  • Any other items that need to happen?
  • How will the timeZone field on the EvalMetricSpec behave if the above conditions are met/not met?
0 Likes

#7
  1. If you want your measurement series to be normalized in the desired zone, then set the timeZone field on the measurement series (Remember by default normalization normalizes and stores the normalized values in the zone that the data was provided). Changing this value on 1 series but not others can cause your metrics to aggregate information in various zones (which might not make sense), so be sure to change this setting consistently across various headers

  2. Yes it is always a good idea to have the dates be stored with the right offsets irrespective of whether using this feature or not.

  3. No

  4. When timeZone field is set on the evalMetrics, the following behavior will happen. (FYI this is available as a part of the TimeZone documentation in the In-Depth section)

Topic: TimeZone
Category: Advanced

###Need
A lot of large organizations today have global footprints and have the need to analyze their data in different timezones to make sense of their data at an aggregated level. Imagine an organization having operations in Americas, Europe, Australia, etc wanting to analyze the energy consumption across all of their facilities across the world. In spite of each region generating data in their respective timezones, it still does make sense to analyze this at a broader level to answer questions like “What was the electricity consumption across all my facilities at 2 pm”.

###Solution
Due to the increasing need for these use cases, C3’s time series engine now has the ability to query data in various zones irrespective of where the source of the data comes from. All C3 time series evaluation apis take in a parameter for timezone, which indicates the desired time zone in which the data is desired. The engine figures out the source timezone of the data, converts them to the desired zone & performs time series math on this transformed data.

The following are the supported time zone choices.

#####1. NONE
Creating a datetime in NONE timezone represents that the datetime is in “logical time” i.e. there is no zone information associated with this datetime and means the date and time are as stated by this object. E.g. “2010-01-01T04:00:00” without any zone information means 4 am on the 1st of January, 2010. It doesn’t matter whether this date is being viewed in California or Australia. It will still remain the 1st of January, 2010 4am. This is the default mode for all time series computation.

#####2. UTC
Coordinated Universal Time (UTC) is the basis for civil time today. This 24-hour time standard is kept using highly precise atomic clocks combined with the Earth’s rotation (reference : https://www.timeanddate.com/time/aboututc.html)

#####3. OFFSET
One can create dates in specific offsets. These aren’t really time zones but offset from the UTC time. E.g. “2010-01-01T00:00:00-08:00”, the offset will always be 8 hours shifted from the UTC time. The 24 hour time standard is maintained for offset based date times.

#####4. ACTUAL TIMEZONE ID
Create datetime in specific time zone id. E.g “2010-01-01T00:00:00-07:00” (America/Los_Angeles). This datetime will follow the day light savings shifts (may not always have 24 hours in a day) and is in the America/Los_Angeles time zone. “-07:00” happens to be the offset for the “America/Los_Angeles” time zone on the 1st of January, 2010.

###Example
You can now evaluate metrics to return results in any time zone, offset. An example call would look like:

Metric Evaluation in various time zones
1. Organization.evalMetric({
 expression:"ElectricityConsumption",
 id:"MyOrgId",
 start:"2016-01-01"
 end:"2017-01-01"
 interval:"HOUR",
 timeZone: "NONE"
});
//Expected results should be in logical times (no zone) and will always have 24 hours in a day when evaluated at intervals finer or equal to an HOUR
 
2. Organization.evalMetric({
 expression:"ElectricityConsumption",
 id:"MyOrgId",
 start:"2016-01-01"
 end:"2017-01-01"
 interval:"HOUR",
 timeZone: "America/Los_Angeles"
});
//Expected results will be in America/Los_Angeles time zone and could have fewer/more than 24 hours when evaluated at intervals finer or equal to an HOUR
 
3. Organization.evalMetric({
 expression:"ElectricityConsumption",
 id:"MyOrgId",
 start:"2016-01-01"
 end:"2017-01-01"
 interval:"HOUR",
 timeZone: "UTC"
});
//Expected results will be in UTC time zone and will always have 24 hours in a day when evaluated at intervals finer or equal to an HOUR
 
4. Organization.evalMetric({
 expression:"ElectricityConsumption",
 id:"MyOrgId",
 start:"2016-01-01"
 end:"2017-01-01"
 interval:"HOUR",
 timeZone: "+08:00"
})
//Expected results will be in +08:00 offset and will always have 24 hours in a day when evaluated at intervals finer or equal to an HOUR

Checkout the {@link TimeZone} to learn more about generic time zone handling

1 Like

#8

Thanks Rohit,

To add additional details here:

In order for the timeZone field on the EvalMetricSpec Type to make sense, the measurement data MUST be loaded with an offset. Otherwise, the Platform will not know how to convert the loaded Measurements to the Time Zone desired.

For clarity, the below image shows the top couple rows of Measurements without offsets, and the bottom couple rows with offsets.

0 Likes