This is possible, but is it useful? I may be missing a technique for using a date/time value in queries?
After creating an index on Update_Time field, doing a find by key for a value like "06/26/2014 11:11:25 13 am", which does exist in the data, goes to the first record in the file. Of course the search value was a character string vs. the index being a time(?) value. Find by key for ctodt("06/26/2014 11:11:25 13 am") goes to the last record in the file, so no better.
I used query genie to create the following
between(update_time,ctodt("06/25/2014 9:00:00 00 am"),ctodt("06/26/2014 11:59:00 00 am"))
This returns ALL records on 06/25/2014, not just the ones in the morning. It also does a full scan of the table, taking 35 sec. In contrast, doing a between() query on a character field takes < 1 sec. The table has 300K records.
Next step is to create a character index for Update_time like the following, using a 24 hour clock.
TIME("yyyy/MM/dd 0h:0m:0s.2",UPDATE_TIME)
Then find by key does find the correct record instantly. But a query for a range of records still does a full scan of the table, requiring 30+ seconds.
t=table.open("person")
i = t.query_create("","between(time(\"yyyy/MM/dd 0h:0m:0s.2\",update_time),\"2014/06/24 09:00:00.00\",\"2014/06/24 13:59:00.00\")","")
Anyone had better luck getting quick response from a query on a time field?
Bill.
After creating an index on Update_Time field, doing a find by key for a value like "06/26/2014 11:11:25 13 am", which does exist in the data, goes to the first record in the file. Of course the search value was a character string vs. the index being a time(?) value. Find by key for ctodt("06/26/2014 11:11:25 13 am") goes to the last record in the file, so no better.
I used query genie to create the following
between(update_time,ctodt("06/25/2014 9:00:00 00 am"),ctodt("06/26/2014 11:59:00 00 am"))
This returns ALL records on 06/25/2014, not just the ones in the morning. It also does a full scan of the table, taking 35 sec. In contrast, doing a between() query on a character field takes < 1 sec. The table has 300K records.
Next step is to create a character index for Update_time like the following, using a 24 hour clock.
TIME("yyyy/MM/dd 0h:0m:0s.2",UPDATE_TIME)
Then find by key does find the correct record instantly. But a query for a range of records still does a full scan of the table, requiring 30+ seconds.
t=table.open("person")
i = t.query_create("","between(time(\"yyyy/MM/dd 0h:0m:0s.2\",update_time),\"2014/06/24 09:00:00.00\",\"2014/06/24 13:59:00.00\")","")
Anyone had better luck getting quick response from a query on a time field?
Bill.
Comment