Support Questions
Find answers, ask questions, and share your expertise

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?