You have a process that queries based on all rows after a certain date using a postgresql INTERVAL of one day. However, some rows come back for the previous day or are missing.
This process inactivates any terminated resources from the months prior to current month. For eg. In June, it will take last month’s last date i.e 5/31 and inactivate any resources before or on 5/31.
Issue: You have identified an issue where in DB the termination date of user was showing later day while in clarity it showed the actual termination. So you tweaked the query to take termination day – 1 day (to record actual termination date). However, now you are observing that the terminations of current month are also taking place.
It is not accounting for <= condition. You have also tried < as well but it doesn’t work either.
select c.id userid, c.user_name userName,
c.user_status_id status,
c.first_name firstName,
c.last_name lastName,
c.email_address userEmail,
to_char((r1.date_of_termination -INTERVAL'1 DAY')::date,'yyyy-mm-dd')as termination date,
to_char(r1.date_of_termination::date,'yyyy-mm-dd'),
...
Release : 16.1.2
-- get hh:mm:ss
to_char(r1.date_of_
-- subtract hours from date/timestamp
to_char((r1.date_of_
-- include offset from GMT, "0600" or "0500", etc...
r1.date_of_termination at TIME ZONE 'UTC' as tz_date_of_termination,