CODEWITHSUNDEEP
sqlhadoopapache-sparkhive
Simon Su
Simon Su

Reputation: 2343

Why cast as timestamp give out two different result

I have a hive table with two rows like this:

0: jdbc:hive2://localhost:10000/default> select * from t2;
+-----+--------+
| id  | value  |
+-----+--------+
| 10  | 100    |
| 11  | 101    |
+-----+--------+
2 rows selected (1.116 seconds)

but when I issue a query :

select cast(1 as timestamp) from t2;

it gives out unconsistent result, can anyone tell me the reason ?

0: jdbc:hive2://localhost:10000/default> select cast(1 as timestamp) from t2;
+--------------------------+
|           _c0            |
+--------------------------+
| 1970-01-01 07:00:00.001  |
| 1970-01-01 07:00:00.001  |
+--------------------------+
2 rows selected (0.913 seconds)
0: jdbc:hive2://localhost:10000/default> select cast(1 as timestamp) from t2;
+--------------------------+
|           _c0            |
+--------------------------+
| 1970-01-01 08:00:00.001  |
| 1970-01-01 07:00:00.001  |
+--------------------------+
2 rows selected (1.637 seconds)

Upvotes: 1

Views: 109

Answers (1)

apc
apc

Reputation: 141

I can't reproduce your problem, which Hive version are you using? Hive had a bug with timestamp and bigint (see https://issues.apache.org/jira/browse/HIVE-3454), but it doesn't explain your problem. For example Hive 0.14 gives different results for

SELECT (cast 1 as timestamp), cast(cast(1 as double) as timestamp) from my_table limit 5;

Upvotes: 1

Related Questions