Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Comparing date type column results not properly

Comparing date type column results not properly

Contributor

Hello, I'm jonggyun.

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

But actually, there are 3,122 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:lnxxxxn1503: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:lnxxxxn1503: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:lnxxxxn1503: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)

Thanks in advance.

Don't have an account?
Coming from Hortonworks? Activate your account here