Query from Superset on date fields

[thierry-beemenergy]

Hello,

Thanks for your work!

I have issues to run queries from Superset with date-times, maybe you will have clues:

• My collection has a timestamp column named "date". I need to escape its name in my SQL queries, otherwise PartiQL won't parse it. But then, it sounds like to not match the existing field in the collection (data are always null).
• I tried to query another collection with a timestamp column named differently, this time I could retrieve values, but I have a troubles to filter on it, it always find no rows, with queries like select * from something where somedate > '2000-01-01T00:00:00Z';

Thanks for your help

[passren]

@thierry-beemenergy Thank you for bringing this up. Will check.

[passren]

@thierry-beemenergy Did you use double quote ( " ) or backticks ( ` ) to escape the keyword?

[thierry-beemenergy]

Both 🙂 and single quotes as well.
With backticks, it raise an SQL parsing exception, probably from Superset itself and before giving to the DB connection driver:

2026-01-30 07:54:27,468:WARNING:superset.views.error_handling:SupersetErrorsException
Traceback (most recent call last):
  File "/app/.venv/lib/python3.10/site-packages/flask/app.py", line 1484, in full_dispatch_request
    rv = self.dispatch_request()
  File "/app/.venv/lib/python3.10/site-packages/flask/app.py", line 1469, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
  File "/app/.venv/lib/python3.10/site-packages/flask_appbuilder/security/decorators.py", line 109, in wraps
    return f(self, *args, **kwargs)
  File "/app/superset/views/base_api.py", line 120, in wraps
    duration, response = time_function(f, self, *args, **kwargs)
  File "/app/superset/utils/core.py", line 1369, in time_function
    response = func(*args, **kwargs)
  File "/app/superset/views/base_api.py", line 92, in wraps
    return f(self, *args, **kwargs)
  File "/app/superset/utils/log.py", line 304, in wrapper
    value = f(*args, **kwargs)
  File "/app/superset/sqllab/api.py", line 409, in execute_sql_query
    command_result: CommandResult = command.run()
  File "/app/superset/utils/decorators.py", line 266, in wrapped
    return on_error(ex)
  File "/app/superset/utils/decorators.py", line 236, in on_error
    raise ex
  File "/app/superset/utils/decorators.py", line 259, in wrapped
    result = func(*args, **kwargs)
  File "/app/superset/commands/sql_lab/execute.py", line 105, in run
    status = self._run_sql_json_exec_from_scratch()
  File "/app/superset/commands/sql_lab/execute.py", line 158, in _run_sql_json_exec_from_scratch
    return self._sql_json_executor.execute(
  File "/app/superset/sqllab/sql_json_executer.py", line 110, in execute
    raise SupersetErrorsException(
superset.exceptions.SupersetErrorsException: [SupersetError(message="Error parsing near '`' at line 1:13", error_type=<SupersetErrorType.INVALID_SQL_ERROR: 'INVALID_SQL_ERROR'>, level=<ErrorLevel.ERROR: 'error'>, extra={'sql': 'select `date` from something', 'engine': 'base', 'line': 1, 'column': 13, 'issue_codes': [{'code': 1003, 'message': 'Issue 1003 - There is a syntax error in the SQL query. Perhaps there was a misspelling or a typo.'}]}), SupersetError(message='This database does not allow for DDL/DML, and the query could not be parsed to confirm it is a read-only query. Please contact your administrator for more assistance.', error_type=<SupersetErrorType.DML_NOT_ALLOWED_ERROR: 'DML_NOT_ALLOWED_ERROR'>, level=<ErrorLevel.ERROR: 'error'>, extra={'issue_codes': [{'code': 1022, 'message': 'Issue 1022 - Database does not allow data manipulation.'}]})]

[passren]

@thierry-beemenergy reserved keyword issue already got addressed. but it's a bit difficult for the timestamp data type. Tried to bring some changes to support type convertion function for columns and where clause. but the effort is much bigger than I expected. Some implementations were done in 0.3.5 branch but I decided to obslete it as the solution is not lean and becoming ugly :-D ... So I may release a new minor version to fix reserved keyword issue first then resolve type conversion in the upcoming versions.

[passren]

v0.4.0 fixed reserved keyword issue. double quotes should be used for the field name if it's keyword.

[passren]

@thierry-beemenergy Value functions are introduced for type conversion. It should work for querying on the timestamp field.

README - Value Functions

Value Functions

PyMongoSQL supports value functions to transform and filter values in WHERE clauses. Built-in value functions include:

str_to_datetime() - Convert ISO 8601 or custom formatted strings to Python datetime objects

# ISO 8601 format
cursor.execute("SELECT * FROM events WHERE created_at >= str_to_datetime('2024-01-15T10:30:00Z')")

# Custom format
cursor.execute("SELECT * FROM events WHERE created_at < str_to_datetime('03/15/2024', '%m/%d/%Y')")

str_to_timestamp() - Convert ISO 8601 or custom formatted strings to BSON Timestamp objects

# ISO 8601 format
cursor.execute("SELECT * FROM logs WHERE timestamp > str_to_timestamp('2024-01-15T00:00:00Z')")

# Custom format
cursor.execute("SELECT * FROM logs WHERE timestamp < str_to_timestamp('01/15/2024', '%m/%d/%Y')")

Both functions:

• Support ISO 8601 strings with 'Z' timezone indicator
• Support custom format strings using Python strftime directives
• Return values with UTC timezone
• Can be combined with standard SQL operators (>, <, >=, <=, =, !=)

[thierry-beemenergy]

Hello,
Excellent, I will update the package and have a try very soon, thanks for your quick work!