[SPARK-17971] Unix timestamp handling in Spark SQL not allowing calculations on UTC times

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Not A Problem
• Affects Version/s: 1.6.2
• Component/s: Spark Core, SQL

Environment

MacOS X JDK 7

• Assignee: Unassigned
• Reporter: Gabriele Del Prete
• Created: 17/Oct/16 17:28
• Updated: 27/Oct/17 08:51
• Resolved: 24/Oct/16 09:08

Description

In our Spark data pipeline we store timed events using a bigint column called 'timestamp', the values contained being Unix timestamp time points.

Our datacenter servers Java VMs are all set up to start with timezone set to UTC, while developer's computers are all in the US Eastern timezone.

Given how Spark SQL datetime functions work, it's impossible to do calculations (eg. extract and compare hours, year-month-date triplets) using UTC values:

• from_unixtime takes a bigint unix timestamp and forces it to the computer's local timezone;
• casting the bigint column to timestamp does the same (it converts it to the local timezone)
• from_utc_timestamp works in the same way, the only difference being that it gets a string as input instead of a bigint.

The result of all of this is that it's impossible to extract individual fields of a UTC timestamp, since all timestamp always get converted to the local timezone.