Dfyjkt

How can I convert date like '2018-03-31' into bigint in Hive?

What Gordon said.

If you have Javascript timestamps, keep in mind that they are simply the number of milliseconds since 1970-01-01T00:00:00.000Z in 64-bit floating point. They can be converted to BIGINT easily. If you're storing those timestamps in DATETIME(3) or TIMESTAMP(3) data types, use UNIX_TIMESTAMP(date)*1000 to get a useful BIGINT millisecond value.

If you only care about dates (not times) you can use TO_DAYS() to get an integer number of days since 0000-01-01 (in the Gregorian calendar; if you're an historian of antiquity and care about the Julian calendar, this approach has problems. If you don't know what I'm talking about, you don't need to worry.) But INT will suffice for these day numbers; BIGINT is overkill.

You could do:

select year(date) * 10000 + month(date) * 100 + day(date)

This produces an integer that represents the date.

If you want a Unix timestamp (seconds since 1970-01-01), then:

select unix_timestamp(date)

You can use unix_timestamp function which converts date or timestamp to a Unix timestamp and returns as a bigint.

Example query:

select unix_timestamp('2018-03-31', 'yyyy-MM-dd');

Output:

+--------------------------------------+
|unix_timestamp(2018-03-31, yyyy-MM-dd)|
+--------------------------------------+
| 1522434600|
+--------------------------------------+

Note: Tested this code in Hive 1.2.0