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

[GitBox <gi...@apache.org>]

zhaoyongjie commented 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**, 
   
   ```
   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: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org