{"action":"created","issue":{"url":"https://api.github.com/repos/airbnb/caravel/issues/631","repository_url":"https://api.github.com/repos/airbnb/caravel","labels_url":"https://api.github.com/repos/airbnb/caravel/issues/631/labels{/name}","comments_url":"https://api.github.com/repos/airbnb/caravel/issues/631/comments","events_url":"https://api.github.com/repos/airbnb/caravel/issues/631/events","html_url":"https://github.com/airbnb/caravel/issues/631","id":160709049,"number":631,"title":"add presto dttm format in dttm_converter","user":{"login":"tarekrached","id":167218,"avatar_url":"https://avatars.githubusercontent.com/u/167218?v=3","gravatar_id":"","url":"https://api.github.com/users/tarekrached","html_url":"https://github.com/tarekrached","followers_url":"https://api.github.com/users/tarekrached/followers","following_url":"https://api.github.com/users/tarekrached/following{/other_user}","gists_url":"https://api.github.com/users/tarekrached/gists{/gist_id}","starred_url":"https://api.github.com/users/tarekrached/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tarekrached/subscriptions","organizations_url":"https://api.github.com/users/tarekrached/orgs","repos_url":"https://api.github.com/users/tarekrached/repos","events_url":"https://api.github.com/users/tarekrached/events{/privacy}","received_events_url":"https://api.github.com/users/tarekrached/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":2,"created_at":"2016-06-16T16:51:36Z","updated_at":"2016-06-29T00:41:20Z","closed_at":"2016-06-28T21:36:22Z","body":"Presto doesn't understand ISO 8601 timestamp strings as time-like, so currently, [timestamp comparisons](https://github.com/airbnb/caravel/blob/master/caravel/models.py#L672-L682) in Presto are being executed as lexical varchar comparisons.\r\n\r\nThis means that `TIMESTAMP` columns in Presto can't be used as dttm columns.  If you do try to use a `TIMESTAMP` column in Presto as a dttm column, you get a `GREATER_THAN_OR_EQUAL(timestamp, varchar) not registered`\r\n\r\nOne solution would be to add a Presto-specific [dttm_converter](https://github.com/airbnb/caravel/blob/master/caravel/models.py#L442-L458), such as\r\n```python\r\n\"CAST('{}' AS TIMESTAMP)\".format(dttm.strftime('%Y-%m-%d %H:%M:%S.%f')[:-3]))\r\n```\r\n\r\nThis approach was initially proposed in #522, but I'm creating this issue so that we can discuss more broadly.\r\n\r\n#506 (or a per-column version) is necessary but not sufficient - if & when it it gets merged, the [timestamp comparison](https://github.com/airbnb/caravel/blob/master/caravel/models.py#L672-L682) with still be performed as a lexical string comparisons in Presto, and that is not going to work for date formats such as MM/DD/YYYY. (e.g. 01/01/2001 < 02/01/2000).\r\n\r\nWhatever solution we end up with should not hinder Presto's optimizer from partition pruning and index utilization."},"comment":{"url":"https://api.github.com/repos/airbnb/caravel/issues/comments/229225403","html_url":"https://github.com/airbnb/caravel/issues/631#issuecomment-229225403","issue_url":"https://api.github.com/repos/airbnb/caravel/issues/631","id":229225403,"user":{"login":"tarekrached","id":167218,"avatar_url":"https://avatars.githubusercontent.com/u/167218?v=3","gravatar_id":"","url":"https://api.github.com/users/tarekrached","html_url":"https://github.com/tarekrached","followers_url":"https://api.github.com/users/tarekrached/followers","following_url":"https://api.github.com/users/tarekrached/following{/other_user}","gists_url":"https://api.github.com/users/tarekrached/gists{/gist_id}","starred_url":"https://api.github.com/users/tarekrached/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tarekrached/subscriptions","organizations_url":"https://api.github.com/users/tarekrached/orgs","repos_url":"https://api.github.com/users/tarekrached/repos","events_url":"https://api.github.com/users/tarekrached/events{/privacy}","received_events_url":"https://api.github.com/users/tarekrached/received_events","type":"User","site_admin":false},"created_at":"2016-06-29T00:41:20Z","updated_at":"2016-06-29T00:41:20Z","body":"Thanks again for your work on this, @mistercrunch!"}}
{"actor":{"display_login":"tarekrached"}}