i'm using postgres and i want tried date_diff, but i get this error
我正在使用postgres,我想尝试date_diff,但我得到这个错误
Warning: pg_query(): Query failed: ERROR: operator does not exist: boolean = integer LINE 1
警告:pg_query():查询失败:ERROR:运算符不存在:boolean = integer LINE 1
and then this is my query
然后这是我的查询
select id_mitra
,company_name
from ref_mitra
where reject=0 and DATE_DIFF(now(),date_status) > 3 and active='N'
what wrong in my query?
我的查询有什么问题?
2 个解决方案
#1
Cast your value 0 to boolean:
将值0转换为布尔值:
select id_mitra
,company_name
from ref_mitra
where reject = CAST(0 as boolean)
and DATE_DIFF(now(),date_status) > 3
and active='N';
Or use a boolean:
或者使用布尔值:
select id_mitra
,company_name
from ref_mitra
where reject = false
and DATE_DIFF(now(),date_status) > 3
and active='N';
DATE_DIFF() isn't a standard function in PostgreSQL, but maybe you created a function using this name.
DATE_DIFF()不是PostgreSQL中的标准函数,但也许您使用此名称创建了一个函数。
#2
You have two errors in your query:
您的查询中有两个错误:
- you are comparing a number (
0
) to aboolean
- there is no
date_diff()
function in Postgres
您正在将数字(0)与布尔值进行比较
Postgres中没有date_diff()函数
Assuming date_status
is a (real) date
column, your query should look like this:
假设date_status是(实际)日期列,您的查询应如下所示:
select id_mitra
,company_name
from ref_mitra
where reject = false
and current_date - date_status > 3
and active='N';
now()
returns a timestamp
but as you apparently want the number of days (not an interval
) you should use current_date
instead of now()
.
now()返回一个时间戳,但是你显然想要天数(不是间隔),你应该使用current_date而不是now()。
I am not sure what date_diff()
really does, maybe you need to write date_status - current_date
to get the same behaviour.
我不确定date_diff()到底是做什么的,也许你需要编写date_status - current_date来获得相同的行为。
Instead of reject = false
you can also use where not reject
而不是reject = false,你也可以使用不拒绝的地方
#1
Cast your value 0 to boolean:
将值0转换为布尔值:
select id_mitra
,company_name
from ref_mitra
where reject = CAST(0 as boolean)
and DATE_DIFF(now(),date_status) > 3
and active='N';
Or use a boolean:
或者使用布尔值:
select id_mitra
,company_name
from ref_mitra
where reject = false
and DATE_DIFF(now(),date_status) > 3
and active='N';
DATE_DIFF() isn't a standard function in PostgreSQL, but maybe you created a function using this name.
DATE_DIFF()不是PostgreSQL中的标准函数,但也许您使用此名称创建了一个函数。
#2
You have two errors in your query:
您的查询中有两个错误:
- you are comparing a number (
0
) to aboolean
- there is no
date_diff()
function in Postgres
您正在将数字(0)与布尔值进行比较
Postgres中没有date_diff()函数
Assuming date_status
is a (real) date
column, your query should look like this:
假设date_status是(实际)日期列,您的查询应如下所示:
select id_mitra
,company_name
from ref_mitra
where reject = false
and current_date - date_status > 3
and active='N';
now()
returns a timestamp
but as you apparently want the number of days (not an interval
) you should use current_date
instead of now()
.
now()返回一个时间戳,但是你显然想要天数(不是间隔),你应该使用current_date而不是now()。
I am not sure what date_diff()
really does, maybe you need to write date_status - current_date
to get the same behaviour.
我不确定date_diff()到底是做什么的,也许你需要编写date_status - current_date来获得相同的行为。
Instead of reject = false
you can also use where not reject
而不是reject = false,你也可以使用不拒绝的地方