Search code examples
gemfiregeode

geode oql order by a string and to_date

I have a region which contains transaction times input as strings in the format yyyy-MM-dd HH:mm:ss.fff and I'd like to be able to run an OQL query with results ordered by transaction time. Whether as a string or a date...

If I run the order by then the results are not coming back ordered.

query --query="select distinct d.value FROM /deal.entries d order by d.TransactTime"

The string TransactTime is entirely random. So I try to translate the string value back to date by the TO_DATE keyword. Something like:

query --query="select distinct d.value FROM /deal.entries d order by TO_DATE(d.TransactTime,'yyyy-MM-dd HH:mm:ss.fff')"

This is giving the error Query is invalid due for error : <Syntax error in query: expecting StringLiteral, found 'TransactTime'>

What am I missing here please?

Solution

  • I see that you are querying from /deal.entries - that means you are querying (key,value) pairs, not just the value. So maybe you need to use d.value.TransactTime.