SQLite/Spatialite file timestamp incorrect on Windows NTFS

[molnar]

See ​https://support.microsoft.com/en-us/kb/190315 for discussion of the Windows NTFS "feature" that causes this bug.

Summary:

• Spatialite uses file-modified timestamp to decide whether extents metadata is still good
• Spatialite uses stat() function to get that timestamp
• Windows NTFS file system's stat implementation modifies the timestamp for daylight savings time (even though it's supposed to be unix time)
• Spatialite reads proper unix time from spatialite_history table
• Since the timestamps are off by a long time (an hour at this time of the year, in our case), Spatialite decides the extents are not valid, when in fact they are.

To fix this, we did something like the following in ogrsqlitedatasource.cpp:OGRSQLiteDataSource::Open()

#ifdef WINDOWS
int bGotAccurateTime = FALSE;
HANDLE hFile = CreateFileW(pwszFilename, /* GENERIC_READ, etc. */);
if (hFile != INVALID_HANDLE_VALUE)
{
    FILETIME sModTime;
    if (GetFileTime(hFile, NULL, NULL, &sModTime))
    {
        // ... things to convert FILETIME to unix time
        bGotAccurateTime = TRUE;
    }
    CloseHandle(hFile);
}
if (!bGotAccurateTime)
{
    nFileTimestamp = sStat.st_mtime;
}
#endif

See ​http://stackoverflow.com/questions/6161776/convert-windows-filetime-to-second-in-unix-linux for conversion

[Jukka Rahkonen]

I think you should inform also the Spatialite project through ​https://groups.google.com/forum/#!forum/spatialite-users.

[molnar]

Replying to jratike80:

I think you should inform also the Spatialite project through ​https://groups.google.com/forum/#!forum/spatialite-users.

Well it's not really the Spatialite project's problem. They are storing perfectly valid timestamps. It is up the users to interpret those timestamps correctly.

The problem is here in the GDAL driver, where we are using stat() to get the last-modified timestamp. Since stat() has a known bad behaviour (modifying a UNIX timestamp for daylight savings time), the GDAL driver is comparing a correct timestamp from Spatialite with a possibly-incorrect timestamp from Windows.

Now, I suppose it is quite possible that others in the spatialite-users group may have this problem, but it is not really specific to Spatialite. Rather, it is common to anyone who both stores timestamps and gets timestamps from Windows stat().

[Jukka Rahkonen]

Informing users was one thing I thought but isn't it also possible that native Spatialite tools like Spatialite-gui are using the same stat() and suffer from the same phenomena on Windows with NTFS?

[molnar]

Replying to jratike80:

Informing users was one thing I thought but isn't it also possible that native Spatialite tools like Spatialite-gui are using the same stat() and suffer from the same phenomena on Windows with NTFS?

It's certainly possible, but we can't even guarantee that they use file-modified timestamps in their application at all (without looking at their source).

[molnar]

Replying to molnar:

Summary:

• Spatialite uses file-modified timestamp to decide whether extents metadata is still good
• Spatialite uses stat() function to get that timestamp

I suppose I should have been clearer here: The Spatialite GDAL/OGR Driver uses stat() and compares timestamps. libspatialite itself seems to do the right thing with timestamps.

[Even Rouault]

From what I can see in OGRSQLiteTableLayer::LoadStatistics(), the file timestamp is only used for Spatialite v3 databases, and no longer for Spatialite v4, so the issue is going to be more and more anecdotical. Spatialite itself seems to only use sqlite3 strftime() function to generate timestamps and I don't think the lib itself does any checking with respect to the file timestamp.

I'm wondering if the proposed fix shouldn't be done in VSIWin32FilesystemHandler::Stat() itself rather than in the SQLite driver.

[molnar]

Replying to rouault:

I'm wondering if the proposed fix shouldn't be done in VSIWin32FilesystemHandler::Stat() itself rather than in the SQLite driver.

That would probably be a better fix, and it would safeguard you against future Daylight Savings Time trouble.

[Even Rouault]

This ticket has been automatically closed because Trac is no longer used for GDAL bug tracking, since the project has migrated to GitHub. If you believe this ticket is still valid, you may file it to ​https://github.com/OSGeo/gdal/issues if it is not already reported there.