Equal, not equal, less than, less or equal, greater than, greater or equal all work. Also the function execution in the IN clause seems to work, as the error message states that the result type is bigint. Following the error message, it expects the input as timestamp, but I couldn't find a syntax to express timestamps in HiveQL.
Two questions remain: 1) How to express timestamps in HiveQL? 2) Why doesn't the IN clause support comparisons between timestamp and bigint, if "equal" and so on does? Thanks for any thought in this, Steffen From: Nitin Pawar [mailto:nitinpawar...@gmail.com] Sent: 05 April 2013 16:11 To: user@hive.apache.org Subject: Re: Syntax for filters on timstamp data type I am not sure IN clause supports executing functions in the query did it fail when you tried less than greater than type On Fri, Apr 5, 2013 at 7:36 PM, LUTTER, Steffen <steffen.lut...@sap.com<mailto:steffen.lut...@sap.com>> wrote: Hi, I have a question regarding filters on timestamps. The syntax seems to be UNIX_TIMESTAMP('yyyy-MM-dd hh:mm:ss'), is there another way to express a datetime type? The problem is that I get an exception when using the IN <list> syntax, while the equal comparison works without problems. Example: SELECT * FROM table1 WHERE datecol IN ( UNIX_TIMESTAMP('2009-01-05 00:00:00'),UNIX_TIMESTAMP('2009-01-10 00:00:00') ) Throws exception: Caused by: java.sql.SQLException: Query returned non-zero code: 10014, cause: FAILED: SemanticException [Error 10014]: Line 5:21 Wrong arguments ''2009-01-10 00:00:00'': The arguments for IN should be the same type! Types are: {timestamp IN (bigint, bigint)} at org.apache.hadoop.hive.jdbc.HiveStatement.executeQuery(HiveStatement.java:189) at org.apache.hadoop.hive.jdbc.HiveStatement.execute(HiveStatement.java:127) at com.sap.connectivity.cs.java.drivers.jdbc.JDBCAPI$Statement.execute(JDBCAPI.java:1648) at com.sap.connectivity.cs.java.drivers.jdbc.JDBCDriver.prepare(JDBCDriver.java:1760) ... 15 more Following query works: SELECT * FROM table1 WHERE datecol = UNIX_TIMESTAMP('2009-01-17 00:00:00') Is there another syntax for datetime types? Could it be a bug in the filter "IN <list>" operation? Thanks in advance, Steffen -- Nitin Pawar
