Store data into QVD saves timestamp as NULL

[nicolaslopedebarrios]

When extracting data from a MySQL table to a QVD file, most timestamp values are saved as NULL.

It is a simple script:

LIB CONNECT TO 'MySQL_MYDB (qs.user)';

LET vTabla = 'MY_TABLE';

$(vTabla):
SELECT *
FROM my_schema.my_table;

STORE $(vTabla) INTO [lib://DAT_MARIADB_MYDB_QVD (qliksense_user)/DAT_01_MARIADB_MYDB_$(vTabla).qvd] (QVD);
DROP TABLE $(vTabla);

When querying the database directly, there are no NULL or invalid values in the timestamp column. However, QVD metadata shows 95% NULLS (using QViewer):

As a result, every date related KPI is not reliable, since data is not consistent with the source.

What can I do to prevent this?

[Vegar]

I have no straight answer, but check if your values are interpreted as null in the select or if the issue is in the conversion between resident table and qvd.

Try

LIB CONNECT TO 'MySQL_MYDB (qs.user)';

LET vTabla = 'MY_TABLE';

$(vTabla):

SELECT *

FROM my_schema.my_table

createe_at IS NOT NULL

LIMIT 100;

[nicolaslopedebarrios]

Hi, the query in a MySQL editor shows there are no nulls. I had a similar problem lately, when Qlik finds an invalid value (this MySQL allows dates as '0000-00-00', I hate it) it starts loading null values instead, but this is not the case (timestamps are valid). I'm gonna try casting to date in the SELECT, and explicitly replacing zero-dates with null and see what happens.

[nicolaslopedebarrios · Accepted solution]

As I suspected, Qlik "doesn't like" MySQLs' zero-dates, so using a case when... then to save '0000-00-00' as NULL for every date column in the SQL statement solved the problem.