hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jason Dere <>
Subject Re: from_unixtime() and epoch definition
Date Mon, 03 Nov 2014 22:33:15 GMT

As Nitin mentions, the behavior is "to a string representing the timestamp of that moment
in the current system time zone".  What are the timezone settings on your machine? 

$ TZ="GMT" date -r 0
Thu Jan  1 00:00:00 GMT 1970

$ TZ="UTC" date -r 0
Thu Jan  1 00:00:00 UTC 1970

$ TZ="Europe/London" date -r 0
Thu Jan  1 01:00:00 BST 1970

$ TZ="Europe/Dublin" date -r 0
Thu Jan  1 01:00:00 IST 1970

On Nov 3, 2014, at 12:50 PM, Maciek <> wrote:

> I'd consider this behaviour as a bug and would like to raise it as such.
> Is there anyone to confirm it's the same on Hive 0.14?
> On Fri, Oct 31, 2014 at 3:41 PM, Maciek <> wrote:
> Actually confirmed! It's down to the timezone settings
> I've moved temporarily server/client settings to 'Atlantic/Reykjavik' (no change in time
comparing to what I was on (GMT), but it's permanent UTC and as such doesn't observe daylight
> I believe this shouldn't matter (see my points from previous mail) but apparently there's
an issue with it.
> Not sure how to deal with this situation (can't just change TZ settings everywhere because
of Hive) and don't want to hardcode anything.
> I'm on Hive 0.13.
> Does Hive 0.14 provide better support for TimeZones?
> On Fri, Oct 31, 2014 at 3:25 PM, Maciek <> wrote:
> Thought about that myself based on my prior (bad) experience when tried to working with
timezones in Hive (functionality pretty much doesn't exists)
> That shouldn't be the case here though, here's why:
> in Oracle [timestamp with timezone] can be adjusted when sent/displayed on the client
based on client's settings. This may be also relevant if the timestamp in question would fall
onto client's daily saving time period. This behaviour would make sense to me, however:
> • this is server, not client settings we're talking about here
> • the server and client do reside in the same timezone anyway, which is currently GMT
> • while we observe the daily saving here [Dublin] the time in question ("1970-01-01
00:00:00") is not in that period, neither the time I'm sending the query (now).
> Based on all above, I don't see the reason the time gets shifted by one hour, but I realise
the issue might be down to the general problems in Hive' implementation of timezones…
> On Fri, Oct 31, 2014 at 12:26 PM, Nitin Pawar <> wrote:
> In hive from_unixtime is returned from the timezone which you belong to
> "From document : from_unixtime(bigint unixtime[, string format]) : Converts the number
of seconds from unix epoch (1970-01-01 00:00:00 UTC) to a string representing the timestamp
of that moment in the current system time zone in the format of "1970-01-01 00:00:00". 
> if possible can you also check by changing the timezone to UTC on your machine?
> On Fri, Oct 31, 2014 at 12:00 PM, Maciek <> wrote:
> Any reason why
> select from_unixtime(0) t0 FROM …
> gives
> 1970-01-01 01:00:00
> ?
> By all available definitions (epoch, from_unixtime etc..) I would expect it to be 1970-01-01
> -- 
> Kind Regards
> Maciek Kocon
> -- 
> Kind Regards
> Maciek Kocon

NOTICE: This message is intended for the use of the individual or entity to 
which it is addressed and may contain information that is confidential, 
privileged and exempt from disclosure under applicable law. If the reader 
of this message is not the intended recipient, you are hereby notified that 
any printing, copying, dissemination, distribution, disclosure or 
forwarding of this communication is strictly prohibited. If you have 
received this communication in error, please contact the sender immediately 
and delete it from your system. Thank You.

View raw message