Differences between PostgreSQL and SQLite in handling QDateTime

[p-himik]

Let's say i have SQLite table test(a) and PostgreSQL table test(a timestamp(0)). This code works different for different tables:
@query.prepare( "insert into test values (?);" );
const QDateTime cd = QDateTime::currentDateTime();
query.addBindValue( cd );
query.exec();
query.prepare( "select * from test where a=?;" );
query.addBindValue( cd );
query.exec();
while( query.next() )
{
qDebug() << query.value( 0 );
}@

For SQLite it outputs current timestamp and for PostgreSQL it returnes nothing.
Of course when i add "::timestamp(0)" to the end of the SELECT query PostgreSQL returns timestamp too. But the question is, should different SQL Qt drivers handle QDateTime in a different way? That is, even if i use only pure SQL language i can not change SQL driver without rewriting existing queries.

[fluca1978]

It is interesting to see how the query is translated from the driver. However I think the problem could be in the settings of the data format of the postgresql backend, which could be different from the one your driver is expecting. If I get it right, QSqlQuery binds values using a QVariant and passing it to the d pointer. I suspect the value is then translated into a string representation. If this is correct, the string is in a format your postgresql cannot understand or is misunderstanding.

[p-himik]

If i add "::timestamp(0)" to the query string everything work fine so the problem is not in PostgreSQL.

[quote author="fluca1978" date="1322119344"]I suspect the value is then translated into a string representation.[/quote]

I think so too and it's odd that QSQLITE and QPSQL convert QDateTime to string in a different way.

[fluca1978]

[quote author="p-himik" date="1322120064"]
I think so too and it's odd that QSQLITE and QPSQL convert QDateTime to string in a different way.[/quote]

Please note that ::timestamp is a cast to the PostgreSQL type timestamp. It is perfectly normal that two different drivers convert data to string in different ways, since the two backends are different. If you enable query logging on postgresql you will see the query as sent by the driver and you could understand what is going wrong. Therefore if it is the driver doing a mistake or the configuration of your backend (e.g., locales).

[fluca1978]

The problem with your query is about precision. I cloned your program on a postgresql 9 instance, and the problem is that the value I insert is different from the value that is stored. I try to insert:

'2011-11-24 12:59:39.569' (QDateTime)

but the server stores
2011-11-24 12:59:40 (timestamp(0))

because you have declared the field with a different precision from the one that corresponds to QDateTime. It will be interesting to find out the right precision for the QDateTime.

[p-himik]

Then the question is, why does it store QDateTime in SQLite as a slimple ISO date, without milliseconds?

[fluca1978]

Sql ISO has milliseconds.
Maybe SQLite is managing the value as a different type.

[p-himik]

SQLite doesn't have any data type relative to time and date. It stores them just as strings. So that still could be a driver issue.

[fluca1978]

[quote author="p-himik" date="1322137556"]SQLite doesn't have any data type relative to time and date. It stores them just as strings. So that still could be a driver issue.[/quote]

It is mainly a database issue.