0

I'm trying to convert a string date "Sat Jan 25 00:13:31 +0000 2014" to Unix Timestap using unix_timestamp() HiveQL function.

Convert time string with given pattern to Unix time stamp (in seconds), return 0 if fail: unix_timestamp('2009-03-20', 'yyyy-MM-dd') = 1237532400

Java Doc http://docs.oracle.com/javase/6/docs/api/java/text/SimpleDateFormat.html#month

unix_timestamp("Jan-2014","MMM-yyyy") in Hive returns NULL

The function seems to work only with months expressed with an integer (ie MM). Solutions?

Community
  • 1
  • 1
Adriano Foschi
  • 648
  • 1
  • 8
  • 23

3 Answers3

0

Seems incredible but with other months except January (Jan) it works...

unix_timestamp('Feb 1 18:41:57 +0000 2014','MMM dd HH:mm:ss Z yyyy')
==> 1391280117

the EEE pattern for day of week doesn't work...

Adriano Foschi
  • 648
  • 1
  • 8
  • 23
0

Solved, the problem was the localization of my linux System.

Adriano Foschi
  • 648
  • 1
  • 8
  • 23
0

Using java.time

On the Java side, parse using java.time classes. The YearMonth class represents, well, a year and a month.

DateTimeFormatter f = DateTimeFormatter.ofPattern( "MMM-yyyy" );
YearMonth ym = YearMonth.parse( "Jan-2014" , f );

We can get the first day of the month from that, to get a LocalDate.

LocalDate today = ym.atDay( 1 );

If you want a date-only value for Hive, you feed a string in standard SQL format. For a date-only value, SQL format coincides with ISO 8601 format. The java.time classes use standard ISO 8601 formats by default when parsing or generating strings.

String output = today.toString(); // YYYY-MM-DD format per ISO 8601 standard.

From there you apparently want a date-time value. We can arbitrarily assign the first moment of the day. The meaning of a date and determining the first moment depends on a time zone. For any given moment, the date and time-of-day vary around the globe by time zone.

ZoneId zoneId = ZoneId.of( "America/Montreal" );
ZonedDateTime zdt = today.atStartOfDay( z );

From that you can generate a number of whole seconds since the epoch of beginning of 1970 in UTC (1970-01-01T00:00:00Z) to feed to Hive.

long secondsSinceEpoch = zdt.toEpochSecond();

1388534400

Going the other direction, you will need to go through the Instant class. 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).

Instant instant = Instant.ofEpochSecond( 1_388_534_400L );

Apply a time zone if you want to view the wall-clock time of some region.

ZoneId zoneId = ZoneId.of( "America/Montreal" );
ZonedDateTime zdt = instant.atZone( z );

About java.time

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.

Community
  • 1
  • 1
Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154