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')
}
);
};
我已经写了这个接口,它返回特定日期的false ...但是,当我在pgadmin中使用相同的值参数运行相同的查询时,它返回的是true
我不能理解他的行为。日期采用MM/DD/YYYY格式。
发布于 2021-02-04 21:59:37
原因可能是日期字符串的处理方式。替换此查询行
and pr.end_date NOT BETWEEN $1 AND $2使用这个(即显式和受控的字符串到日期的转换)
and pr.end_date NOT BETWEEN to_date($1, 'MM/DD/YYYY') AND to_date($2, 'MM/DD/YYYY')我建议,只要涉及日期、时间戳、时区或编码,您总是使用明确的设置/规则,而不是依赖默认设置。
https://stackoverflow.com/questions/66047028
复制相似问题