Обработка 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<>рабочий пример здесь

Обратите внимание, что мой запрос выводит местную дату и время для Азии/Куала-Лумпур:

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