Reply
Explorer
Posts: 16
Registered: ‎05-22-2016

[Phoenix] Comparing date type column results not properly

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)

Announcements

Currently incubating in Cloudera Labs:

Envelope
HTrace
Ibis
Impyla
Livy
Oryx
Phoenix
Spark Runner for Beam SDK
Time Series for Spark
YCSB