Menu
I'm trying to convert EST datetime to UTC in a Hive query, but can't see daylight saving taken into account. Do you know how to account for daylight saving in Hive?
For example:
TO_UTC_TIMESTAMP('2014-12-31 00:00:00', 'EST') gives 2014-12-31 05:00:00 i.e. 5 hour difference
TO_UTC_TIMESTAMP('2014-06-30 00:00:00', 'EST') gives 2014-06-30 05:00:00, also 5 hour difference
I'm expecting the June query to give a 4 hour difference.
In June the East Coast observes EDT (Eastern Daylight Savings Time), but Hive doesn't understand EDT at all:
TO_UTC_TIMESTAMP('2014-12-31 00:00:00', 'EDT') gives 2014-12-31 00:00:00 i.e. no difference
Any ideas?
Thanks, Ilmari
(Running Hadoop 1.0.3 on AWS Elastic MapReduce)
337
The switch to daylight saving time does not affect UTC. It refers to time on the zero or Greenwich meridian, which is not adjusted to reflect changes either to or from Daylight Saving Time.
Most of the United States and Canada observe DST on the same dates with a few exceptions. Hawaii and Arizona are the two U.S. states that don't observe daylight saving time, though Navajo Nation, in northeastern Arizona, does follow DST, according to NASA.
The simplest way to remember "time zone math" for the states that recognize Daylight saving time is three-two-one: three hours difference from Eastern to Pacific, two hours difference from Eastern to Mountain, and one hour difference from Eastern to Central.
Today, most Americans spring forward (turn clocks ahead and lose an hour) on the second Sunday in March (at 2:00 A.M.) and fall back (turn clocks back and gain an hour) on the first Sunday in November (at 2:00 A.M.). See how your sunrise and sunset times will change with our Sunrise/set Calculator.
Here is an open ticket from the Hive project that address this issue.
https://issues.apache.org/jira/browse/HIVE-12194
See 2nd comment:
Ben Breakstone added a comment - 16/Oct/15 16:54 It's worth noting the daylight saving time version of US three-letter codes like "PDT" are not included in /lib/zi/ for the Oracle JDK. New identifiers like "PST8PDT" appear to work as expected. See http://www.oracle.com/technetwork/articles/javase/alertfurtherinfo-139131.html
Perhaps as Ben Breakstone suggests new identifiers will work?
166
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
