SQLite DATETIME fieldtype

[phildu44]

When i test this code:



QSqlDatabase m_dbSQLite = QSqlDatabase::addDatabase("QSQLITE");
QDir baseDir = QDir(qApp->applicationDirPath());

#if defined(Q_OS_WIN)
if (baseDir.dirName().toLower() == "debug" || baseDir.dirName().toLower() == "release")
baseDir.cdUp();
#elif defined(Q_OS_MAC)
if (baseDir.dirName() == "MacOS") {
baseDir.cdUp();
baseDir.cdUp();
baseDir.cdUp();
}
#endif
m_dbSQLite.setDatabaseName(baseDir.absolutePath()+"/Datas/Bels.sqlite");
if(m_dbSQLite.open())
{
QSqlQuery query = QSqlQuery();

query.exec("SELECT [Birthdate] FROM Patients");
QSqlRecord rec = query.record();
QSqlField fld = rec.field("Birthdate");

qDebug() << fld.type();
}
QVariant::QString is return while the field type is DATETIME. Why?

Thanks

[Lykurg]

I don't think the type of your field is DATETIME since SQLite only supports NULL, INTEGER, REAL, BLOB and TEXT. And usually a date/time is stored as TEXT in SQLite.

EDIT: Try to play with the date() function of SQLite.

[phildu44]

In SQLite Manager (plugin Firefox) the type DATETIME exist, but your respons tell me that is not a real DATETIME type because data are stored in format TEXT.
Thank you.

[Lykurg]

In SQLite Manager (plugin Firefox) the type DATETIME exist
Strange, but according to the docs (http://sqlite.org/datatype3.html) there isn't such a type.

[Tanuki-no Torigava]

Hi, had the same problem before. For now I'm using QDateTime::toTime_t() and store that in SQLite as INTEGER. When I need to show the exact date I just need to perform the reverse operation. Hope that helps.

[phildu44]

Thanks. That help me for use in code. The problem persist when I'm using a model/view because field is not formatted correctly (integer or string YYYY-MM-DDTHH:MM:SS).

The solution is tu use MySQL.