You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Peyman Mohajerian <mo...@gmail.com> on 2014/09/10 23:38:12 UTC
String to Timestamp conversion bug
Hi Guys,
I Hive .13 for this conversion:
select cast('2999-12-31 23:59:59' as timestamp)
I get:
1830-11-23 00:50:51.580896768
up to around year 2199 it works fine, the work around is to convert the
string to int and then back to timestamp:
from_unixtime(unix_timestamp('2999-12-31 23:59:59.000000')
But why is this issue happening in the first place?
Thanks,
Re: String to Timestamp conversion bug
Posted by Jason Dere <jd...@hortonworks.com>.
FYI it looks like this has to do with the representation of timestamps during vectorized execution - timestamps are represented as a long value representing nanoseconds since the epoch, thus the max timestamp value in that format is 2262-04-11
On Sep 22, 2014, at 4:13 PM, Peyman Mohajerian <mo...@gmail.com> wrote:
> So i found out more detail about this issue,
> if in:
> select cast('2999-12-31 23:59:59' as timestamp) from table;
> if the table has 'orc' data, and you are using hive .13 and set hive.vectorized.execution.enabled = true;
> then this issue occurs, it maybe related to: hive-6656 i'm not certain of that.
>
>
>
>
>
>
> On Wed, Sep 10, 2014 at 11:05 PM, Peyman Mohajerian <mo...@gmail.com> wrote:
> It is using either 1.6 or 1.7, but i tested
> System.out.println(" " + Timestamp.valueOf("2999-12-31 23:59:59" ));
> on both 1.7 and 1.6 version and it works in both cases.
>
> On Wed, Sep 10, 2014 at 10:12 PM, Jason Dere <jd...@hortonworks.com> wrote:
> Hmm that's odd .. it looks like this works for me:
>
> hive> select cast('2999-12-31 23:59:59' as timestamp);
> OK
> 2999-12-31 23:59:59
> Time taken: 0.212 seconds, Fetched: 1 row(s)
>
> For string to timestamp conversion, it should be using java.sql.Timestamp.valueOf(). What version of jvm are you using?
>
>
>
> On Sep 10, 2014, at 2:38 PM, Peyman Mohajerian <mo...@gmail.com> wrote:
>
>> Hi Guys,
>>
>> I Hive .13 for this conversion:
>> select cast('2999-12-31 23:59:59' as timestamp)
>> I get:
>> 1830-11-23 00:50:51.580896768
>> up to around year 2199 it works fine, the work around is to convert the string to int and then back to timestamp:
>> from_unixtime(unix_timestamp('2999-12-31 23:59:59.000000')
>>
>> But why is this issue happening in the first place?
>>
>> Thanks,
>
>
> 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.
>
>
--
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.
Re: String to Timestamp conversion bug
Posted by Peyman Mohajerian <mo...@gmail.com>.
So i found out more detail about this issue,
if in:
select cast('2999-12-31 23:59:59' as timestamp) from table;
if the table has 'orc' data, and you are using hive .13 and set
hive.vectorized.execution.enabled = true;
then this issue occurs, it maybe related to: hive-6656 i'm not certain of
that.
On Wed, Sep 10, 2014 at 11:05 PM, Peyman Mohajerian <mo...@gmail.com>
wrote:
> It is using either 1.6 or 1.7, but i tested
> System.out.println(" " + Timestamp.valueOf("2999-12-31 23:59:59" ));
> on both 1.7 and 1.6 version and it works in both cases.
>
> On Wed, Sep 10, 2014 at 10:12 PM, Jason Dere <jd...@hortonworks.com>
> wrote:
>
>> Hmm that's odd .. it looks like this works for me:
>>
>> hive> select cast('2999-12-31 23:59:59' as timestamp);
>> OK
>> 2999-12-31 23:59:59
>> Time taken: 0.212 seconds, Fetched: 1 row(s)
>>
>> For string to timestamp conversion, it should be using
>> java.sql.Timestamp.valueOf(). What version of jvm are you using?
>>
>>
>>
>> On Sep 10, 2014, at 2:38 PM, Peyman Mohajerian <mo...@gmail.com>
>> wrote:
>>
>> Hi Guys,
>>
>> I Hive .13 for this conversion:
>> select cast('2999-12-31 23:59:59' as timestamp)
>> I get:
>> 1830-11-23 00:50:51.580896768
>> up to around year 2199 it works fine, the work around is to convert the
>> string to int and then back to timestamp:
>> from_unixtime(unix_timestamp('2999-12-31 23:59:59.000000')
>>
>> But why is this issue happening in the first place?
>>
>> Thanks,
>>
>>
>>
>> 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.
>
>
>
Re: String to Timestamp conversion bug
Posted by Peyman Mohajerian <mo...@gmail.com>.
It is using either 1.6 or 1.7, but i tested
System.out.println(" " + Timestamp.valueOf("2999-12-31 23:59:59" ));
on both 1.7 and 1.6 version and it works in both cases.
On Wed, Sep 10, 2014 at 10:12 PM, Jason Dere <jd...@hortonworks.com> wrote:
> Hmm that's odd .. it looks like this works for me:
>
> hive> select cast('2999-12-31 23:59:59' as timestamp);
> OK
> 2999-12-31 23:59:59
> Time taken: 0.212 seconds, Fetched: 1 row(s)
>
> For string to timestamp conversion, it should be using
> java.sql.Timestamp.valueOf(). What version of jvm are you using?
>
>
>
> On Sep 10, 2014, at 2:38 PM, Peyman Mohajerian <mo...@gmail.com> wrote:
>
> Hi Guys,
>
> I Hive .13 for this conversion:
> select cast('2999-12-31 23:59:59' as timestamp)
> I get:
> 1830-11-23 00:50:51.580896768
> up to around year 2199 it works fine, the work around is to convert the
> string to int and then back to timestamp:
> from_unixtime(unix_timestamp('2999-12-31 23:59:59.000000')
>
> But why is this issue happening in the first place?
>
> Thanks,
>
>
>
> 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.
Re: String to Timestamp conversion bug
Posted by Jason Dere <jd...@hortonworks.com>.
Hmm that's odd .. it looks like this works for me:
hive> select cast('2999-12-31 23:59:59' as timestamp);
OK
2999-12-31 23:59:59
Time taken: 0.212 seconds, Fetched: 1 row(s)
For string to timestamp conversion, it should be using java.sql.Timestamp.valueOf(). What version of jvm are you using?
On Sep 10, 2014, at 2:38 PM, Peyman Mohajerian <mo...@gmail.com> wrote:
> Hi Guys,
>
> I Hive .13 for this conversion:
> select cast('2999-12-31 23:59:59' as timestamp)
> I get:
> 1830-11-23 00:50:51.580896768
> up to around year 2199 it works fine, the work around is to convert the string to int and then back to timestamp:
> from_unixtime(unix_timestamp('2999-12-31 23:59:59.000000')
>
> But why is this issue happening in the first place?
>
> Thanks,
--
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.
Re: String to Timestamp conversion bug
Posted by Peyman Mohajerian <mo...@gmail.com>.
The point of over-flow is:
2262-04-11 20:00:00
if you go a second earlier it works fine:
2262-04-11 19:23:59
On Wed, Sep 10, 2014 at 5:38 PM, Peyman Mohajerian <mo...@gmail.com>
wrote:
> Hi Guys,
>
> I Hive .13 for this conversion:
> select cast('2999-12-31 23:59:59' as timestamp)
> I get:
> 1830-11-23 00:50:51.580896768
> up to around year 2199 it works fine, the work around is to convert the
> string to int and then back to timestamp:
> from_unixtime(unix_timestamp('2999-12-31 23:59:59.000000')
>
> But why is this issue happening in the first place?
>
> Thanks,
>