how to convert unix epoch time to date string in hive

Question

I have a log file which contains timestamp column. The timestamp is in unix epoch time format.

I want to create a partition based on a timestamp with partitions year, month and day.

So far I have done this but it is throwing an error.

PARSE ERROR cannot recognize input '(' in column type

Here is my code.

from (
      from raw_data
            MAP  ${PREFIX}raw_data.line
            USING 's3://scripts/clean.py'
            AS (timestamp STRING, name STRING)
      ) map_out
INSERT OVERWRITE TABLE date_base_data_temp PARTITION(year(timestamp), month(timestamp)), day(timestamp))) 
    select map_out.name;

score 59 · Accepted Answer · edited Sep 07 '17 at 14:24

59

Oof, that looks ugly. Try using this function in Hive:

SELECT from_unixtime(unix_timestamp) as new_timestamp from raw_data ...

Or if timestamp is in ms instead of seconds:

SELECT from_unixtime(unix_timestamp DIV 1000) as new_timestamp from raw_data ...

That converts a unix timestamp into a YYYY-MM-DD HH:MM:SS format, then you can use the following functions to get the year, month, and day:

SELECT year(new_timestamp) as year, month(new_timestamp) as month, day(new_timestamp) as day ...

edited Sep 07 '17 at 14:24

chhantyal

11,874
7
51
77

answered Sep 23 '11 at 16:01

Aswin

658
6
2

Thanks! Saved a lot of my time. This is exactly what I was looking for! – Mahalakshmi Lakshminarayanan Mar 31 '15 at 03:48
4

Make sure the `timestamp_value` (here `unix_timestamp`) is in seconds otherwise use `from_unixtime(timestamp_value DIV 1000)` – shriyog Jan 31 '17 at 13:05
I am getting only time till second but i want ms also . how do i do that ? – Avinash Nishanth S Oct 13 '17 at 09:34
@shriyog I have the date in epoch format. The value is 1513708200000. Kindly help me out with the query to convert the value into date format – Jon Andrews Nov 20 '18 at 07:49
@BasilPaul As I have mentioned, you need to convert your timestamp value to seconds & then pass - `from_unixtime(timestamp_value DIV 1000)` – shriyog Nov 20 '18 at 08:30
select FROM_UNIXTIME(cast(activation_date as bigint),'dd-MM-yyyy') from oct_pre_profile limit 5; – Jon Andrews Nov 20 '18 at 08:50
04-07-49937 The output for year is weird. Could you help me out with the problem – Jon Andrews Nov 20 '18 at 08:51
I already suggested to convert your value into seconds `select FROM_UNIXTIME(cast(activation_date as bigint) DIV 1000,'dd-MM-yyyy')` – shriyog Nov 20 '18 at 09:00
Okay. Appreciate your support – Jon Andrews Nov 20 '18 at 09:10

score 12 · Answer 2 · answered Jun 14 '16 at 22:07

12

With more recent releases of Hive and SparkSQL, data type of date and type casting options are available. Following should work in Hive as well as Spark SQL

SELECT cast(from_unixtime(epoch_datetime) as date) from myHiveTable

answered Jun 14 '16 at 22:07

inder

131
1
4

Abdul Mannan · Answer 3 · 2019-11-13T05:58:15.800

10

If you need to convert the date in custom format, use this:

select date_format(from_unixtime(epoch_datetime),'yyyyMM') as formatted_date from myHiveTable;

which will return the date as yearMonth e.g. 201708

edited Nov 13 '19 at 05:58

answered Aug 11 '17 at 10:54

Abdul Mannan

1,072
12
19

I think you may have left out one `y` in the format specifier. – NYCeyes Feb 02 '19 at 02:56

score 4 · Answer 4 · answered Dec 27 '17 at 00:15

4

Adding this query to the list where the timestamp needs to be converted to date string yyyy-MM-dd for a string partition:

hive> select date_format(from_unixtime(epoch_datetime), 'yyyy-MM-dd') as day from table_name limit 20;

-- If required, remove the millis precision for timestamps
hive> select date_format(from_unixtime(cast(epoch_datetime/1000 as bigint)), 'yyyy-MM-dd') as day from table_name limit 20;

answered Dec 27 '17 at 00:15

Yash Sharma

1,674
2
16
23

2

`date_format` is not necessary, `from_unixtime` can receive another format argument: `select from_unixtime(epoch_datetime, 'yyyy-MM-dd') as day from table_name limit 20;` https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF – Julian Qian Sep 05 '18 at 15:44
@JulianQian You are correct. I used your more succinct version, and it works fine. – NYCeyes Feb 02 '19 at 03:14

Loganathan .R · Answer 5 · 2018-10-08T12:37:40.837

-1

select order_id, date_format(from_unixtime(order_date/1000),'yyy-MM-dd') as order_date ,order_customer_id,order_status
from orders

or if you see any error on the same , try to use select order_id, date_format(from_unixtime(order_date DIV 1000),'yyy-MM-dd') as order_date ,order_customer_id,order_status from orders

edited Oct 08 '18 at 12:37

answered Oct 07 '18 at 14:39

Loganathan .R

21
8

how to convert unix epoch time to date string in hive

5 Answers5