Avro tojson date format

Question

I imported table with selected columns using sqoop to avro file format. Using avro-tools tojson the date appear in strange format (negetive). How can I decode date ?

    {"first_name":{"string":"Mary"},"last_name":  {"string":"Botman"},"birth_date":{"long":-345772800000}}

where as MySQL query fives correct format

  mysql> select first_name, last_name, birth_date from employees where first_name like 'Mary' and last_name ='Botman';
  +------------+-----------+------------+
  | first_name | last_name | birth_date |
  +------------+-----------+------------+
  | Mary       | Botman    | 1959-01-17 |
  +------------+-----------+------------+
  1 row in set (0.07 sec)

Answer 1

The long value -345772800000 represents the ...

...specified number of milliseconds since the standard base time known as "the epoch", namely January 1, 1970, 00:00:00 GMT the number of milliseconds since 1 Jan 1970

In your example, it's a negative value so it's counting backwards in time from "the epoch". In Java code you can create a LocalDate from this value as follows, which will give you the same result as shown in your Hive query results.

LocalDate date17Jan1959 = Instant.ofEpochMilli(-345772800000L)
    .atZone(ZoneOffset.UTC)
    .toLocalDate();