CODEWITHSUNDEEP
sqlpostgresql
juanpscotto
juanpscotto

Reputation: 1050

How to substract/add minutes from a timestamp in postgreSQL

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

Upvotes: 21

Views: 42939

Answers (2)

Saiyam Jain
Saiyam Jain

Reputation: 161

select getdate()- INTERVAL '10 min'

Even if you have to subtract days it can be done; just use " 10 days" instead of minutes.

Upvotes: 2

Ezequiel Tolnay
Ezequiel Tolnay

Reputation: 4572

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

Upvotes: 49

Related Questions