[GitHub] [superset] zhaoyongjie edited a comment on pull request #18569: feat(explore): Allow using time formatter on temporal columns in data table

Mon, 07 Feb 2022 06:38:17 -0800 


zhaoyongjie edited a comment on pull request #18569:
URL: https://github.com/apache/superset/pull/18569#issuecomment-1031532537


   Thanks for your hard work. Before I reviewed the code, I did some 
experiments. 
   
   1. Let us use this SQL(PG) to make a virtual dataset, then open it in 
explore.
   ```
   select
     '2022-01-03 00:00:00'::TIMESTAMP as datetime0,
     '2022-02-07 00:00:00'::TIMESTAMP as datetime1
   UNION ALL
     SELECT '2022-01-04 00:00:00'::TIMESTAMP,
     '2022-02-08 00:00:00'::TIMESTAMP
   UNION ALL
     SELECT '2022-01-05 00:00:00'::TIMESTAMP,
     '2022-02-09 00:00:00'::TIMESTAMP
   UNION ALL
     SELECT '2022-01-06 00:00:00'::TIMESTAMP,
     '2022-02-10 00:00:00'::TIMESTAMP
   UNION ALL
     SELECT '2022-01-07 00:00:00'::TIMESTAMP,
     '2022-02-11 00:00:00'::TIMESTAMP
   UNION ALL
     SELECT '2022-01-08 00:00:00'::TIMESTAMP,
     '2022-02-12 00:00:00'::TIMESTAMP
   UNION ALL
     SELECT '2022-01-09 00:00:00'::TIMESTAMP,
     '2022-02-13 00:00:00'::TIMESTAMP
   UNION ALL
     SELECT '2022-01-10 00:00:00'::TIMESTAMP,
     '2022-02-14 00:00:00'::TIMESTAMP
   UNION ALL
     SELECT '2022-01-11 00:00:00'::TIMESTAMP,
     '2022-02-15 00:00:00'::TIMESTAMP
   UNION ALL
     SELECT '2022-01-12 00:00:00'::TIMESTAMP,
     '2022-02-16 00:00:00'::TIMESTAMP
   UNION ALL
     SELECT '2022-01-13 00:00:00'::TIMESTAMP,
     '2022-02-17 00:00:00'::TIMESTAMP
   UNION ALL
     SELECT '2022-01-14 00:00:00'::TIMESTAMP,
     '2022-02-18 00:00:00'::TIMESTAMP
   UNION ALL
     SELECT '2022-01-15 00:00:00'::TIMESTAMP,
     '2022-02-19 00:00:00'::TIMESTAMP
   UNION ALL
     SELECT '2022-01-16 00:00:00'::TIMESTAMP,
     '2022-02-20 00:00:00'::TIMESTAMP
   ```
   
   2. Select *table*, put datatime0 and datetime1 to **groupby control**, put 
**count(*)** to metric, select **month** grain
   
   3. viz result and query result is different.
   <img width="1279" alt="image" 
src="https://user-images.githubusercontent.com/2016594/152806687-5b8460a5-4cfa-4010-b84e-000ba18c22ee.png";>
   
   This problem was not actually introduced by this PR, but it is a 
long-standing one.
   
   let us check SQL, we can see time function only apply on **main time 
column**, while **datetime1** wrongly uses the time drill-down function in the 
frontend. 
   
   IMO, We'd better fix the original issue before we merge this PR into master.
   
   ```
   SELECT DATE_TRUNC('month', datetime0) AS datetime0,
          datetime1 AS datetime1,
          count(*) AS count
   FROM
     (select '2022-01-03 00:00:00'::TIMESTAMP as datetime0,
             '2022-02-07 00:00:00'::TIMESTAMP as datetime1
      UNION ALL SELECT '2022-01-04 00:00:00'::TIMESTAMP,
                       '2022-02-08 00:00:00'::TIMESTAMP
      UNION ALL SELECT '2022-01-05 00:00:00'::TIMESTAMP,
                       '2022-02-09 00:00:00'::TIMESTAMP
      UNION ALL SELECT '2022-01-06 00:00:00'::TIMESTAMP,
                       '2022-02-10 00:00:00'::TIMESTAMP
      UNION ALL SELECT '2022-01-07 00:00:00'::TIMESTAMP,
                       '2022-02-11 00:00:00'::TIMESTAMP
      UNION ALL SELECT '2022-01-08 00:00:00'::TIMESTAMP,
                       '2022-02-12 00:00:00'::TIMESTAMP
      UNION ALL SELECT '2022-01-09 00:00:00'::TIMESTAMP,
                       '2022-02-13 00:00:00'::TIMESTAMP
      UNION ALL SELECT '2022-01-10 00:00:00'::TIMESTAMP,
                       '2022-02-14 00:00:00'::TIMESTAMP
      UNION ALL SELECT '2022-01-11 00:00:00'::TIMESTAMP,
                       '2022-02-15 00:00:00'::TIMESTAMP
      UNION ALL SELECT '2022-01-12 00:00:00'::TIMESTAMP,
                       '2022-02-16 00:00:00'::TIMESTAMP
      UNION ALL SELECT '2022-01-13 00:00:00'::TIMESTAMP,
                       '2022-02-17 00:00:00'::TIMESTAMP
      UNION ALL SELECT '2022-01-14 00:00:00'::TIMESTAMP,
                       '2022-02-18 00:00:00'::TIMESTAMP
      UNION ALL SELECT '2022-01-15 00:00:00'::TIMESTAMP,
                       '2022-02-19 00:00:00'::TIMESTAMP
      UNION ALL SELECT '2022-01-16 00:00:00'::TIMESTAMP,
                       '2022-02-20 00:00:00'::TIMESTAMP) AS virtual_table
   GROUP BY DATE_TRUNC('month', datetime0),
            datetime1
   ORDER BY count DESC
   LIMIT 1000;
   ```
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: [email protected]

For queries about this service, please contact Infrastructure at:
[email protected]



---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to