2

I'm trying to read a timestamp and reconstruct it in a different format as per my requirement, in Hive. But, I can't seem to get the month and date right. I suspect timestamp format to be incorrect but not sure where I'm going wrong.

Query

select from_unixtime(unix_timestamp("Sun Mar 28 19:51:10 GMT+05:30 2021", "EEE MMM dd HH:mm:ss z YYYY"), "HH:mm:ss dd-MM-YYYY-z")

Output

14:21:10 27-12-2021-UTC
leftjoin
  • 36,950
  • 8
  • 57
  • 116
codeseeker
  • 85
  • 2
  • 5

1 Answers1

2

Read the SimpleDateFormat class docs which is used under the hood of unix_timestamp and from_unixtime.

y - is a year ---this is what you need Y - is week year ---this is what you used in a pattern

See also https://errorprone.info/bugpattern/MisusedWeekYear And some explanation what is week year here: https://docs.oracle.com/javase/7/docs/api/java/util/GregorianCalendar.html#week_year

The week year for short is the year that the relevant week number belongs to.

Fixed:

select from_unixtime(unix_timestamp("Sun Mar 28 19:51:10 GMT+05:30 2021", "EEE MMM dd HH:mm:ss z yyyy"), "HH:mm:ss dd-MM-yyyy-z")

Result:

14:21:10 28-03-2021-UTC
leftjoin
  • 36,950
  • 8
  • 57
  • 116
  • Thank you @leftjoin, I'm able to get the timestamp correctly now, after changing from YYYY to yyyy. – codeseeker Nov 04 '21 at 15:29
  • 1
    This document `https://help.gooddata.com/cloudconnect/manual/date-and-time-format.html` google's top favorite is all wrong. I believe `https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html` should be the proper document to follow. – Popeye Nov 05 '21 at 01:00