如何从 postgreSQL 中的时间戳中减去/添加分钟
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37173732/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to substract/add minutes from a timestamp in postgreSQL
提问by juanpscotto
I have the following scenario:
我有以下场景：
I have employees who register their check in/out from their work. But they have 10 minutes of tolerance.
我有员工在他们的工作中登记入住/退房。但他们有10分钟的容忍度。
The late entries I get with this view:
我用这个观点得到的迟到的条目：
CREATE OR REPLACE VIEW employees_late_entries
(
id,
created_datetime,
entry_datetime,
contact_id,
contact_name,
user_id,
employees_perm_id
)
AS
SELECT precence_records.id,
precence_records.created AS created_datetime,
("substring"(precence_records.created::text, 0, 11) || ' '::text) || contacts.entry_time::text AS entry_datetime,
contacts.id AS contact_id,
contacts.name AS contact_name,
precence_records.user_id,
precence_records.employees_perm_id
FROM precence_records,
contacts
WHERE
precence_records.type::text = 'entry'::text AND
contacts.employee = true AND
contacts.id = precence_records.contact_id AND
( ("substring"(precence_records.created::text, 0, 11) || ' '::text) || contacts.entry_time::text) < precence_records.created::text AND
precence_records.employees_perm_id IS NULL;
the precence_records.createdis the check in time and contacts.entry_timeits the time of the schedule entry time for the employee.
这precence_records.created是签到时间，contacts.entry_time它是员工的时间表输入时间的时间。
This is the condition contacts.entry_timevs precence_records.createdto get the late entries:
这是获得迟到条目的condition contacts.entry_timevs precence_records.created：
( ("substring"(precence_records.created::text, 0, 11) || ' '::text) || contacts.entry_time::text) < precence_records.created::text
So I wanna do something like that:
所以我想做这样的事情：
( ("substring"(precence_records.created::text, 0, 11) || ' '::text) || (contacts.entry_time::text + 10 MINUTES) ) < precence_records.created::text
DATA TYPES:
数据类型：
precence_records.created TIMESTAMP contacts.entry_time VARCHAR
precence_records.created TIMESTAMP contacts.entry_time VARCHAR
Can you help me please
你能帮我吗
回答by Ziggy Crueltyfree Zeitgeister
Dates, Times and Timestamps in PostgreSQL can be added/subtracted an INTERVAL value:
PostgreSQL 中的日期、时间和时间戳可以添加/减去一个 INTERVAL 值：
SELECT now()::time - INTERVAL '10 min'
If your timestamp field is varchar, you can cast it first to timestamp data type and then subtract the interval:
如果您的时间戳字段是 varchar，您可以先将其转换为时间戳数据类型，然后减去间隔：
( (left(precence_records.created::text, 11) || ' ') ||
(contacts.entry_time::time + INTERVAL '10min')::text )::timestamp <
precence_records.created::timestamp
