{"action":"created","issue":{"url":"https://api.github.com/repos/metabase/metabase/issues/4058","repository_url":"https://api.github.com/repos/metabase/metabase","labels_url":"https://api.github.com/repos/metabase/metabase/issues/4058/labels{/name}","comments_url":"https://api.github.com/repos/metabase/metabase/issues/4058/comments","events_url":"https://api.github.com/repos/metabase/metabase/issues/4058/events","html_url":"https://github.com/metabase/metabase/issues/4058","id":198693049,"node_id":"MDU6SXNzdWUxOTg2OTMwNDk=","number":4058,"title":"Support UNIX timestamps (INTEGER) columns for SQL datetime field filter variables","user":{"login":"ralf-rf","id":21954488,"node_id":"MDQ6VXNlcjIxOTU0NDg4","avatar_url":"https://avatars3.githubusercontent.com/u/21954488?v=4","gravatar_id":"","url":"https://api.github.com/users/ralf-rf","html_url":"https://github.com/ralf-rf","followers_url":"https://api.github.com/users/ralf-rf/followers","following_url":"https://api.github.com/users/ralf-rf/following{/other_user}","gists_url":"https://api.github.com/users/ralf-rf/gists{/gist_id}","starred_url":"https://api.github.com/users/ralf-rf/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ralf-rf/subscriptions","organizations_url":"https://api.github.com/users/ralf-rf/orgs","repos_url":"https://api.github.com/users/ralf-rf/repos","events_url":"https://api.github.com/users/ralf-rf/events{/privacy}","received_events_url":"https://api.github.com/users/ralf-rf/received_events","type":"User","site_admin":false},"labels":[{"id":234606524,"node_id":"MDU6TGFiZWwyMzQ2MDY1MjQ=","url":"https://api.github.com/repos/metabase/metabase/labels/Proposal","name":"Proposal","color":"D7EDC2","default":false},{"id":314241023,"node_id":"MDU6TGFiZWwzMTQyNDEwMjM=","url":"https://api.github.com/repos/metabase/metabase/labels/Query%20Processor","name":"Query Processor","color":"68BEFC","default":false}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":6,"created_at":"2017-01-04T12:05:51Z","updated_at":"2018-07-19T14:37:35Z","closed_at":null,"author_association":"NONE","body":"Browser:  55.0.2883.95 (64-bit)\r\nOS: MacOS\r\nDatabase: BigQuery\r\nMetabase Version: v0.22.0\r\nHosted: Google Compute Engine\r\nInternal DB: H2\r\n\r\nI have created a SQL based question which uses the following structure:\r\n\r\n```sql\r\nSELECT [stats.app_events_recent_page.page_id] AS page_id, total as count, ratio * 100 AS percent FROM (\r\n SELECT [stats.app_events_recent_page.page_id], count(*) AS total, RATIO_TO_REPORT(total) OVER() AS ratio\r\n FROM [stats.app_events_recent_page]\r\n WHERE [[{{datetime}} AND ]] [stats.app_events_recent_page.event_name] = 'page_view'\r\n   AND [stats.app_events_recent_page.page_id] > 0 \r\n   [[AND retailer_name = {{kies_een_retailer}}]]\r\n   [[AND folder_version_id = {{folder_version_id}}]]\r\n GROUP by [stats.app_events_recent_page.page_id]\r\n ORDER BY [stats.app_events_recent_page.page_id] ASC\r\n) \r\n```\r\n\r\nI have created a Variable 'datetime' using a Field filter called TimestampMilliSeconds. The value of this field is in Milliseconds. The schema of the used table is also setup to treat the timestamp as an MS formatted one. The resulting query is:\r\n\r\n```sql\r\nSELECT [stats.app_events_recent_page.page_id] AS page_id, total as count, ratio * 100 AS percent FROM (\r\n SELECT [stats.app_events_recent_page.page_id], count(*) AS total, RATIO_TO_REPORT(total) OVER() AS ratio\r\n FROM [stats.app_events_recent_page]\r\n WHERE CAST(date([stats.app_events_recent_page.timestamp_micros]) AS timestamp) BETWEEN CAST('2016-12-28 00:00' AS timestamp) AND CAST('2017-01-03 00:00' AS timestamp) \r\n    AND  [stats.app_events_recent_page.event_name] = 'page_view'\r\n    AND [stats.app_events_recent_page.page_id] > 0 \r\n GROUP by [stats.app_events_recent_page.page_id]\r\n ORDER BY [stats.app_events_recent_page.page_id] ASC\r\n) \r\n```\r\n\r\nResulting in no results... because the timestamp is not converted to seconds first; but the MS are interpreteted as seconds instead.\r\n\r\nThe non custom SQL queries work as expected:\r\n```sql\r\nSELECT count(*) AS [count] \r\nFROM [stats.app_events_recent] \r\nWHERE ([stats.app_events_recent.event_name] = 'folder_view' \r\n  AND CAST(date(msec_to_timestamp([stats.app_events_recent.timestamp_micros])) AS timestamp) BETWEEN CAST(date(timestamp('2016-12-27T11:18:55.094Z')) AS timestamp) \r\n  AND CAST(date(timestamp('2017-01-03T11:18:55.094Z')) AS timestamp))\r\n````\r\n\r\nThe **msec_to_timestamp** part is missing in the result of custom SQL query. Looks like a small bug; but would be very nice if you could clean this one!"},"comment":{"url":"https://api.github.com/repos/metabase/metabase/issues/comments/406299186","html_url":"https://github.com/metabase/metabase/issues/4058#issuecomment-406299186","issue_url":"https://api.github.com/repos/metabase/metabase/issues/4058","id":406299186,"node_id":"MDEyOklzc3VlQ29tbWVudDQwNjI5OTE4Ng==","user":{"login":"eschultze","id":12034548,"node_id":"MDQ6VXNlcjEyMDM0NTQ4","avatar_url":"https://avatars1.githubusercontent.com/u/12034548?v=4","gravatar_id":"","url":"https://api.github.com/users/eschultze","html_url":"https://github.com/eschultze","followers_url":"https://api.github.com/users/eschultze/followers","following_url":"https://api.github.com/users/eschultze/following{/other_user}","gists_url":"https://api.github.com/users/eschultze/gists{/gist_id}","starred_url":"https://api.github.com/users/eschultze/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/eschultze/subscriptions","organizations_url":"https://api.github.com/users/eschultze/orgs","repos_url":"https://api.github.com/users/eschultze/repos","events_url":"https://api.github.com/users/eschultze/events{/privacy}","received_events_url":"https://api.github.com/users/eschultze/received_events","type":"User","site_admin":false},"created_at":"2018-07-19T14:37:35Z","updated_at":"2018-07-19T14:37:35Z","author_association":"NONE","body":"Found a workaround for sqlite if anyone is wondering\r\n`[[ AND datetime(timestamp/1000, 'unixepoch') >= {{Start}} ]] [[ AND datetime(timestamp/1000, 'unixepoch') <= {{End}} ]]`"}}
{"actor":{"display_login":"eschultze"}}