Как получить разницу между временем начала и окончания и суммировать все записи
У меня есть таблица с именем «Время отправки работника», и в ней есть два поля с именами «Начало» и «Конец» (оба имеют метку времени):
Если у меня есть все записи, я хочу получить разницу в часах от времени начала и окончания, а также общий подсчет того, сколько раз он работал, поэтому мне нужно сначала сделать разницу в часах, а затем вычислить всю разницу в часах со всеми записями. Мой текущий запрос таков:
SELECT
wst.id,
wst.start_time as start,
wst.end_time as end,
l.name,
FROM workers_send_times wst
INNER JOIN workers_plan wp
ON wp.id = wst.workers_plan_id
LEFT JOIN location_orders lo
ON lo.id = wp.location_orders_id
LEFT JOIN location l
ON l.id = lo.location_id
WHERE wst.user_id = $2
AND extract(MONTH FROM start) = $3 AND extract(YEAR FROM end) = $6
ORDER BY wst.id, wst.send_start_at
в запросе я хочу показать записи только за определенный месяц и год. Но как мне получить разницу в часах и суммировать все значения?
@AdrianKlaver различается не только датой начала и окончания, потому что он работал с 12:00 до 16:00, тогда я хочу получить значение 4, потому что это разница в 4 часа, а затем вычислить все часы
Пожалуйста, просмотрите минимальный воспроизводимый пример, а затем предоставьте DDL (включая индексы), образцы данных и ожидаемый результат (это будет гораздо полезнее, чем просто описание желаемого поведения). Также обновите тег, чтобы указать версию PostgreSQL. Поскольку $2, $3 и $6 есть в запросе, а $1, $4 и $5 нет, я предполагаю, что это фрагмент, а не весь запрос. Извлечение месяца и года вместо поиска по фактическому диапазону дат неэффективно.
Затем посмотрите, работает ли что-то вроде: SELECT count(wst.id), sum(wst.end_time - wst.start_time as total_hrs FROM ... GROUP BY wst.id?
Ответы 1
Поскольку примеров данных нет, я воспользуюсь некоторыми фиктивными на основе вашего утверждения:
I have a table named Worker Send Times and in there are two fields
named start and end (both are timestamp):
Надеемся, это поможет вам скорректировать свой код...
WITH -- S a m p l e D a t a :
wst ( id, start_time, end_time ) AS
( Select 1, TIMESTAMP '2024-07-27 09:00:00', TIMESTAMP '2024-07-27 17:00:00' Union All
Select 1, TIMESTAMP '2024-07-28 08:00:00', TIMESTAMP '2024-07-28 14:00:00' Union All
Select 1, TIMESTAMP '2024-07-29 09:00:00', TIMESTAMP '2024-07-29 16:00:00' Union All
Select 2, TIMESTAMP '2024-07-27 12:00:00', TIMESTAMP '2024-07-27 16:00:00' Union All
Select 2, TIMESTAMP '2024-07-28 11:00:00', TIMESTAMP '2024-07-28 17:00:00' Union All
Select 3, TIMESTAMP '2024-07-28 09:00:00', TIMESTAMP '2024-07-28 11:00:00' Union All
Select 3, TIMESTAMP '2024-07-29 14:00:00', TIMESTAMP '2024-07-29 21:00:00'
)
-- S Q L : (aggregated)
Select id,
Sum(end_time - start_time) as id_total_hours,
Count(id) as id_total_count
From wst
Group By id
Order By id
/* R e s u l t :
id id_total_hours id_total_count
-- -------------- --------------
1 21:00:00 3
2 10:00:00 2
3 09:00:00 2 */
... и если вы хотите получить его построчно...
-- S Q L : (row by row)
Select id, start_time, end_time,
end_time - start_time as id_day_hours,
Sum( end_time - start_time ) Over( Partition By id ) as id_total_hours,
Count(id) Over( Partition By id ) as id_total_count
From wst
Order By id, start_time
/* R e s u l t :
id start_time end_time id_day_hours id_total_hours id_total_count
-- -------------------- -------------------- ------------ -------------- --------------
1 2024-07-27 09:00:00 2024-07-27 17:00:00 08:00:00 21:00:00 3
1 2024-07-28 08:00:00 2024-07-28 14:00:00 06:00:00 21:00:00 3
1 2024-07-29 09:00:00 2024-07-29 16:00:00 07:00:00 21:00:00 3
2 2024-07-27 12:00:00 2024-07-27 16:00:00 04:00:00 10:00:00 2
2 2024-07-28 11:00:00 2024-07-28 17:00:00 06:00:00 10:00:00 2
3 2024-07-28 09:00:00 2024-07-28 11:00:00 02:00:00 09:00:00 2
3 2024-07-29 14:00:00 2024-07-29 21:00:00 07:00:00 09:00:00 2 */
Хотите различать по местоположению, например
l.name?