I want to convert the date which is in '2015-05-09T09:00:12.123462000' format to the unix timestamp in hive. The UNIX_TIMESTAMP('2015-05-09T09:00:12.123462000') doesn't work. I am not sure how i can convert this. I need this to compare two dates in different format. I am converting both the dates to unix timestamp but this fails. can someone please help with this.
Thanks
Your input uses the full ISO 8601 format, with a "T" between date and time, and fractional seconds. Hive expects an SQL format (i.e. with a space between date and time) as seen in java.sql.Timestamp and ODBC, with or without fractional seconds, as stated in the Hive documentation.
Just apply some very elementary string massaging -- then "cast" the String to a Hive Timestamp. And pleeease forget that lame roundtrip to and from UNIX_TIMESTAMP:
cast(regexp_replace('2015-05-09T09:00:12.123462000', 'T',' ') as Timestamp)
The Answer by Samson Scharfrichter is correct and should be accepted. I'll just add some words about java.time types.
java.time.InstantThe java.time classes supplant the troublesome old legacy date-time classes such as java.sql.Timestamp.
The Instant class represents a moment on the timeline in UTC with a resolution of nanoseconds (up to nine (9) digits of a decimal fraction).
Your input string complies with the ISO 8601 standard. The java.time classes use ISO 8601 formats by default when parsing/generating strings that represent date-time values. So no need to specify a formatting pattern. You can directly parse your string as an Instant object.
Your input string lacks an indication of offset-from-UTC or time zone. If it was intended to be in UTC, append a Z for Zulu which means UTC.
Instant instant = Instant.parse( "2015-05-09T09:00:12.123462000" + "Z" );
You can generate such a string, merely call toString. The default formatter used by toString prints the decimal fraction in groups of three digits as needed. In this example the last three digits are zeros so they are omitted.
String output = instant.toString();
2015-05-09T09:00:12.123462Z
To make this into SQL-style string expected by Hive, replace the T with a SPACE and remove the Z.
String inputForHive = output.replace( "T" , " " ).replace( "Z" , "" );
2015-05-09 09:00:12.123462
Hive also provides for conversions from:
1970-01-01T00:00:00Z).The second one I suggest you avoid. The floating-point types such as float, Float, double, and Double in Java purposely trade off accuracy for faster execution time. This often results in extraneous digits at the end of your decimal fraction. If you need fractional second, stick with the String type & Instant object.
The first one can easily be obtained from an Instant by calling the getEpochSecond method. Of course this means data loss as this method leaves behind any fractional second.
long secondsSinceEpoch = instant.getEpochSecond();
Going the other direction.
Instant instant = Instant.ofEpochSecond( secondsSinceEpoch );
Once you have your Instant objects, you can compare with methods such as compareTo, equals, isBefore, isAfter.
Boolean happenedBefore = thisInstant.isBefore( thatInstant );
The java.time framework is built into Java 8 and later. These classes supplant the troublesome old date-time classes such as java.util.Date, .Calendar, & java.text.SimpleDateFormat.
The Joda-Time project, now in maintenance mode, advises migration to java.time.
To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations.
Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport and further adapted to Android in ThreeTenABP (see How to use…).
The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.
