Calc filter by date error

Question

Dear OF team,

when i create a dataset in collect and successfuly import it to Calc i get an error if I want to filter tables according to dates. First of all, they are imported as text variables, which means you cannot filter for example years that are newer then 2001 (> 2001) and more importantly i cannot even use "=". For example filter results = 2017.

this is the error i get:

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: jOOQ; bad SQL grammar [select count(*) from "probaaaa"."a_gen_view" where "probaaaa"."a_gen_view"."date1_year" between ? and ?]; nested exception is org.postgresql.util.PSQLException: ERROR: operator does not exist: integer >= character varying
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 89

any simple solutions?

Accepted Answer

Hi,

I've just released a new version of Calc that handles date variables as well.

In order to use date variables, make sure you are using Postgres9.4+.

After having installed Calc-2.1-a11 ( http://www.openforis.org/tools/calc.html ), you need to create a new workspace.

If you have calculations associated with it, just export the previous calc workspace and import it into the new one.

When filtering by dates, make sure you use the format 'yyyy-MM-dd'. Here a picture with an example : alt text

Please let me know if you face other issues.

Thank you,

Mino

Answer 2

1	Great! After deleting workspace and reimport everything works as it should. Even with SQLite. Thank you! permanent link answered 21 Mar '17, 16:29 dfinzagr 33●1●3●7 accept rate: 0%

Answer 3

0	Hi, at the moment we dont import dates as dates into Calc. We'll discuss this feature to implement and we'll let you know soon. Thank you, Mino permanent link answered 27 Feb '17, 14:44 Mino Togna ♦♦ 521●1●6 accept rate: 12%

Answer 4

0	Hmm it doesnt work. Dates arent recognized as dates (so arent found under "D" tab). But I am using Sqlite. Any easy soluitons? permanent link answered 21 Mar '17, 15:38 dfinzagr 33●1●3●7 accept rate: 0%

Answer 5

0

Have you deleted the old workspace first?

Calc has not been tested with Sqlite. At the moment only Postgres 9.4+ is supported.

For more information about the required software, please visit the installation requirement page : http://www.openforis.org/tools/calc/tutorials/installation.html

I hope this helps.

Thank you, Mino

permanent link

answered 21 Mar '17, 15:53

Mino Togna ♦♦
521●1●6
accept rate: 12%

Answer 6

0	Nice to hear that! I'm glad it's also working with SQLite ! Thanks for letting us know! Mino permanent link answered 21 Mar '17, 16:52 Mino Togna ♦♦ 521●1●6 accept rate: 12%

Calc filter by date error

Follow this question

Related questions