Menu

Dates, Times, and Universal Coordination

cover-image

This post provides an overview and various methods converting dates between time zones, with examples and considerations for Daylight Savings time.

Having recently leveraged Dave Andrade's Twitter Analytics dashboard, I was curious to observe that, apparently, the optimal time for me to send tweets is on Mondays at around 2am. But wait. I don't send tweets while I'm sleeping. So, how can this be?

It's because the Twitter Analytics time stamps are stored in UTC.

Store Universally, Display Locally

As a best practice, one should store data in as constistent & portable a format as possible. For date & time values, that format is Coordinated Universal Time.

Coordinated Universal Time - Wikipedia, the free encyclopedia

So we should store our data in UTC and convert it into the local time zone for display. But conversion is tricky: offsetting the hour is easy. It's daylight savings that you need to consider.

Governments the world over are constantly tinkering with daylight savings. Not only national laws, but also state, provencial, and local municipality laws are each constantly changing.

The country of Argentina, for a personal example, attempted twice during my seven years living in Buenos Aires to switch to daylight savings. They failed both times, reverting back mid-change. This resulted in unplanned adjustments to the clock hour, and multiple software updates released world-wide to actualize the relationship between time zones.

Below are three demonstrations of how you might approach time zone conversion, prior to performing your visual analysis in Tableau.

1. As Part of ETL Processing

Since I was pre-processing my Twitter Analytics data anyway, the most sensible approach for me was to leverage the sophistication of R. I've thus gone back now to update my original script and insert the equivalent of these two new lines of code:

  
# time zone conversion
twit_data$LocalTime <- as.POSIXct(twit_data$time,tz="GMT")  
twit_data$LocalTime <- format(twit_data$LocalTime, tz="US/Pacific",usetz=FALSE)  

Notice that R is a vectorized language. The entire vector of values is converted in a single command. No loops required. Also note: I'm saving the converted values into a new column, named LocalTime, thus preserving the original UTC values.

Revolution Analytics covers a variety of important considerations when converting time zones in R:

Converting time zones in R: tips, tricks and pitfalls

2. As a Calculated Field in Tableau

Another option is to calculate the offset hours in Tableau. Dave has added this calculation to his dashboard on Tableau Public. His calculation in Tableau converts the time zone from UTC to EST, with a daylight savings adjustment, for any date between the years 2010 and 2020.

This approach is OK for an informal analysis, but is too brittle for a production grade application. That's because the calculation can break under a variety of very plausible situations:

  • any date earlier than the year 2010
  • any date after the year 2020
  • and any dates inside of the range, should the rules governing daylight savings happen to change

This type of brittle rigidity should be actively avoided in your production data pipelines & code. As an example of what can go wrong (and a harbinger of interesting times to come), this medium post is a fascinating read:
The curious case of the disappearing Polish S

For those various ‘less formal' dashboards in which it is an option, I will sometimes take this parameter based approach below.

First, check how the parameter is used within the dashboard. Basically, two times per year, the publisher will republish. This time with the parameter set in a new position. Or, the consumer can always change the parameter in real time if they have to:


This is what the parameter looks like:


And the calculation simply decides which date math to do, based on the parameter:


The parameter values of -7 and -8 above are for converting from UTC into PST.

While the parameter does need to be changed manually twice per year, this is simple and flexible and transparent approach. The user has control & there's nothing complicated that can break or cause confusion about how the system works.

3. At the Database Layer

A third option for converting time zones is to leverage the database itself. In his custom server admin views, Tableau Zen Master Mark Jackson leverages the postgres database to convert the Tableau repository timestamps from UTC into EST using Custom SQL:

SELECT "historical_events"."created_at" AT TIME ZONE 'EST' AS "created_at (EST)"

Tableau Zen: Custom Tableau Server Admin Views

Additionally, Pass-Thru SQL can also leverage the database. The Pass-Thru approach may, in many cases, be more appropriate than custom SQL in the data connection. This is because custom SQL data sources often involve trade-offs (for example, from a performance perspective, custom SQL data sources cannot benefit from join culling). Thanks to Joe Mako for highlighting the Pass-Thru option!

In Summary

One must be very careful when converting dates & times. The conversion can occur at any layer in your stack:

  • in the Database
  • as a part of ETL
  • in Tableau

Daylight savings rules are ever changing. Brittle calculations are best avoided. Date and time conversions in a production pipeline should patch dynamically when world's details change.

Also, keep an eye on Tableau. In the future, it will only make sense for them to begin to perform these daylight savingsaware conversions directly within their software.

And as it turns out, the optimal time for my tweets is now apparently on Sundays, at around 6pm.

Word Count: 732

References

  1. "Leverage Dave's Twitter Analytics", Keith Helfrich, Red Headed Step Data, February 12, 2015
    http://redheadedstepdata.io/leverage-daves-twitter-analytics/
  2. "Coordinated Universal Time", Wikipedia.org, February 21, 2015
    http://en.wikipedia.org/wiki/Coordinated_Universal_Time
  3. "Converting time zones in R: tips, tricks and pitfalls", David Smith, Revolution Analytics, June 02, 2009
    http://blog.revolutionanalytics.com/2009/06/converting-time-zones.html
  4. "Twitter Analytics Dashboard", Dave Andrade, Tableau Public, February 19, 2015
    https://public.tableausoftware.com/profile/dave.andrade#!/vizhome/TwitterAnalyticsforDave/TwitterAnalytics
  5. "Custom Tableau Server Admin Views", Mark Jackson, Tableau Zen, August 13, 2014
    http://ugamarkj.blogspot.com/2014/08/custom-tableau-server-admin-views.html