Working with Date type in database applications

[kolach]

I have two questions about working with date type values
in database applications:

1. Viewing Date records in QTableView:
Is there a simple way to make QTableView to show date in
a format different from ISODate ("yyyy-mm-dd")

2. To edit Date records in QTableView:
Where can I intercept a creation of QDateEditor instance
which was created to edit a corresponded data value.

Thanks.

[Everall]

I have two questions about working with date type values
in database applications:

1. Viewing Date records in QTableView:
Is there a simple way to make QTableView to show date in
a format different from ISODate ("yyyy-mm-dd")

Q3 has QdataTable::setDateFormat(Qt::LocalDate) and you could even set it with the dateformat property in QT Designer 3.

in the porting to QT4 documentation:
The QDataTable class has been renamed Q3DataTable and moved to the Qt3Support library. It is expected that Qt 4.1 will offer a replacement class. In the meantime, you can use Q3DataTable for creating data-aware forms or you can roll your own.

As far I can see there is no “simple” approach like the above for QTableView in QT4.1 yet. So you will have to do it yourself. (I didn't check the latest snapshots)

2. To edit Date records in QTableView:
Where can I intercept a creation of QDateEditor instance
which was created to edit a corresponded data value.

Have a look at :
void QItemDelegate::setEditorData ( QWidget * editor, const QModelIndex & index ) const [virtual]
Sets the data to be displayed and edited by the editor for the item specified by index.

void QItemDelegate::setModelData ( QWidget * editor, QAbstractItemModel * model, const QModelIndex & index ) const [virtual]
Sets the data for the specified model and item index from that supplied by the editor.

I think you need both to make sure the values you change follow the date format of your database.

Cheers

[kolach]

Thanks for reply!
It's a pity though that there is no bloodless solusion for
the first question.

[wysota]

There isn't? A simple custom delegate and you're done.

[kolach]

There isn't? A simple custom delegate and you're done.

It doesn't look simple because one have to reimplement
QItemDelegate:: paint(.....) and that is about 90 lines of code

simple is something like this
tableView->setColumnDateFormat( index, "dd-MM-yyyy" );

but I noticed that this model QSqlTableModel -> QTableView
lacks column based controll over the records view.

[wysota]

Better yet, don't reimplement the delegate -- reimplement the model and it's data() method to return some other format of date for the view to use.

BTW. Just to show the delegate is not so hard to reimplement (needs some tweaking probably):

void MyItemDelegate::drawDisplay ( QPainter * painter, 
                                   const QStyleOptionViewItem & option, 
                                   const QRect & rect, const QString & text ) const{
  QDate dat = QDate::fromString(text);
  if(dat.isValid()){
    QItemDelegate::drawDisplay(painter, option, rect, 
                               dat.toString("dd.MM.yyyy"));
  } else {
    QItemDelegate::drawDisplay(painter, option, rect, text);
  }
}

void MyItemDelegate::drawDisplay ( QPainter * painter, 
                                   const QStyleOptionViewItem & option, 
                                   const QRect & rect, const QString & text ) const{
  QDate dat = QDate::fromString(text);
  if(dat.isValid()){
    QItemDelegate::drawDisplay(painter, option, rect, 
                               dat.toString("dd.MM.yyyy"));
  } else {
    QItemDelegate::drawDisplay(painter, option, rect, text);
  }
}

To copy to clipboard, switch view to plain text mode 

[kolach]

Better yet, don't reimplement the delegate -- reimplement the model and it's data() method to return some other format of date for the view to use.

BTW. Just to show the delegate is not so hard to reimplement (needs some tweaking probably):

see above

see above

To copy to clipboard, switch view to plain text mode 

but how would I know that I must reconvert and draw date in THIS VERY cell?
In this method I don't even have row and column coords of the cell that is being
painted.

[wysota]

In the code above the delegate checks if the data it has to render is in a date format. If so, it tries to convert it, otherwise, it renders the data without conversion. Of course this may not work in every situation, but you can extend the delegate to tell it which column (or rather what rectangle) contains the date.

[kolach]

In the code above the delegate checks if the data it has to render is in a date format. If so, it tries to convert it, otherwise, it renders the data without conversion. Of course this may not work in every situation, but you can extend the delegate to tell it which column (or rather what rectangle) contains the date.

Sorry, I've underestimated your solution.

But to make it work in every situation it's better to reimplement paint method of the deleagate. Because there you know everything you need.

[wysota]

You can overload paint(), check if you should treat any column in a special way, mark it somehow, call original paint() and in drawDisplay check for that mark and act according to it.

There is no need to rewrite paint() code.

BTW. A quick solution would be to return the date from the database in a format of your choice, instead of converting it when you display it.

Anyway, the PROPER solution would be to reimplement the model.

[kolach]

You can overload paint(), check if you should treat any column in a special way, mark it somehow, call original paint() and in drawDisplay check for that mark and act according to it.

There is no need to rewrite paint() code.

BTW. A quick solution would be to return the date from the database in a format of your choice, instead of converting it when you display it.

Anyway, the PROPER solution would be to reimplement the model.

Yes, it is posible. But looks like hack though.

As to the model:

Model returns data in QVariant format.
In delegate I see:

QItemDelegate:: paint(...)
{
...
QString text = model->data(index, Qt:: DisplayRole).toString();
...
}
in case that data is of DATE type, toString() makes ISODate convertion.

So, as I've mentioned, model returns data in QVariant format and for the MODEL I think it is a good solution. It is responsibility of VIEW to view data in this or that way.

So it seems that QItemDelegate:: paint(...) has an ill design.

[ball]

maybe you can format the date-output using built-in sql function from database first?

like to_date(), to_string in oracle

[kolach]

maybe you can format the date-output using built-in sql function from database first?

like to_date(), to_string in oracle

If I still want (and I do!) it to be of DATE type, it will not matter.
Because QItemDelegate will call model's data(...) method that converts all received from database data to QVariant and then QItemDelegate will call QVariant::toString() -> so again ISODate conversion.

[wysota]

Yes, it is a limitation, for sure and there are probably more issues like this, but we have to cope with what we have.