Process not behaving the way it should.

Products

• Clarity PPM SaaS

Issue/Introduction

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'),
...

 

 

Environment

Release : 16.1.2

Resolution

The dates are indeed saved in UTC/GMT in the database.

 

You may want to use hours as an interval instead of days.

 

 

For example

 

 

-- get hh:mm:ss

to_char(r1.date_of_termination::date,'yyyy-mm-dd hh:mm:ss') as real_date_of_termination,

-- subtract hours from date/timestamp

to_char((r1.date_of_termination - INTERVAL '6 HOURS')::date,'yyyy-mm-dd hh:mm:ss') as minus_hours_date_of_termination,

-- include offset from GMT, "0600" or "0500", etc...

r1.date_of_termination at TIME ZONE 'UTC' as tz_date_of_termination,