Re: Syntax for filters on timstamp data type
Glad to hear, Steffan. Do you mind creating a JIRA for this at
https://issues.apache.org/jira?
Thanks in advance!
Mark
On Mon, Apr 8, 2013 at 6:38 AM, LUTTER, Steffen steffen.lut...@sap.comwrote:
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:grover.markgro...@gmail.com]
*Sent:* 05 April 2013 18:43
*To:* user@hive.apache.org
*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 steffen.lut...@sap.com
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: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
wrote:
Hi,
I have a question regarding filters on timestamps. The syntax seems to be
UNIX_TIMESTAMP('-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
** **
RE: Syntax for filters on timstamp data type
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:grover.markgro...@gmail.com]
Sent: 05 April 2013 18:43
To: user@hive.apache.org
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
steffen.lut...@sap.commailto:steffen.lut...@sap.com 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:nitinpawar...@gmail.commailto:nitinpawar...@gmail.com]
Sent: 05 April 2013 16:11
To: user@hive.apache.orgmailto: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.commailto:steffen.lut...@sap.com wrote:
Hi,
I have a question regarding filters on timestamps. The syntax seems to be
UNIX_TIMESTAMP('-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
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 steffen.lut...@sap.comwrote:
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:grover.markgro...@gmail.com]
*Sent:* 05 April 2013 18:43
*To:* user@hive.apache.org
*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 steffen.lut...@sap.com
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: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
wrote:
Hi,
I have a question regarding filters on timestamps. The syntax seems to be
UNIX_TIMESTAMP('-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
** **
--
Nitin Pawar
RE: Syntax for filters on timstamp data type
Exactly
From: Nitin Pawar [mailto:nitinpawar...@gmail.com]
Sent: 08 April 2013 16:06
To: user@hive.apache.org
Subject: 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
steffen.lut...@sap.commailto:steffen.lut...@sap.com 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:grover.markgro...@gmail.commailto:grover.markgro...@gmail.com]
Sent: 05 April 2013 18:43
To: user@hive.apache.orgmailto:user@hive.apache.org
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
steffen.lut...@sap.commailto:steffen.lut...@sap.com 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:nitinpawar...@gmail.commailto:nitinpawar...@gmail.com]
Sent: 05 April 2013 16:11
To: user@hive.apache.orgmailto: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.commailto:steffen.lut...@sap.com wrote:
Hi,
I have a question regarding filters on timestamps. The syntax seems to be
UNIX_TIMESTAMP('-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
--
Nitin Pawar
Syntax for filters on timstamp data type
Hi,
I have a question regarding filters on timestamps. The syntax seems to be
UNIX_TIMESTAMP('-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
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.comwrote:
Hi,
** **
I have a question regarding filters on timestamps. The syntax seems to be
UNIX_TIMESTAMP('-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
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 steffen.lut...@sap.comwrote:
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
wrote:
Hi,
I have a question regarding filters on timestamps. The syntax seems to be
UNIX_TIMESTAMP('-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
