CODEWITHSUNDEEP
sqlstringdatetimehive
marlon
marlon

Reputation: 7713

How to get a datetime in a format from hive query?

hql="
  select DISTINCT b.mid, from_unixtime(b.create_time,'yyyy-MM-dd hh.mm.ss'), to_id from
      (select mid, create_time, to_id, dt from mark_table where dt>=$dt1 and dt<=$dt2 and to_id = 'Category_001' ) b
   
  "

I have a hive query above, and the 'b.create_time' is a column with date, but the default time format of this column is like this:

1594553700

I think it is time in seconds? I hope to convert it using this:

from_unixtime(b.create_time,'yyyy-MM-dd hh.mm.ss')

But it gives an error, saying that b.create_time can't be a string for that function to work.

How to deal with this?

Upvotes: 1

Views: 670

Answers (1)

GMB
GMB

Reputation: 222652

1594553700 does look like a Unix timestamp (the number of seconds since January 1st, 1970): it corresponds to Sunday, July 12, 2020 11:35:00 AM.

from_unixtime() is the right function to turn it to a formatted date string. But it seems like you are storing this timestamp as a string (which is definitely not a good idea), while the function wants a number as argument.

Just explicitly cast to the correct datatype:

from_unixtime(cast(b.create_time as bigint),'yyyy-MM-dd hh.mm.ss')

Upvotes: 1

Related Questions