[
https://issues.apache.org/jira/browse/HIVE-3822?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13544761#comment-13544761
]
Mark Grover commented on HIVE-3822:
-----------------------------------
The short answer is no.
Here is why:
That particular unix timestamp represents Wed, 19 Dec 2012 19:12:19 GMT. The
Hive timestamp datatype is a based on [TimestampWritable|
https://github.com/apache/hive/blob/trunk/serde/src/java/org/apache/hadoop/hive/serde2/io/TimestampWritable.java]
which is a essentially a wrapper around [java.sql.Timestamp|
https://github.com/apache/hive/blob/trunk/serde/src/java/org/apache/hadoop/hive/serde2/io/TimestampWritable.java#L67].
java.sql.Timestamp as you know is timezone-less. It's more or less equivalent
to a unix timestamp. Consequently, Hive timestamps are inherently
timezone-less. The tricky bit though is when you have to print this timestamp
in a human-readable form where you need use a particular timezone. As you will
see in the code, the [DateFormat |
https://github.com/apache/hive/blob/trunk/serde/src/java/org/apache/hadoop/hive/serde2/io/TimestampWritable.java#L65]
specifies no particular timezone when [formatting the Timestamp object to a
human-readable string|
https://github.com/apache/hive/blob/trunk/serde/src/java/org/apache/hadoop/hive/serde2/io/TimestampWritable.java#L318],
so it ends up using the default timezone of the JVM which is the same as the
system's default timezone unless specifically overriden.
To summarize, when we print a timestamp object in Hive as a human-readable
string, the default JVM/system timezone is used. In my case, the above Hive
queries were run on PST machine (GMT-8), so 2012-12-19 11:12:19 is the correct
expected output instead of 2012-12-19 19:12:19
Hope that helps!
> Casting from double to timestamp type seems inconsistent
> --------------------------------------------------------
>
> Key: HIVE-3822
> URL: https://issues.apache.org/jira/browse/HIVE-3822
> Project: Hive
> Issue Type: Bug
> Affects Versions: 0.9.0
> Reporter: Mark Grover
> Assignee: Mark Grover
>
> {code}
> select cast(1355944339 as timestamp) from decimal_3 limit 1;
> 1970-01-16 08:39:04.339
> select cast(1355944339000 as timestamp) from decimal_3 limit 1;
> 2012-12-19 11:12:19
> select cast(1355944339.123456789 as timestamp) from decimal_3 limit 1;
> 2012-12-19 11:12:19.1234567
> {code}
> If specifying the unixTimestamp without a decimal point, we need to specify
> the millisecond timestamp. If specifying with a decimal point, we need to
> specify only the second timestamp and the rest goes after decimal. Moreover,
> it seems like some precision is lost (notice '89' are lost in the last query.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
