Bug #11426


sql_value_traits for WDate does not handle dates before year 1970

Added by Stefan Bn 6 months ago. Updated 4 months ago.

Roel Standaert
Target version:
Start date:
Due date:
% Done:


Estimated time:


I was wondering why the WDate's in my Sqlite table show empty values for all dates prior to year 1970.

I think the situation is in WtSqlTraits.h here, followed in Sqlite3.C:

inline void sql_value_traits<WDate, void>
::bind(const WDate& v, SqlStatement *statement, int column, int /* size */)
  if (v.isNull())
    statement->bind(column, v.toTimePoint() , SqlDateTimeType::Date);

The WDate is represented as a std::chrono::time_point using v.toTimePoint() and that refers to the distance to epoch (= 01.01.1970 00:00:00). This leads to negative values for all years prior to 1970 and an empty SQL representation - at least in my configuration: SQLite, Wt 4.9.1 built on Windows 10 using Clang64 in C++17 mode.

Is this expected behavior or a bug? :-) This way, Wt is not able to SQL-save birth dates for all persons born before 1970 :-)


Actions #1

Updated by Roel Standaert 6 months ago

  • Status changed from New to InProgress
  • Assignee set to Roel Standaert
  • Target version set to 4.9.2

Hm, this seems like a Windows-specific bug. I changed the dbo_test1 test case to use pre-UNIX epoch dates, and while it's fine on my Linux system, our Windows build is indeed failing.

Actions #2

Updated by Roel Standaert 6 months ago

Turns out every single way that the Sqlite3 backend encodes timestamps is wrong in some way, so I'm fixing all of them:

  1. when using ISO8601AsText milliseconds are encoded incorrectly for dates before 1970, e.g. December 31, 1969 23:59:59.999 is encoded as 1970-01-01T00:00:00.0-1 instead of 1969-12-31T23:59:59.999.
  2. when using JulianDaysAsReal the decimal (i.e. time of day) part is divided by 1000. It also uses November 24, 4714 BCE at midnight as the reference, instead of noon, so it's off by 0.5 compared to Sqlite's own julianday(...) function.
  3. when using UnixTimeAsInteger the date part is discarded entirely, and we're saving the time of day as milliseconds instead of as seconds.

For JulianDaysAsReal I'm still trying to decide whether we should save WDate(2023, 3, 17) as 2460020.5 or as 2460021.

Actions #3

Updated by Roel Standaert 6 months ago

  • Status changed from InProgress to Review
  • Assignee deleted (Roel Standaert)
  • Target version changed from 4.9.2 to 4.10.0
Actions #4

Updated by Roel Standaert 6 months ago

  • Status changed from Review to Implemented @Emweb
  • Assignee set to Roel Standaert
  • % Done changed from 0 to 100
Actions #5

Updated by Roel Standaert 6 months ago

  • Status changed from Implemented @Emweb to Resolved
Actions #6

Updated by Matthias Van Ceulebroeck 4 months ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF