Search code examples
hadoophivehue

Hive timestamp format

I have some data with the following format:

28/04/2017 00:00:00|20550|22/05/2017 00:00:00|

I am setting | as a separator. For the data in the first and third row in this example, I put timestamp as a data type in HUE, but I get nulls as answer.

I have looked around and it seems to me like Hive supports Unix format timestamp (from places like this )

But, I get a "Invalid date" message (I'm doing it with HUE so far, I'm still new with this technologies). If I try to make this data String, I can see values, but when I try to use to_date() the problem persists.

Is there anything I'm ignoring?

Solution

  • The only supported timestamp format is yyyy-MM-dd HH:mm:ss with optional fraction of seconds.
    Anything else should be read as string and converted later.

    Demo

    with t as (select '28/04/2017 00:00:00' as mydate)
select  from_unixtime(to_unix_timestamp (mydate,'dd/MM/yyyy HH:mm:ss'))
from    t

    2017-04-28 00:00:00