带日期或不带时区的带有日期或时间戳的查询中的generate_series（）处理

数据库设计

考虑对设置进行一些修改：

CREATE TABLE employee (
  id           int PRIMARY KEY  -- !
, name         text             -- do NOT use char(n) !
, division_id  int
);

CREATE  TABLE attendance (
  id             int PRIMARY KEY  --!
, employee_id    int NOT NULL REFERENCES employee -- FK!
, activity_type  int
, created_at     timestamptz NOT NULL
);

定义PK使得汇总行变得更加容易，因为PK覆盖了GROUP BY子句中的整个行。看到：

• 当按键聚合时，为什么不能从“ GROUP BY”中排除从属列？

我不会使用“名称”作为列名称。这不是描述性的。每隔一列可以被命名为“名称”。考虑：

• 使用数据类型“文本”存储字符串有什么缺点？
• 如何在PostgreSQL中实现多对多关系？

询问

SELECT *
FROM  (        -- complete employee/date grid for division in range
   SELECT g.d::date AS the_date, id AS employee_id, name, division_id
   FROM  (
      SELECT generate_series(MIN(created_at) AT TIME ZONE 'Asia/Kuala_Lumpur'
                           , MAX(created_at) AT TIME ZONE 'Asia/Kuala_Lumpur'
                           , interval '1 day')
      FROM   attendance
      ) g(d)
   CROSS  JOIN employee e
   WHERE  e.division_id = 1
   ) de
LEFT   JOIN (  -- checkins & checkouts per employee/date for division in range
   SELECT employee_id, ts::date AS the_date
        , array_agg(id) as rows
        , min(ts)             FILTER (WHERE activity_type = 1) AS min_check_in
        , max(ts)             FILTER (WHERE activity_type = 2) AS max_check_out
        , array_agg(ts::time) FILTER (WHERE activity_type = 1) AS check_ins
        , array_agg(ts::time) FILTER (WHERE activity_type = 2) AS check_outs
   FROM  (
      SELECT a.id, a.employee_id, a.activity_type, a.created_at AT TIME ZONE 'Asia/Kuala_Lumpur' AS ts  -- convert to timestamp
      FROM   employee   e
      JOIN   attendance a ON a.employee_id = e.id
   -- WHERE  a.created_at >= timestamp '2020-11-20' AT TIME ZONE 'Asia/Kuala_Lumpur' -- "sargable" expressions
   -- AND    a.created_at <  timestamp '2020-11-21' AT TIME ZONE 'Asia/Kuala_Lumpur' -- exclusive upper bound (includes all of 2020-11-20);
      AND    e.division_id = 1
      ORDER  BY a.employee_id, a.created_at, a.activity_type  -- optional to guarantee sorted arrays
   ) sub
   GROUP  BY 1, 2
   ) a USING (the_date, employee_id)
ORDER  BY 1, 2;

db <>在这里拨弄

请注意，我的查询输出了Asia / Kuala_Lumpur的本地日期和时间：

test=> SELECT timestamptz '2020-11-20 08:52:01 +0' AT TIME ZONE 'Asia/Kuala_Lumpur' AS local_ts;
      local_ts       
---------------------
 2020-11-20 16:52:01

从哪儿开始？您需要了解时区的概念以及Postgres数据类型timestamp with time zone（timestamptz）与timestamp without time zone（timestamp）。否则，这将是无休止的混乱。从这里开始：

• 在Rails和PostgreSQL中完全忽略时区

最值得注意的是，timestamptz也没有存储时区：

• 数据类型为“带时区的时间戳”的时区存储

当简单地转换timestamptz为date或时timestamp，将假定会话的当前时区设置。不是你想要的。为该AT TIME ZONE构造显式提供一个时区，以免发生这种情况。在您的小提琴中，您同时拥有：

  ...
  , generate_series(
        startdate::timestamp AT TIME ZONE 'Asia/Kuala_Lumpur', 
        enddate::timestamp AT TIME ZONE 'Asia/Kuala_Lumpur', 
        interval '1 day') g(d)
   ...

也没有做你想做的。转换为（错误！）后timestamp，该AT TIME ZONE构造将值转换回timestamptz。

此外，您的查询会生成所有用户的完整笛卡尔乘积以及表中的最大天数范围attendance，只是使用以下方法将其缩减为单天：

    where created_at >= timestamp '2020-11-20' AT TIME ZONE 'Asia/Kuala_Lumpur'
    and   created_at <  timestamp '2020-11-20' AT TIME ZONE 'Asia/Kuala_Lumpur' + interval '1 day'

该WHERE子句最终完成了应做的事情。但是先生成完整的日期，只丢弃其中的大部分时间是没有意义的。（似乎您是在此同时从我的其他提琴中复制过来的？）

我注释掉了该WHERE子句，并generate_series()在查询中保留了您的优化版本作为概念证明。进一步阅读：

• 在PostgreSQL中生成两个日期之间的时间序列