[jira] [Commented] (HIVE-22691) HIve TimeStamp Query

["Karen Coppage (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/HIVE-22691?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17033560#comment-17033560 ] 

Karen Coppage commented on HIVE-22691:
--------------------------------------

Unix timestamp converts to number of seconds since epoch, so subseconds will be lost.

Try instead:
{code:java}
from xyz select date_format(last_modify_date, "yyyy-MM-dd HH:mm:ss.SSS");{code}
 

When Hive 4 comes out you can use

[https://cwiki.apache.org/confluence/display/Hive/CAST...FORMAT+with+SQL%3A2016+datetime+formats]

> HIve TimeStamp Query
> --------------------
>
>                 Key: HIVE-22691
>                 URL: https://issues.apache.org/jira/browse/HIVE-22691
>             Project: Hive
>          Issue Type: Task
>            Reporter: Taramol Soman
>            Priority: Minor
>
> I have two time stamp columns in a Hive DB storing timestamp in following format:
> hive> select last_date from xyz limit 2;
> 2019-08-21 15:11:23.553
> 2019-08-21 15:11:23.553
> [Above has milliseconds stored in it by default]
> hive> select last_modify_date from xyz limit 2;
> OK
> 2018-04-18 23:32:58
> 2017-09-22 04:02:32
> I need a common Hive select query which would convert both the above timestamps to 'YYYY-MM-DD HH:mm:ss.SSS' formats, preserving the millisecond value if exists, or appending '.000' if it doesnt exist.
> What I have tried so far:
> elect last_modify_date, from_unixtime(unix_timestamp(last_modify_date), "yyyy-MM-dd HH:mm:ss.SSS") as ts from xyz limit 3;
> However, the above query displays '.000' for both the above said timestamp columns.
> Please help



--
This message was sent by Atlassian Jira
(v8.3.4#803005)