Display data from database after preprocessing

[yagabey]

Hi,

I would like to display a data from a database after some preprocessing.

I am storing some QDateTime values in the form of qint64 variables(by using toMSecsSinceEpoch() ) in the pgsql database. But when displaying the data from database , I would like to display a beautiful date-time string( by using fromMSecsSinceEpoch() )

I am simply setting the sql query model to my tableview. I couldnt figure out how and where should i convert the qint64 value to a date string to be displayed in my table view ?

Thanks in advance...

[wysota]

You can do that in the delegate or in the query itself (by asking the database to do it).

[pitonyak]

You can do this in many places and it depends on your preferences and how you have designed your program. Consider the following options:

1. As already mentioned, you can choose to format your data when you extract it, but, then it is always extracted as a string. I prefer to pull in the data into the correct expected data type, so, for me, that would mean a QDateTime object when I read it.
2. I have a model class that feeds data from the data( const QModelIndex &index, int role ) const method. If the role is Qt:isplayRole, you can choose to format the date and return a string for display and then return a QDateTime in the variant for the edit role.
3. I use my own delegate class. This is very easy to write your own based on what you want to accomplish.

Considering the final option, I have something like this in my delegate class. Consider this snippet!

QString LinkBackFilterDelegate::displayText(const QVariant & value, const QLocale & locale ) const
{
  if (value.type() == QVariant::Date && !getDateFormatString().isEmpty())
  {
    return value.toDate().toString(getDateFormatString());
  }
  else if (value.type() == QVariant::DateTime && !getDateTimeFormatString().isEmpty())
  {
    return value.toDateTime().toString(getDateTimeFormatString());
  }
  else if (value.type() == QVariant::Time && !getTimeFormatString().isEmpty())
  {
      return value.toTime().toString(getTimeFormatString());
  }
  else if (value.type() == QVariant::Bool)
  {
      if (value.toBool())
      {
          if (!getBoolTrue().isEmpty()) {
              return getBoolTrue();  // "True"
          }
      }
      else if (!getBoolFalse().isEmpty()) {
          return getBoolFalse(); // "False"
      }
  }
 
  return QStyledItemDelegate::displayText(value, locale);
}

QString LinkBackFilterDelegate::displayText(const QVariant & value, const QLocale & locale ) const
{
  if (value.type() == QVariant::Date && !getDateFormatString().isEmpty())
  {
    return value.toDate().toString(getDateFormatString());
  }
  else if (value.type() == QVariant::DateTime && !getDateTimeFormatString().isEmpty())
  {
    return value.toDateTime().toString(getDateTimeFormatString());
  }
  else if (value.type() == QVariant::Time && !getTimeFormatString().isEmpty())
  {
      return value.toTime().toString(getTimeFormatString());
  }
  else if (value.type() == QVariant::Bool)
  {
      if (value.toBool())
      {
          if (!getBoolTrue().isEmpty()) {
              return getBoolTrue();  // "True"
          }
      }
      else if (!getBoolFalse().isEmpty()) {
          return getBoolFalse(); // "False"
      }
  }

  return QStyledItemDelegate::displayText(value, locale);
}

To copy to clipboard, switch view to plain text mode 

I also want the formatting when I edit

QWidget *LinkBackFilterDelegate::createEditor(QWidget *parent, const QStyleOptionViewItem & option,
                                              const QModelIndex &index) const
{
  if (!index.isValid())
  {
    return nullptr;
  }
  QVariant qvar = index.model()->data(index, Qt::EditRole);
  if (qvar.type() == QVariant::StringList)
  {
    return new QComboBox(parent);
  }
  else if (qvar.type() == QVariant::Bool)
  {
    QCheckBox* checkBox = new QCheckBox(parent);
    checkBox->setTristate(false);
    return checkBox;
  }
  else if (qvar.type() == QVariant::Date && !getDateFormatString().isEmpty())
  {
    QDateEdit* editor = new QDateEdit(parent);
    editor->setDisplayFormat(getDateFormatString());
    // Annoying that I need this!
    editor->setAutoFillBackground(true);
    return editor;
  }
  else if (qvar.type() == QVariant::DateTime && !getDateTimeFormatString().isEmpty())
  {
    QDateTimeEdit* editor = new QDateTimeEdit(parent);
    editor->setDisplayFormat(getDateTimeFormatString());
    // Annoying that I need this!
    editor->setAutoFillBackground(true);
    return editor;
  }
  else if (qvar.type() == QVariant::Time && !getTimeFormatString().isEmpty())
  {
    QTimeEdit* editor = new QTimeEdit(parent);
    editor->setDisplayFormat(getTimeFormatString());
    // Annoying that I need this!
    editor->setAutoFillBackground(true);
    return editor;
  }
 
  //widget = new QLineEdit(parent);
  // Returns an expanding line editor.
  QWidget* widget = QStyledItemDelegate::createEditor(parent, option, index);
 
  // If I do not set this to true, then the "view" data shows through the text while editing.
  // Also, the expanding editor grows over existing controls, and you can see those through
  // the edit display as well (distracting).
  widget->setAutoFillBackground(true);
  return widget;
}

QWidget *LinkBackFilterDelegate::createEditor(QWidget *parent, const QStyleOptionViewItem & option,
                                              const QModelIndex &index) const
{
  if (!index.isValid())
  {
    return nullptr;
  }
  QVariant qvar = index.model()->data(index, Qt::EditRole);
  if (qvar.type() == QVariant::StringList)
  {
    return new QComboBox(parent);
  }
  else if (qvar.type() == QVariant::Bool)
  {
    QCheckBox* checkBox = new QCheckBox(parent);
    checkBox->setTristate(false);
    return checkBox;
  }
  else if (qvar.type() == QVariant::Date && !getDateFormatString().isEmpty())
  {
    QDateEdit* editor = new QDateEdit(parent);
    editor->setDisplayFormat(getDateFormatString());
    // Annoying that I need this!
    editor->setAutoFillBackground(true);
    return editor;
  }
  else if (qvar.type() == QVariant::DateTime && !getDateTimeFormatString().isEmpty())
  {
    QDateTimeEdit* editor = new QDateTimeEdit(parent);
    editor->setDisplayFormat(getDateTimeFormatString());
    // Annoying that I need this!
    editor->setAutoFillBackground(true);
    return editor;
  }
  else if (qvar.type() == QVariant::Time && !getTimeFormatString().isEmpty())
  {
    QTimeEdit* editor = new QTimeEdit(parent);
    editor->setDisplayFormat(getTimeFormatString());
    // Annoying that I need this!
    editor->setAutoFillBackground(true);
    return editor;
  }

  //widget = new QLineEdit(parent);
  // Returns an expanding line editor.
  QWidget* widget = QStyledItemDelegate::createEditor(parent, option, index);

  // If I do not set this to true, then the "view" data shows through the text while editing.
  // Also, the expanding editor grows over existing controls, and you can see those through
  // the edit display as well (distracting).
  widget->setAutoFillBackground(true);
  return widget;
}

To copy to clipboard, switch view to plain text mode 

Note that I have a specific delegate that I use for check boolean / check boxes. I also have more code to handle setting editor data and such, but, not worth pasting it unless I know you will bother to use is.

[d_stranz]

If you don't need to edit the SQL model in the view (in other words, it is for display only), then you can derive a new proxy model from QIdentityProxyModel and override the QIdentityProxyModel::data() method to format your dates. You set the proxy model on your view, and the SQL model as the source model for the proxy. The Qt docs give exactly that case as an example.