Converting only time to unixtimestamp in Hive

Question

I have a column eventtime that only stores the time of day as string. Eg: 0445AM - means 04:45 AM. I am using the below query to convert to UNIX timestamp.

select unix_timestamp(eventtime,'hhmmaa'),eventtime from data_raw limit 10;

This seems to work fine for test data. I always thought unixtimestamp is a combination of date and time while here I only have the time. My question is what date does it consider while executing the above function? The timestamps seem to be quite small.

Answer 1

Unix timestamp is the bigint number of seconds from Unix epoch (1970-01-01 00:00:00 UTC). The unix time stamp is a way to track time as a running total of seconds.

select unix_timestamp('0445AM','hhmmaa') as unixtimestamp

Returns

17100

And this is exactly 4hrs, 45min converted to seconds.

select 4*60*60 + 45*60

returns 17100

And to convert it back use from_unixtime function

select from_unixtime (17100,'hhmmaa') 

returns:

0445AM

If you convert using format including date, you will see it assumes the date is 1970-01-01

select from_unixtime (17100,'yyyy-MM-dd hhmmaa') 

returns:

1970-01-01 0445AM

See Hive functions dosc here.

Also there is very useful site about Unix timestamp