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?
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.