postgresql - API results different response than SQL query

Question

const amIAvailable = (request, response) => {
    const { start_date, end_date, my_emp_id } = request.body;
    console.log(request.body, 'body')
    pool.query(
        `
        SELECT EXISTS (SELECT me.employee_id from (
            select counts.employee_id from
            (select e.employee_id, count(e.employee_id) review_count from emp_details_2 e 
                  left join project_allocation pa 
                  on e.employee_id = pa.employee_id 
                  left join project pr 
                  on pa.pid = pr.pid 
                  where pa.project_alloc_status='In Progress' AND pr.start_date NOT BETWEEN $1 AND $2
                  and pr.end_date NOT BETWEEN $1 AND $2
                  group by (e.employee_id))
                  counts 
                  inner join role_allocation ra 
                  on counts.employee_id = ra.employee_id 
                  where counts.review_count <= ra.inclusive_review) me
                  where me.employee_id = $3)
        `, [start_date, end_date, my_emp_id],
        (error, results) => {
            if (error) {
                throw error;
            }
            response.status(200).json(results.rows);
            console.log(results.rows, 'result')
        }
    );
};

question from:https://stackoverflow.com/questions/66047028/api-results-different-response-than-sql-query

Answer 1

Probably the reason is the way that date strings are treated. Replace this query line

and pr.end_date NOT BETWEEN $1 AND $2

with this one (i.e. make string-to-date conversion explicit and controlled)

and pr.end_date NOT BETWEEN to_date($1, 'MM/DD/YYYY') AND to_date($2, 'MM/DD/YYYY')

I would suggest that whenever dates, timestamps, time zones or encodings are concerned you always use explicit settings/rules and never rely on defaults.