pgsql timestamp without time zone ＞ character varying解决方案

短命女 2023-09-27 12:11 139阅读 0赞

### 问题背景 ###

最近给一个项目从`mysql迁移到pgsql`中去，mysql中的日期大于小于等于操作符可以直接对string生效，会进行自动转换，但是在pgsql下不行，并且会触发`“Cause: org.postgresql.util.PSQLException: ERROR: operator does not exist: timestamp without time zone >= character varying”`，项目是`springboot`2+`mybatis plus`框架，该任务由定时任务执行。

报错信息：

//by zhengkai.blog.csdn.net
    org.springframework.jdbc.BadSqlGrammarException: 
    ### Error querying database.  Cause: org.postgresql.util.PSQLException: 错误: 操作符不存在: timestamp without time zone > character varying
      建议：没有匹配指定名称和参数类型的操作符. 您也许需要增加明确的类型转换.
      位置：418
    ### The error may exist in com/softdev/system/mapper/UnitMapper.java (best guess)
    ### The error may involve defaultParameterMap
    ### The error occurred while setting parameters
    ### SQL: SELECT  unit_id,tender_id,source,from_keyword,owner_unit,owner_unit_logic,bid_unit,bid_unit_logic,price,price_logic,specialist,province,city,bid_province,bid_city,open_id,url,tender_name,tender_name_format,sub_project,create_time,update_time,tags,tag_industry,status,business_status,fix_flag,detail_flag,multi_bid_flag,duplicate_flag,word_flag,remark  FROM unit     WHERE (status = ? AND source = ? AND create_time > ?) ORDER BY create_time DESC
    ### Cause: org.postgresql.util.PSQLException: 错误: 操作符不存在: timestamp without time zone > character varying
      建议：没有匹配指定名称和参数类型的操作符. 您也许需要增加明确的类型转换.
      位置：418
    ; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: 错误: 操作符不存在: timestamp without time zone > character varying
      建议：没有匹配指定名称和参数类型的操作符. 您也许需要增加明确的类型转换.
      位置：418
    	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101)
    	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
    	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79)
    	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79)
    	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91)
    	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
    	at jdk.proxy2/jdk.proxy2.$Proxy66.selectList(Unknown Source)
    	at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224)
    	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:166)
    	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:77)
    	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)
    	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
    	at jdk.proxy2/jdk.proxy2.$Proxy79.selectList(Unknown Source)
    	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
    	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
    	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208)
    	at jdk.proxy2/jdk.proxy2.$Proxy80.selectList(Unknown Source)
    	at com.softdev.system.service.FetchService.syncGGZY2(FetchService.java:632)
    	at com.softdev.system.task.GGZY_ZJCS_Task.run(GGZY_ZJCS_Task.java:38)
    	at com.softdev.system.controller.TaskController.ggzy_zjcs_Task(TaskController.java:152)
    	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
    	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
    	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:150)
    	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117)
    	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895)
    	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808)
    	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
    	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1067)
    	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:963)
    	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
    	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909)
    	at javax.servlet.http.HttpServlet.service(HttpServlet.java:681)
    	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
    	at javax.servlet.http.HttpServlet.service(HttpServlet.java:764)
    	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
    	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
    	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
    	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
    	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
    	at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
    	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
    	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
    	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
    	at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
    	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
    	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
    	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
    	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
    	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
    	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
    	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
    	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197)
    	at org.apache.catalina.core.StandardContextValve.__invoke(StandardContextValve.java:97)
    	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:41002)
    	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:540)
    	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
    	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
    	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
    	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357)
    	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:382)
    	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
    	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:895)
    	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1732)
    	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
    	at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
    	at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
    	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    	at java.base/java.lang.Thread.run(Thread.java:833)
    Caused by: org.postgresql.util.PSQLException: 错误: 操作符不存在: timestamp without time zone > character varying
      建议：没有匹配指定名称和参数类型的操作符. 您也许需要增加明确的类型转换.
      位置：418
    	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2674)
    	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2364)
    	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:354)
    	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:484)
    	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:404)
    	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:162)
    	at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:151)
    	at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497)
    	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
    	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
    	at jdk.internal.reflect.GeneratedMethodAccessor107.invoke(Unknown Source)
    	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:64)
    	at jdk.proxy2/jdk.proxy2.$Proxy105.query(Unknown Source)
    	at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
    	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
    	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
    	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
    	at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:81)
    	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62)
    	at jdk.proxy2/jdk.proxy2.$Proxy104.query(Unknown Source)
    	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151)
    	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145)
    	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
    	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
    	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427)
    	... 68 more

### 解决方案 ###

如果你没有用hutool，那么使用`Timestamp.valueOf(yourTime)` ，格式一般为"2022-10-01 00:00:00"；  
如果你有用hutool，那么可以使用`DateUtil.parse(dateStr,dateFmt)`，建议根据实际发生的业务以及针对系统所设置的日期格式对日期类型的String做处理。

原先的查询语句为

//by zhengkai.blog.csdn.net
    QueryWrapper<Unit> queryWrapper = new QueryWrapper<Unit>().eq("status",1).eq("source","广州公共资源交易中心").gt("create_time",configMapper.value("sync.start")).orderByDesc("create_time");

`Timestamp.valueOf(yourTime)`解决方案

//by zhengkai.blog.csdn.net
    QueryWrapper<Unit> queryWrapper = new QueryWrapper<Unit>().eq("status",1).eq("source","广州公共资源交易中心").gt("create_time",Timestamp.valueOf(configMapper.value("sync.start"))).orderByDesc("create_time");

`DateUtil.parse(dateStr,dateFmt)`解决方案（这里 sync.start 2022-06-01 为分析开始日期）

//by zhengkai.blog.csdn.net
    QueryWrapper<Unit> queryWrapper = new QueryWrapper<Unit>().eq("status",1).eq("source","广州公共资源交易中心").gt("create_time",DateUtil.parse(configMapper.value("sync.start"),"yyyy-MM-dd")).orderByDesc("create_time");

ok，错误解决了！