Phoenix function in the Where clause

[#1]

We have a phoenix table with "key" as time in millisec. I am trying to get count of number of records for the last 10 minutes by using Phoenix functions in the where clause, but not going anywhere. It just returns the zero count.

Anyone tried functions on the where clause something like below ?

select count(*) from CDC where CDC."key" > TO_CHAR((TO_NUMBER(NOW())-600000);

-Datta

[#2]

Can you share your table schema, please? Also, it might help you to do something like the following to help debug

select key, TO_CHAR(TO_NUMBER(NOW())-600000) from CDC;

[#3]

yes , functions in where clause works perfectly.

I think to_char() will be giving epoch with commas

[#4]

Hi Josh, Thanks for the response. It works fine in the select query. I built the function by putting it in select query something like below. 0: jdbc:phoenix:lnxhdpdp07.smrcy.com,lnxhdpdp> select "key", TO_CHAR(TO_NUMBER(NOW())-600000) as TM from CDC LIMIT 5;

+------------------------------------------+-------------------+ | key | TM | +------------------------------------------+-------------------+ | 1460067042710,EPT,26|T|Z978926||1 | 1,460,734,960,474 | | 1460067042710,EPT,26|T|Z978926||8 | 1,460,734,960,474 | | 1460067042711,EPT,26|T|Z978926||1 | 1,460,734,960,474 | | 1460067042711,EPT,26|T|Z978926||8 | 1,460,734,960,474 | | 1460067042712,EPT,26|T|Z978926||1 | 1,460,734,960,474 | +------------------------------------------+-------------------+

Also works fine, If I put the hardcoded value in the function part of where clause ... 0: jdbc:phoenix:lnxhdpdp07.smrcy.com,lnxhdpdp> SELECT COUNT(*) FROM CDC WHERE "key"> '1460734960474'; +------------------------------------------+ | COUNT(1) | +------------------------------------------+ | 539753 | +------------------------------------------+ Also tried putting it in "where (regexp_split(CDC."key",',')[1]) > TO_CHAR((TO_NUMBER(NOW())-60000)" didn't work. DDL is as below : DROP VIEW IF EXISTS CDC; CREATE VIEW CDC ( "key" VARCHAR primary key ) default_column_family='d';

[#5]

"where (regexp_split(CDC."key",',')[1]) > TO_CHAR((TO_NUMBER(NOW())-60000)"

You want to be doing the regexp_split on the TO_CHAR side of that expression, not the key side. Like Ankit pointed out, TO_CHAR is putting the commas in the number while your key does not have commas.

However, a comma sorts before all of the numbers so I would have thought that you would get all records (instead of none). Still, I think I would try fixing your WHERE clause to make sure you comparing numbers without commas on both sides of the expression.

[#6 · Accepted solution]

Thanks @asinghal & @Josh Elser . The following query fixed it..

select count(*) from CDC WHERE "key" > TO_CHAR(TO_NUMBER(NOW())-600000,'#############');

Appreciate your quick help.

-Datta