Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive >> mail # user >> Syntax for filters on timstamp data type


Copy link to this message
-
Re: Syntax for filters on timstamp data type
great

so can we assume that equals comparison can auto cast but not the in  range
statement ?
On Mon, Apr 8, 2013 at 7:08 PM, LUTTER, Steffen <[EMAIL PROTECTED]>wrote:

>  Hi Mark,****
>
> ** **
>
> Correct, I just did some tests and the cast is the way to go. While for
> comparison operations (equal, diff, …) implicit casts work, this is not the
> case for the IN clause. I think it should, as eventually this just
> translates to a disjunction of comparisons so it should be the same. ****
>
> ** **
>
> Anyway, I have a working solution now. For the record I paste two working
> example queries below.****
>
> ** **
>
> Thanks a lot for your help !!!****
>
> ** **
>
> Steffen****
>
> ** **
>
> Example 1: SELECT * FROM  table1 WHERE datecol  =  CAST('2009-01-17
> 00:00:00' AS timestamp)****
>
> Example 2: SELECT * FROM  table1 WHERE datecol IN (CAST ('2009-01-11
> 00:00:00' AS timestamp), CAST ('2009-01-08 00:00:00' AS timestamp) )****
>
> ** **
>
> *From:* Mark Grover [mailto:[EMAIL PROTECTED]]
> *Sent:* 05 April 2013 18:43
>
> *To:* [EMAIL PROTECTED]
> *Subject:* Re: Syntax for filters on timstamp data type****
>
> ** **
>
> Steffan,****
>
> One thing that may be different is that equal can cast operands to make
> equals work but that may not be true for IN. FWIW, this is me just
> speculating, I haven't looked at the code just yet.****
>
> ** **
>
> Perhaps, you could explicit casting to get around this?****
>
> On Fri, Apr 5, 2013 at 7:36 AM, LUTTER, Steffen <[EMAIL PROTECTED]>
> wrote:****
>
> 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:[EMAIL PROTECTED]]
> *Sent:* 05 April 2013 16:11
> *To:* [EMAIL PROTECTED]
> *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 <[EMAIL PROTECTED]>
> 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')****

Nitin Pawar
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB