zeesql with ISO 8601

[awoodevpub]

Good evening,

I was wondering if there was any kind of support for indexed searching within zeesql applied to ISO 8601 Dates, more specifically, I've got a huge database with many date types in it and it would be wonderful to be able to search between date ranges and so on without middle processing and converting to timestamps etcetera

I've checked all your documentation but the one I actually need is missing: work-with-dates

I wonder if zeesql supports dates and you just have no documentation about it yet or such functionality it is yet to provide...

Thanks
Best regards

[siscia]

Hi :)

So ZeeSQL is based on SQLite, which is not super friendly with dates, but it can be managed just fine.

The database is in Redis or some other RDBMS?

Anyway, you should be able to just create an index against the date.

sqlite> create table foo(a int, b string);
sqlite> insert into foo values(1, '2021-10-08T09:46:25+00:00'),(2, '2021-10-09T09:46:25+00:00');
sqlite>
sqlite> select * from foo where b BETWEEN '2021-10-07' AND '2021-10-12';
1|2021-10-08T09:46:25+00:00
2|2021-10-09T09:46:25+00:00
sqlite> explain query plan select * from foo where b BETWEEN '2021-10-07' AND '2021-10-12';
QUERY PLAN
`--SCAN TABLE foo
sqlite>
sqlite> CREATE INDEX abc on foo (b);
sqlite> explain query plan select * from foo where b BETWEEN '2021-10-07' AND '2021-10-12';
QUERY PLAN
`--SEARCH TABLE foo USING INDEX abc (b>? AND b<?)

Does this answer your question?

[awoodevpub]

@siscia

Yes it is RDBMS, more specifically Mysql database

Let me play around with the example you just provided, but yes I expect this kind of behavior

Thank you
Best Regards