Project

General

Profile

[wt4.0]problem with dates with Sqlite

Added by Patrick ottavi almost 5 years ago

Hello,

since version 4.0 the date storage processing with SQlite 3 to change:

the use of strftime means that dates can not be less than 1970

which is not problem for a Wt :: WDateTime but is problematic for Wt :: WDate. to store a date of birth.

the other problem I encountered is that you have added the milliseconds on a WDateTime which causes a problem of compatibility with the existing bases especially when the WDateTime is used in the key of the table.

would not be possible to make the storage of milliseconds optional?

I modified the following functions:

 virtual void bind(int column, const std::chrono::system_clock::time_point& value,
            SqlDateTimeType type) override
//....
/*
 if (type == SqlDateTimeType::Date){
        std::strftime(str, sizeof(str), "%Y-%m-%d", tm);
        v = str;
      }
      else {
        std::strftime(str, sizeof(str), "%Y-%m-%dT%H:%M:%S", tm);
        v = str;
        std::stringstream ss;
        ss.imbue(std::locale::classic());
        ss << "." << std::setfill('0') << std::setw(3) << ms.count()%1000;
        v.append(ss.str());

    if (storageType == DateTimeStorage::PseudoISO8601AsText)
      v[v.find('T')] = ' ';
      }
*/
 // replace by:
          if (type == SqlDateTimeType::Date)
      {      
        auto days = floor<date::days>(value);
        auto ymd = date::year_month_day(days);
        std::stringstream ss;

ss << int(ymd.year()) << "-"<< std::setfill('0') << std::setw(2) <<unsigned(ymd.month()) << "-"<<std::setfill('0') << std::setw(2)<< unsigned(ymd.day());
        v = ss.str();        
      }
      else {
        std::strftime(str, sizeof(str), "%Y-%m-%dT%H:%M:%S", tm);
        v = str;
        /*std::stringstream ss;
        ss.imbue(std::locale::classic());
        ss << "." << std::setfill('0') << std::setw(3) << ms.count()%1000;
        v.append(ss.str());*/

    if (storageType == DateTimeStorage::PseudoISO8601AsText)
      v[v.find('T')] = ' ';
      }
//--------------------------------------------
virtual bool getResult(int column, std::chrono::system_clock::time_point *value, SqlDateTimeType type) override
  {
//....
/*  
 switch (storageType) {
    case DateTimeStorage::ISO8601AsText:
    case DateTimeStorage::PseudoISO8601AsText: {
      std::string v;
      if (!getResult(column, &v, -1))
    return false;

      try {
      if (type == SqlDateTimeType::Date){
      int year, month, day;
      std::sscanf(v.c_str(), "%d-%d-%d", &year, &month, &day);
      std::tm tm = std::tm();
      tm.tm_year = year - 1900;
      tm.tm_mon = month - 1;
      tm.tm_mday = day;
      std::time_t t = timegm(&tm);
      *value = std::chrono::system_clock::from_time_t(t);
    } else {
      std::size_t t = v.find('T');

      if (t != std::string::npos)
        v[t] = ' ';
      if (v.length() > 0 && v[v.length() - 1] == 'Z')
        v.erase(v.length() - 1);

      int year, month, day, hour, min, sec, ms;
      std::sscanf(v.c_str(), "%d-%d-%d %d:%d:%d.%d", &year, &month, &day, &hour, &min, &sec, &ms);*/

// replace by:
    if (type == SqlDateTimeType::Date) {

            int year, month, day;
            std::sscanf(v.c_str(), "%d-%d-%d", &year, &month, &day);

            auto ymd = date::year(year) / month  / day;             
            *value = date::sys_days(ymd);             
          }
         else {
      //std::size_t t = v.find('T');
      //if (t != std::string::npos)    v[t] = ' '; !!!******why you do not test DateTimeStorage :: ISO8601AsText*****!!!!
      if (v.length() > 0 && v[v.length() - 1] == 'Z')    v.erase(v.length() - 1);

      int year, month, day, hour, min, sec, ms=0;
      std::sscanf(v.c_str(), "%d-%d-%dT%d:%d:%d", &year, &month, &day, &hour, &min, &sec);

Regards,

Patrick