Bug report #17681

Negative values for age in expressions

Added by Rombert Stapel over 2 years ago. Updated over 2 years ago.

Status:Closed
Priority:High
Assignee:Sandro Santilli
Category:Expressions
Affected QGIS version:2.18.14 Regression?:No
Operating System:Mac OS High Sierra Easy fix?:No
Pull Request or Patch supplied:Yes Resolution:fixed/implemented
Crashes QGIS or corrupts data:No Copied to github as #:25578

Description

In calculating the difference between two dates from a long list of starting and ending dates (from medieval period to now), some of the outcomes produce negative values:

DATEfrom DATEto AGE (in days)
1380-01-01 1515-12-31 -37
1100-01-01 1225-12-31 -3689
580-01-01 665-12-31 -18299
1612-01-01 1697-12-31 -18299

Whereas most other dates do provide correct ages (>90%):
1001-01-01 1199-12-31 23339
1814-01-01 2016-12-31 24434

The expression used here is: day(age("DATEto","DATEfrom")). Is this a bug?

Associated revisions

Revision 336995dc
Added by Sandro Santilli over 2 years ago

Allow age expression to represent time intervals above 130 years (#5914)

Uses a 64bit integer instead of 32bit one for seconds,
meaning about 584 billions of years rather than 136...

Closes #17681

Includes test

History

#1 Updated by Andreas Neumann over 2 years ago

  • Priority changed from Normal to High

Seems to be a serious issue to me.

Calculations that provide wrong results are always bad.

#2 Updated by Sandro Santilli over 2 years ago

  • Assignee set to Sandro Santilli
  • Status changed from Open to In Progress

I'm looking at this, starting from the existing unit test tests/src/core/testqgsexpression.cpp (run via output/bin/qgis_expressiontest).
The test in there puts later day first, but I'm not sure yet about the test code semantic:

day(age(to_date('2004-03-22'),to_date('2004-03-12')))

#3 Updated by Sandro Santilli over 2 years ago

I'm guessing it's an overflow of `int` type used to compute seconds between the dates.
The first age you report as working has 4291574400, max integer is 4294967296.
The first non-working age has 6279724800 seconds, so turns to negative.
I'm working on adding an automated test for this.

#4 Updated by Sandro Santilli over 2 years ago

  • Pull Request or Patch supplied changed from No to Yes

Pull request ready for test: https://github.com/qgis/QGIS/pull/5914
Andreas: can you give that a try ?

#5 Updated by Sandro Santilli over 2 years ago

  • Status changed from In Progress to Closed
  • % Done changed from 0 to 100

#6 Updated by Giovanni Manghi over 2 years ago

  • Resolution set to fixed/implemented

Also available in: Atom PDF