date/datetime-specific operators

[Former user]

Python 3.3 has added a timestamp() method to datetime.datetime objects.

In this example SettingDefaults.start_time is defined as Column(DateTime, default=datetime.now, nullable=False).

    default_settings = session.query(SettingDefaults).\
        filter(SettingDefaults.start_time.timestamp() <= start_timestamp)

gives:

    Traceback (most recent call last):
      File "/sites/metrics_dev/env/lib/python3.3/site-packages/SQLAlchemy-0.8.0-py3.3.egg/sqlalchemy/sql/expression.py", line 2267, in __getattr__
        return getattr(self.comparator, key)
    AttributeError: 'Comparator' object has no attribute 'timestamp'

    During handling of the above exception, another exception occurred:

    Traceback (most recent call last):
      File "/sites/metrics_dev/env/lib/python3.3/site-packages/SQLAlchemy-0.8.0-py3.3.egg/sqlalchemy/orm/attributes.py", line 262, in __getattr__
        return getattr(self.comparator, key)
      File "/sites/metrics_dev/env/lib/python3.3/site-packages/SQLAlchemy-0.8.0-py3.3.egg/sqlalchemy/orm/properties.py", line 209, in __getattr__
        return getattr(self.__clause_element__(), key)
      File "/sites/metrics_dev/env/lib/python3.3/site-packages/SQLAlchemy-0.8.0-py3.3.egg/sqlalchemy/sql/expression.py", line 2273, in __getattr__
        key)
    AttributeError: Neither 'AnnotatedColumn' object nor 'Comparator' object has an attribute 'timestamp'

    During handling of the above exception, another exception occurred:

    Traceback (most recent call last):
      [SNIP ***](***)
        filter(SettingDefaults.start_time.timestamp() <= start_timestamp)
      File "/sites/metrics_dev/env/lib/python3.3/site-packages/SQLAlchemy-0.8.0-py3.3.egg/sqlalchemy/orm/attributes.py", line 270, in __getattr__
        key)
    AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with SettingDefaults.start_time has an attribute 'timestamp'

[Former user]

ugh.. sorry for the formatting. I thought the initial spaces were going to place it in a code block.

[Mike Bayer]

No methods that are present on a Python datetime object are implemented directly as SQL operators right now (we have all of: 'astimezone', 'combine', 'ctime', 'date', 'day', 'dst', 'fromordinal', 'fromtimestamp', 'hour', 'isocalendar', 'isoformat', 'isoweekday', 'max', 'microsecond', 'min', 'minute', 'month', 'now', 'replace', 'resolution', 'second', 'strftime', 'strptime', 'time', 'timetuple', 'timetz', 'today', 'toordinal', 'tzinfo', 'tzname', 'utcfromtimestamp', 'utcnow', 'utcoffset', 'utctimetuple', 'weekday', 'year'). Right now, SQL functions have to be called explicitly. For example, "hour" you can get using extract(column, "hour"). Functions like astimezone() and such are not implemented directly at all, you need to use whatever functions are present in the target database using func.<funcname>(col).

Now that we have much better ability to do type-specific operators in 0.8, we can begin adding some more functions to the DateTime type and similar, but only to the degree that a SQL implementation is reasonably available on at least a handful of platforms. It's certainly a feature worth looking into.

[Former user]

Ah, okay, thank you for the response.

[Mike Bayer]

#2769 is the ticket for this issue.