如何从 postgreSQL 中的时间戳中减去/添加分钟 [英] How to substract/add minutes from a timestamp in postgreSQL

问题描述

我有以下场景:

我有员工在他们的工作中登记他们的签入/签出.但他们有 10 分钟的容忍度.

我用这个视图得到的迟到的条目:

CREATE OR REPLACE VIEWEmployees_late_entries(ID，创建日期时间，输入日期时间，联系 ID，联系人姓名，用户身份，雇员perm_id)作为选择 precence_records.id，precence_records.created AS created_datetime，("substring"(precence_records.created::text, 0, 11) || ' '::text) ||contacts.entry_time::text AS entry_datetime，contact.id AS contact_id,contacts.name AS contact_name,precence_records.user_id,precence_records.employees_perm_idFROM precence_records,联系人在哪里precence_records.type::text = 'entry'::text ANDcontact.employee = true ANDcontact.id = precence_records.contact_id AND( ("substring"(precence_records.created::text, 0, 11) || ' '::text) || contacts.entry_time::text) 

precence_records.created 是签到时间，contacts.entry_time 是员工计划进入时间的时间.

这是获取迟到条目的条件contacts.entry_time vs precence_records.created:

 ( ("substring"(precence_records.created::text, 0, 11) || ' '::text) || contacts.entry_time::text) 

所以我想做这样的事情:

 ( ("substring"(precence_records.created::text, 0, 11) || ' '::text) || (contacts.entry_time::text + 10 MINUTES) ) <precence_records.created::text

数据类型:

precence_records.created TIMESTAMPcontacts.entry_time VARCHAR

你能帮我吗

解决方案

PostgreSQL 中的日期、时间和时间戳可以添加/减去一个 INTERVAL 值:

SELECT now()::time - INTERVAL '10 min'

如果你的timestamp字段是varchar，你可以先把它转换成timestamp数据类型，然后减去间隔:

 ((left(precence_records.created::text, 11) || ' ') ||(contacts.entry_time::time + INTERVAL '10min')::text )::timestamp <precence_records.created::timestamp

I have the following scenario:

I have employees who register their check in/out from their work. But they have 10 minutes of tolerance.

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.created is the check in time and contacts.entry_time its the time of the schedule entry time for the employee.

This is the condition contacts.entry_time vs precence_records.created to get the late entries:

 ( ("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

Can you help me please

解决方案

Dates, Times and Timestamps in PostgreSQL can be added/subtracted an INTERVAL value:

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:

 ( (left(precence_records.created::text, 11) || ' ') ||
   (contacts.entry_time::time + INTERVAL '10min')::text )::timestamp <
 precence_records.created::timestamp

这篇关于如何从 postgreSQL 中的时间戳中减去/添加分钟的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！