koopjs / winnow

Deprecated
Apache License 2.0
90 stars 18 forks source link

Add Support for timestamp Syntax and BETWEEN operator #64

Closed gwright closed 6 years ago

gwright commented 6 years ago
Add support for date comparisons in WHERE filter.

Winnow currently processes the 'where' parameter as follows:
* parse to AST with `flora-sql-parser`
* traverse AST and produce new SQL where clause
* pass new where clause to alasql to filter features

ESRI web maps can generate 'where' parameters that use timestamp literals:

    where created_at > timestamp '2018-03-01 14:01:00'

The flora-sql-parser is able to parse this correctly into a 'timestamp'
node, but the traverse function in where.js doesn't recognize this node
type and throws an exception.

Web maps can also generate 'between' expressions like:

    where created_at BETWEEN timestamp '2018-03-01' AND timestamp '2018-03-02'

which is also parsed correctly by flora-sql-parser but is serialized
back into a string that isn't understood by alasql:

    where created_at BETWEEN ('2018-03-01','2018-03-02')

This commit adds support for the timestamp node and BETWEEN operator so
that the resulting where clause syntax is understood by alasql.

The timestamp node is rendered as an iso8601 timestamp string,
which can be safely compared. The BETWEEN operator node is rendered
into the standard SQL syntax.
gwright commented 6 years ago

@rgwozdz I reverted the version bump. I can rebase and eliminate it entirely if you would prefer.

rgwozdz commented 6 years ago

@gwright - no worries, not necessary.

zbeat commented 6 years ago

🎉 Looking forward to this...

rgwozdz commented 6 years ago

Looks good. Will bump version and release shortly.

rgwozdz commented 6 years ago

@gwright - thanks very much for the contribution. Added and released in v1.13.0.