Postgresql difference between two timestamps in minutes In Postgresql. So I wanna do something like that: ( ("substring"(precence_records.created::text, 0, 11) || ' '::text) || (contacts.entry_time::text + 10 MINUTES) ) < precence_records. You may add or subtract an INTERVAL to a TIMESTAMP to How do I calculate hours. The number of measurements may vary by the hour. Times are rounded down to the nearest minute. 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 I want a PostgreSQL query where the Result column with the formula (current time value - (value at one hour before)) If you subtract Data of (7:45-6:45) you will get the Result value of 7:45. The precence_records.created is the check in time and contacts.entry_time its the time of the schedule entry time for the employee. Now, to work with TIMESTAMP and INTERVAL, you need to understand these few. Precence_records.employees_perm_id IS NULL add/subtract 1 minute from present date/time value psql select now + interval. ( ("substring"(precence_records.created::text, 0, 11) || ' '::text) || contacts.entry_time::text) < precence_records.created::text AND Precence_records.type::text = 'entry'::text ANDĬontacts.id = precence_ntact_id AND ("substring"(precence_records.created::text, 0, 11) || ' '::text) || contacts.entry_time::text AS entry_datetime, Precence_records.created AS created_datetime, The late entries I get with this view: CREATE OR REPLACE VIEW employees_late_entries I have employees who register their check in/out from their work.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |