I have a column in a QGIS attribute table that contains elapsed time (in years). I have created an additional column in the attribute table to convert the elapsed time to a date. I am using an expression using the open field calculator to add the elapsed time to the current date. The expression I am using is now() + to_interval(concat("elapsed_time"),' years')), with elapsed time being the elapsed time column.

Everything works fine but when the elapsed time becomes greater than ~68 years, the output date reverts back to 1950, instead of correctly adding the number of years to the current date. I have tried to use other dates besides the current date and get the same results. Does anyone have any ideas of why this happens and how to fix it?

  • 2
    QGIS may be using intervals in seconds internally, and storing them in int32 format. 32-bit integers only allow 4,294,967,296 different values, and that number of seconds converted to years gives 136 years. That may explain why it only calculates -68 to +68 years, and past 68 years the integer overflows back to negatives. – FSimardGIS Apr 24 '18 at 22:11
  • I don't know what is the cause, so +1 from me if @FSimardGIS can post the comment as answer. I thought I would add that this is reproduced with my dataset in QGIS 2.18.19 while it does not happen in QGIS3.0.2. – Kazuhito Apr 24 '18 at 22:18
  • 32-bit time_t overflows in 2038, but the epoch is 01 Jan 1970, not 1950. Your issue is likely the limitations of the integer-based support library, not QGIS. Where possible, you should use struct tm-based date functions. – Vince Apr 24 '18 at 22:30
  • What data type are you working with? different feature sources can have different date storage requirements, same as the implementations of SQL are subtlety different between data types. According to dbase.com/KnowledgeBase/int/db7_file_fmt.htm the DATE data type in a shapefile is 8 bytes stored in the format YYYYMMDD but a TIMESTAMP is also 8 bytes - two longs, first for date, second for time. The date is the number of days since 01/01/4713 BC. Time is hours x 3600000L + minutes x 60000L + Seconds x 1000L so if you're suffering integer overflow you'd be back in prehistory! – Michael Stimson Apr 24 '18 at 22:52
  • 1
    @Michael Stimson - you have the wrong format link. Shapefiles are based on dBase-III+ (actually, more like III-ish, since 255 fields are permitted), which didn't support timestamps. – Vince Apr 24 '18 at 23:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.