1

I have issued the following select statement against a HIVE table in HUE editor and got below result. 

SELECT statestartdate,
to_date(from_unixtime(statestartdate, 'yyyy-MM-dd HH:mm:ss')) AS statestartdatestr
FROM processmining.InstanceUserPerformance limit 100

RESULT

statestartdate  statestartdatestr
1363782909107   45186-07-24

QUESTION In the year area of the 'statestartdatestr' field, I am getting 45186 for some strange reason!? Could anyone point out why please? Thnx.

F. Aydemir
  • 2,665
  • 5
  • 40
  • 60

2 Answers2

2

It seems like your statestartdate is the number of milliseconds since the Unix epoch. The function from_unixtime takes the number of seconds from the Unix epoch, according to Cloudera docs.

Therefore, if you wanted to convert your statestartdate to seconds and a date data type you could try something like this...

SELECT statestartdate,
  to_date(
    from_unixtime(
      floor(statestartdate / 1000),
      'yyyy-MM-dd HH:mm:ss'
    )
  ) AS statestartdatestr
FROM processmining.InstanceUserPerformance LIMIT 100
Derek Plautz
  • 108
  • 1
  • 5
1

I had the same issues in my javascript application. The 'year' part of the date string was 5 digits, as shown below. The reason for this was that I was trying to convert the timestamp to 'ms' but it was already given in 'ms'.

I'd suggest that you double check whether your timestamp is in seconds or milliseconds. Try removing any code that does millisecond conversion and see if that helps.

sammy_boi
  • 11
  • 3