Dates from MySql database dont show correctly on QTableView widget

[SaadAli]

As the title says, I don't get the dates properly when I show a model on QTableView widget. I want the dates to be shown as YYYY-MM-DD eg. (2023-06-13). That is how it is stored in the database.

I have used the following code to store the date, the date comes from a QDateEdit widget:

QSqlQuery query;
query.prepare("INSERT INTO client(commission_date) " VALUES(:commission_date)");
query.bindValue(":commission_date", ui->clientDateEdit->date().toString(Qt::DateFormat::ISODate));
query.exec();

I have also tried the following to add date:

query.bindValue(":commission_date", ui->clientDateEdit->date());

The date stored into MySql database is correct from both the ways.

The following shows the output inside QTableView widget:

This is how the dates are stored in the database (this is how I want it to be viewed in Qt):

This is the code to create a model and then show it using a QTableView widget:

QSqlModel *model = new QSqlQueryModel();

model->setQuery("SELECT * FROM " + ui->selectTableComboBox->currentText());
ui->tableView->setModel(model);
ui->tableView->show();
ui->tableView->resizeColumnsToContents();

selectTableComboBox is just a combobox where I select the table which is the client table that has the dates.
How can I view the dates properly?

[JonB]

@SaadAli
Not much to say beyond subclass QStyledItemDelegate and override the virtual method QString QStyledItemDelegate::displayText(const QVariant &value, const QLocale &locale) const. value will be date, return a string from your override with the date formatted as you choose. Finally use void QAbstractItemView::setItemDelegateForRow(int row, QAbstractItemDelegate *delegate) to set a new instance of your delegate on the desired date column.

If in doubt Google for, as you would expect, qstyleditemdelegate displaytext date. The first hit shows code.

[JonB]

@SaadAli
I would store the dates to the database by passing/binding a date value, not converting it to string (your ISODate). Let the binder handle that (i.e. use your second way).

Use a QStyledItemDelegate in the QTableView for those columns which are dates, and have that format the date whatever way you want use to see it if not the default. See e.g. https://forum.qt.io/topic/53790/qtableview-date-format/2.

[SaadAli]

@JonB
I have been researching on how to use QStyledItemDelegate to re-implement but I don't have the knowledge and understanding about re-implementing it. The reference that you gave says to re-implement displayTextMethod by implementing a subclass of displayTextMethod. I don't know much about the arguments being passed to that function, what do they refer to and how to use them. If you can kindly share to me some resources or tutorial on how to understand them that will be really helpful. Thanks in advance.

[JonB]

@SaadAli
Not much to say beyond subclass QStyledItemDelegate and override the virtual method QString QStyledItemDelegate::displayText(const QVariant &value, const QLocale &locale) const. value will be date, return a string from your override with the date formatted as you choose. Finally use void QAbstractItemView::setItemDelegateForRow(int row, QAbstractItemDelegate *delegate) to set a new instance of your delegate on the desired date column.

If in doubt Google for, as you would expect, qstyleditemdelegate displaytext date. The first hit shows code.

[SaadAli]

@JonB
Okay I followed the answer of the first hit here, I received by googling qstyleditemdelegate displaytext date.

I cant seem to make it work. This is what I have done.
Created a dateitemdelegate.h header file:

#ifndef DATEITEMDELEGATE_H
#define DATEITEMDELEGATE_H

#include <QStyledItemDelegate>
#include <QDateTime>


class DateItemDelegate : public QStyledItemDelegate
{
public:
    DateItemDelegate(QObject* parent = nullptr) : QStyledItemDelegate(parent){};

    QString displayText(const QVariant& value, const QLocale& locale) const;
};

#endif // DATEITEMDELEGATE_H

Created dateitemdelegate.cpp source file:

#include "dateitemdelegate.h"

QString DateItemDelegate::displayText(const QVariant& value, const QLocale& locale) const
{
    if (value.type() == QVariant::DateTime)
    {
        return value.toDateTime().toString(Qt::ISODate);
    }
    else
        return QStyledItemDelegate::displayText(value, locale);
}

Created a pointer object of my class DateItemDelegate:

DateItemDelegate()* dateItemDelegate = new DateItemDelegate();

And then called it into the model:

QSqlModel *model = new QSqlQueryModel();

model->setQuery("SELECT * FROM " + ui->selectTableComboBox->currentText());
ui->tableView->setModel(model);
ui->tableView->setItemDelegate(dateItemDelegate);
ui->tableView->show();
ui->tableView->resizeColumnsToContents();

[JonB]

@SaadAli
Your code looks reasonable to me. Except for DateItemDelegate()* dateItemDelegate = new DateItemDelegate();, did you really use copy & paste for that? As a matter of habit please put override in the .h file.

You have not said whether your displayText() gets called? You have not shown whether you ever get QVariant::DateTime?

[SaadAli]

@JonB
@JonB said in Dates from MySql database dont show correctly on QTableView widget:

Except for DateItemDelegate()* dateItemDelegate = new DateItemDelegate();, did you really use copy & paste for that?

Yes that was a mistake here due to copy pasting, the brackets aren't in my code. And now I have added the override in my .h file.

@JonB said in Dates from MySql database dont show correctly on QTableView widget:

You have not said whether your displayText() gets called? You have not shown whether you ever get QVariant::DateTime?

How will I know if displayText() was called? I added qDebug() << value.toDateTime().toString(Qt::ISODate); in my displayText() function and did not see anything print.

[JonB]

@SaadAli
Please always use copy & paste for code.

But I don't know where you added that statement in displayText() because you don't say :( Find out whether displayText() is ever called.

Above line ui->tableView->setItemDelegate(dateItemDelegate); insert

Q_ASSERT(dateItemDelegate);

[SaadAli]

@JonB said in Dates from MySql database dont show correctly on QTableView widget:

But I don't know where you added that statement in displayText() because you don't say :( Find out whether displayText() is ever called.

Here is where I put my qDebug() code, in dateitemdelegate.cpp file:

QString DateItemDelegate::displayText(const QVariant& value, const QLocale& locale) const
{
    if (value.type() == QVariant::DateTime)
    {
        qDebug() << value.toDate().toString(Qt::ISODate);
        return value.toDate().toString(Qt::ISODate);
    }
    else
        return QStyledItemDelegate::displayText(value, locale);
}

@JonB said in Dates from MySql database dont show correctly on QTableView widget:

Above line ui->tableView->setItemDelegate(dateItemDelegate); insert
Q_ASSERT(dateItemDelegate);

I have inserted that code above that line, I dont see anything happening or printing :(

model->setQuery("SELECT * FROM " + ui->selectTableComboBox->currentText());
    ui->tableView->setModel(model);
    Q_ASSERT(dateItemDelegate);
    ui->tableView->setItemDelegate(dateItemDelegate);
    ui->tableView->show();
    ui->tableView->resizeColumnsToContents();

[JonB]

@SaadAli

@JonB said in Dates from MySql database dont show correctly on QTableView widget:

Find out whether displayText() is ever called.

Please, this is common sense.

[SaadAli]

@JonB
I apologize if I may not be doing some common debugging, I am new to Qt framework and don't have the full grasp of how the framework works.

Below is how I make the QTableView implement the output.

model->setQuery("SELECT * FROM " + ui->selectTableComboBox->currentText());
ui->tableView->setModel(model);
Q_ASSERT(dateItemDelegate);
ui->tableView->setItemDelegate(dateItemDelegate);
ui->tableView->show();
ui->tableView->resizeColumnsToContents();

It should call whatever it should do display it from the database. One way to find out if it is ever called was by adding the line in the displayText() function as below.

QString DateItemDelegate::displayText(const QVariant& value, const QLocale& locale) const
{
    if (value.type() == QVariant::DateTime)
    {
        qDebug() << "Here";
        return value.toDate().toString(Qt::ISODate);
    }
    else
        return QStyledItemDelegate::displayText(value, locale);
}

Other than that I dont have any code that might be tinkering with the TableView widget or the delegate stuff. Help me with what easy debug stuff I might be missing so I can show what you want.

[JonB]

@SaadAli
I suggested multiple times you find out whether displayText() is called. All you have done is put a qDebug() next to returning a ISODate string which you say isn't happening. Why don't you put

qDebug() << "DateItemDelegate::displayText() called"

as the first statement inside the function, not inside any if? That is simple debugging, nothing to do with Qt.

Even better would be qDebug() << value.type().

Then you & I would know whether it is actually called or not.

And if it is called, and it does not print out what is inside an if statement, then you know the if condition is never true. Why do you believe you will get any QVariant::DateTime value type? You don't even set it with a QDateTime, and it does not look like a datetime to me.

[SaadAli]

@JonB
Problem solved! You are very correct on my debugging today, my brain is slow today :(
First I printed it before the 'if' condition. And it printed, that means it was entering displayText().

Then your second point was what the problem was, it was never receiving QVariant::DateTime but QVariant::Date. I changed that on my if condition and it solved it. I learned that just copy pasting from someone else's problem without tailoring it is not a good thing :)

Thank you for your answer and patience @JonB .