May be a JIRA ?
I remember having my own UDF for doing this. If possible I will share the
code
On Thu, Nov 6, 2014 at 6:22 AM, Jason Dere <jd...@hortonworks.com> wrote:
> Hive should probably at least provide a timezone option to
> from_unixtime().
> As you mentioned, Hive doesn't really do any timezone handling, just
> assumes things are in the system's local timezone. It will be a bit of a
> bigger project to add better time zone handling to Hive timestamps.
>
>
> On Nov 5, 2014, at 7:18 AM, Maciek <mac...@sonra.io> wrote:
>
> I see… and confirm, it's consistent with Linux/Unix output I get:
> date -r 0
> Thu 1 Jan 1970 01:00:00 IST
>
> date
> Wed 5 Nov 2014 14:49:52 GMT
> Got some digging and it actually makes sense. Turns out Ireland didn't
> observe daylight saving time in years 1968-1972 as set permanently to
> GMT+1=IST.
>
> Anyway, back to Hive
> I'm trying to convert unix_times to UTC (using from_unixtime UDF )but due
> to the issue it I'm getting different results on different servers (TZ
> settings)
> Is there any way influence that behaviour without changing timezone on the
> server?
>
> Oracle for that instance offers a good few options to facilitate timezone
> conversion, among the others:
> 'AT TIME ZONE [GMT]' clause
> ALTER SESSION SET TIME_ZONE [= 'GMT']
> or
> to_timestamp_tz() function
>
> Currently it seems, the only way to perform this conversion is to detect
> server settings first (won't work at all for some cases like though JDBC
> connection I think) and apply the shift during the process.
>
> Would be really nice if Hive offers some elegant way to support this.
> I'm thinking of similar ALTER SESSION statement equivalent, maybe
> parameter SET in hive or extra parameter for the from_unixtime() Hive
> function?
>
> On Mon, Nov 3, 2014 at 10:33 PM, Jason Dere <jd...@hortonworks.com> wrote:
>
>>
>> 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 <mac...@sonra.io> 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 <mac...@sonra.io> 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 saving.
>>> 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 <mac...@sonra.io> 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 [UTC]
>>>>
>>>> • 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 <nitinpawar...@gmail.com>
>>>> 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 <mac...@sonra.io> 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 00:00:00…?
>>>>>>>
>>>>>>
>
> CONFIDENTIALITY NOTICE
> 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.
>
--
Nitin Pawar
