CODEWITHSUNDEEP
hiveapache-spark-sql
leeyuiwah
leeyuiwah

Reputation: 7162

Hive/SparkSQL: How to convert a Unix timestamp into a timestamp (not string)?

I thought this would be easy ...

In Hive/SparkSQL, how do I convert a unix timestamp [Note 1] into a timestamp data type?

(Note 1: That is, number of seconds/milliseconds since Jan 1, 1970)

I thought from_unixtime() would do that, but it gives back a string instead of a timestamp. The following experiment illustrates the problem

Step 0: Preparation

select 
  from_unixtime(1508673584) as fut;

Result:

-----------------------
| fut                 |
| ------------------- |
| 2017-10-22 11:59:44 |
-----------------------

Step 1: Create a table with the result of from_unixtime()

create table test
select 
  from_unixtime(1508673584) as fut;

Step 2: Examine the datatype of the column fut

describe test;

Result:

----------------------------------
| col_name | data_type | comment |
| -------- | --------- | ------- |
| fut      | string    | <null>  |
----------------------------------

I also tried this

select 
  from_utc_timestamp(1508618794*1000, 'EDT');

According to the manual (link here), this should work. Because it states that:

Coverts a timestamp* in UTC to a given timezone (as of Hive 0.8.0). * timestamp is a primitive type, including timestamp/date, tinyint/smallint/int/bigint, float/double and decimal. Fractional values are considered as seconds. Integer values are considered as milliseconds.. E.g from_utc_timestamp(2592000.0,'PST'), from_utc_timestamp(2592000000,'PST') and from_utc_timestamp(timestamp '1970-01-30 16:00:00','PST') all return the timestamp 1970-01-30 08:00:00

However, I got an error of 

Error: org.apache.spark.sql.AnalysisException: 
  cannot resolve 'from_utc_timestamp((1508618794 * 1000), 'EDT')' 
  due to data type mismatch: 
  argument 1 requires timestamp type, 
  however, '(1508618794 * 1000)' is of int type.; line 2 pos 2;
'Project [unresolvedalias(from_utc_timestamp((1508618794 * 1000), EDT), None)]
+- OneRowRelation$

SQLState:  null
ErrorCode: 0

Upvotes: 2

Views: 8379

Answers (2)

Lavanya varma
Lavanya varma

Reputation: 75

create table test AS select cast(from_unixtime(1508673584) as timestamp) as fut;

Upvotes: 1

leeyuiwah
leeyuiwah

Reputation: 7162

(I am providing an answer myself here.)

The answer is to use cast(). This works for both date and timestamp

select 
  from_unixtime(1508673584)                    as fut,
  cast(from_unixtime(1508673584) as date)      as futAsDate,
  cast(from_unixtime(1508673584) as timestamp) as futAsTimestamp;

Result:

------------------------------------------------------------
| fut                 | futAsDate  | futAsTimestamp        |
| ------------------- | ---------- | --------------------- |
| 2017-10-22 11:59:44 | 2017-10-22 | 2017-10-22 11:59:44.0 |
------------------------------------------------------------

Verification of data types

create table test2
select 
  from_unixtime(1508673584)                    as fut,
  cast(from_unixtime(1508673584) as date)      as futAsDate,
  cast(from_unixtime(1508673584) as timestamp) as futAsTimestamp;

And then

describe test2;

Result:

----------------------------------------
| col_name       | data_type | comment |
| -------------- | --------- | ------- |
| fut            | string    | <null>  |
| futAsDate      | date      | <null>  |
| futAsTimestamp | timestamp | <null>  |
----------------------------------------

Upvotes: 2

Related Questions