search

questdb / questdb-connect

SQLAchemy and Apache Superset extensions for QuestDB
Apache License 2.0
12 stars 3 forks source link

TO_TIMESTAMP returns null #6

Closed bisrael8191 closed 8 months ago

bisrael8191 commented 1 year ago

I'm having an issue trying to use the time range filters in Apache Superset because it auto generates the following query for charts:

SELECT DATE_TRUNC('hour', timestamp) AS timestamp,
       id AS id,
       max(my_metric) AS "MAX(my_metric)"
FROM my_data
WHERE timestamp >= TO_TIMESTAMP('2023-06-18T00:00:00.000000', 'yyyy-MM-ddTHH:mm:ss.SSSUUUZ')
  AND timestamp < TO_TIMESTAMP('2023-06-19T00:00:00.000000', 'yyyy-MM-ddTHH:mm:ss.SSSUUUZ')
GROUP BY DATE_TRUNC('hour', timestamp),
         id
ORDER BY MAX(my_metric) DESC
LIMIT 10000;

TO_TIMESTAMP('2023-06-18T00:00:00.000000', 'yyyy-MM-ddTHH:mm:ss.SSSUUUZ') returns null because of the extra Z on the end of the format, but both TO_TIMESTAMP('2023-06-18T00:00:00.000000', 'yyyy-MM-ddTHH:mm:ss.SSSUUU') and TO_TIMESTAMP('2023-06-18T00:00:00.000000Z', 'yyyy-MM-ddTHH:mm:ss.SSSUUUZ') work as expected. Same behavior seen when using the quest console to manually run queries.

I believe this would be a small change to this line: https://github.com/questdb/questdb-connect/blob/main/src/qdb_superset/db_engine_specs/questdb.py#L218 Either append a Z to the isoformatted string or remove the Z from the TO_TIMESTAMP function.

marregui commented 9 months ago

hi @bisrael8191 thank you for reporting the issue. I apologise for the long delay in getting back, I lost track of the calendar with a packed summer. Would you like to contribute the change?

best

marregui commented 8 months ago

I believe this release fixes it https://pypi.org/project/questdb-connect/1.0.11/