CODEWITHSUNDEEP
sqldatetimehadoophive
Denis Plotnikov
Denis Plotnikov

Reputation: 105

date or string type to bigint

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

Upvotes: 2

Views: 5048

Answers (3)

O. Jones
O. Jones

Reputation: 108651

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.

Upvotes: 2

Bhima Rao Gogineni
Bhima Rao Gogineni

Reputation: 267

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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)

Upvotes: 2

Related Questions