Cloudera Labs
Provide feedback on Cloudera Labs
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here. Want to know more about what has changed? Check out the Community News blog.

[Phoenix] Comparing date type column results not properly

Highlighted

[Phoenix] Comparing date type column results not properly

Contributor

Hello, I'm jonggyun.

 

I had posted this problem before but not solved yet.

(you can check this link.

https://community.cloudera.com/t5/Cloudera-Labs/Phoenix-date-column-value-does-not-compare-properly/...

)

 

When I execute below Query#1, it returns null result.

But actually, there are 3122 rows between '2015-04-12 01:00:00' and '2015-04-21 01:10:00'.

I have checked the result of Query#2 by changing the date value to '2015-04-21 01:00:00.001' .

 

I'd like to know if this is a bug or there is any way to fix this problem.

 

Query#1

0: jdbc:phoenix:lnactnn1503:2181> select count(*) as cnt from SNS_ID_HISTORY where operated between to_date('2015-04-21 01:00:00') and to_date('2015-04-21 01:10:00');
+------+
| CNT |
+------+
+------+
No rows selected (0.013 seconds)

 

Query#2
0: jdbc:phoenix:lnactnn1503:2181> select count(*) as cnt from SNS_ID_HISTORY where operated between to_date('2015-04-21 01:00:00.001') and to_date('2015-04-21 01:10:00');
+-------+
| CNT |
+-------+
| 3122 |
+-------+
1 row selected (0.081 seconds)

 

Query#3
0: jdbc:phoenix:lnactnn1503:2181> select operated from SNS_ID_HISTORY where operated = to_date('2015-04-21 01:00:00');
+--------------------------+
| OPERATED |
+--------------------------+
| 2015-04-21 01:00:00.000 |
| 2015-04-21 01:00:00.000 |
| 2015-04-21 01:00:00.000 |
| 2015-04-21 01:00:00.000 |
| 2015-04-21 01:00:00.000 |
+--------------------------+
5 rows selected (0.063 seconds)