{"action":"closed","issue":{"url":"https://api.github.com/repos/SparklineData/spark-druid-olap/issues/24","repository_url":"https://api.github.com/repos/SparklineData/spark-druid-olap","labels_url":"https://api.github.com/repos/SparklineData/spark-druid-olap/issues/24/labels{/name}","comments_url":"https://api.github.com/repos/SparklineData/spark-druid-olap/issues/24/comments","events_url":"https://api.github.com/repos/SparklineData/spark-druid-olap/issues/24/events","html_url":"https://github.com/SparklineData/spark-druid-olap/issues/24","id":139085488,"number":24,"title":"handle spark datetime functions as grouping expressions","user":{"login":"hbutani","id":1130959,"avatar_url":"https://avatars.githubusercontent.com/u/1130959?v=3","gravatar_id":"","url":"https://api.github.com/users/hbutani","html_url":"https://github.com/hbutani","followers_url":"https://api.github.com/users/hbutani/followers","following_url":"https://api.github.com/users/hbutani/following{/other_user}","gists_url":"https://api.github.com/users/hbutani/gists{/gist_id}","starred_url":"https://api.github.com/users/hbutani/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/hbutani/subscriptions","organizations_url":"https://api.github.com/users/hbutani/orgs","repos_url":"https://api.github.com/users/hbutani/repos","events_url":"https://api.github.com/users/hbutani/events{/privacy}","received_events_url":"https://api.github.com/users/hbutani/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":{"login":"hbutani","id":1130959,"avatar_url":"https://avatars.githubusercontent.com/u/1130959?v=3","gravatar_id":"","url":"https://api.github.com/users/hbutani","html_url":"https://github.com/hbutani","followers_url":"https://api.github.com/users/hbutani/followers","following_url":"https://api.github.com/users/hbutani/following{/other_user}","gists_url":"https://api.github.com/users/hbutani/gists{/gist_id}","starred_url":"https://api.github.com/users/hbutani/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/hbutani/subscriptions","organizations_url":"https://api.github.com/users/hbutani/orgs","repos_url":"https://api.github.com/users/hbutani/repos","events_url":"https://api.github.com/users/hbutani/events{/privacy}","received_events_url":"https://api.github.com/users/hbutani/received_events","type":"User","site_admin":false},"milestone":null,"comments":1,"created_at":"2016-03-07T20:30:32Z","updated_at":"2016-03-08T04:53:49Z","closed_at":"2016-03-08T04:53:49Z","body":"1. Show by YEAR in Tableau \r\nSELECT YEAR(CAST(CONCAT(TO_DATE(lineitem.l_shipdate),' 00:00:00') AS TIMESTAMP)) AS yr_l_shipdate_ok FROM ( select * from lineitembase ) lineitem JOIN ( select * from orders ) orders ON (lineitem.l_orderkey = orders.o_orderkey) JOIN ( select * from customer ) customer ON (orders.o_custkey = customer.c_custkey) JOIN ( select * from custnation ) custnation ON (customer.c_nationkey = custnation.cn_nationkey) JOIN ( select * from custregion ) custregion ON (custnation.cn_regionkey = custregion.cr_regionkey) GROUP BY YEAR(CAST(CONCAT(TO_DATE(lineitem.l_shipdate),' 00:00:00') AS TIMESTAMP))\r\n\r\n2.SELECT CAST(FROM_UNIXTIME(UNIX_TIMESTAMP(CAST(CONCAT(TO_DATE(lineitem.l_shipdate),' 00:00:00') AS TIMESTAMP)), 'yyyy-MM-01 00:00:00') AS TIMESTAMP) AS tmn_l_shipdate_ok FROM ( select * from lineitembase ) lineitem JOIN ( select * from orders ) orders ON (lineitem.l_orderkey = orders.o_orderkey) JOIN ( select * from customer ) customer ON (orders.o_custkey = customer.c_custkey) JOIN ( select * from custnation ) custnation ON (customer.c_nationkey = custnation.cn_nationkey) JOIN ( select * from custregion ) custregion ON (custnation.cn_regionkey = custregion.cr_regionkey) GROUP BY CAST(FROM_UNIXTIME(UNIX_TIMESTAMP(CAST(CONCAT(TO_DATE(lineitem.l_shipdate),' 00:00:00') AS TIMESTAMP)), 'yyyy-MM-01 00:00:00') AS TIMESTAMP)\r\n\r\n3. SELECT SUM(lineitem.l_extendedprice) AS sum_l_extendedprice_ok, CAST(CONCAT(TO_DATE(CAST(CONCAT(TO_DATE(lineitem.l_shipdate),' 00:00:00') AS TIMESTAMP)), ' 00:00:00') AS TIMESTAMP) AS tdy_l_shipdate_ok FROM ( select * from lineitembase ) lineitem JOIN ( select * from orders ) orders ON (lineitem.l_orderkey = orders.o_orderkey) JOIN ( select * from customer ) customer ON (orders.o_custkey = customer.c_custkey) JOIN ( select * from custnation ) custnation ON (customer.c_nationkey = custnation.cn_nationkey) JOIN ( select * from custregion ) custregion ON (custnation.cn_regionkey = custregion.cr_regionkey) GROUP BY CAST(CONCAT(TO_DATE(CAST(CONCAT(TO_DATE(lineitem.l_shipdate),' 00:00:00') AS TIMESTAMP)), ' 00:00:00') AS TIMESTAMP)"}}