2

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?

philantrovert
  • 9,904
  • 3
  • 37
  • 61
monkey intern
  • 705
  • 3
  • 14
  • 34

1 Answers1

3

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

Community
  • 1
  • 1
David דודו Markovitz
  • 42,900
  • 6
  • 64
  • 88
  • Oooh so it is yyyy*-*MM*-*dd right? Like, are the "-" symbol a strict restriction? If so, is there any function to format the date, other than toDate, or something more programaticaly like a spark job required? Thanks for the help. – monkey intern Jun 23 '17 at 06:24
  • 1
    @monkeyintern - sorry for the delay, check the updated answer – David דודו Markovitz Jul 07 '17 at 12:08