7.1.3应用市场的CRM在使用postgresql时有个时区的bug
大致原因是postgresql存储时间格式时是转换成UTC时间戳存储的，但接口用的是"2022:07-29 10:00:00"的时间格式，导致year函数出错，最好就是能让前端后台都携带标准时区格式db_1 | 2022-07-29 06:38:48.970 CST ERROR:function year(timestamp without time zone) does not exist at character 9
db_1 | 2022-07-29 06:38:48.970 CST HINT:No function matches the given name and argument types. You might need to add explicit type casts.
db_1 | 2022-07-29 06:38:48.970 CST STATEMENT:SELECT (year(t0.xcreateTime)||month(t0.xcreateTime)), COUNT(t0.xid) FROM WCRM_CUSTOMER t0 WHERE (t0.xowneruser = $1 AND t0.xowneruser IS NOT NULL AND t0.xcreateTime >= $2 AND t0.xcreateTime <= $3) GROUP BY (year(t0.xcreateTime)||month(t0.xcreateTime))
o2oa_1 | 2022-07-29 06:38:49.083 ERROR com.x.wcrm.assemble.control.jaxrs.statistic.StatisticAction - id:0e594592-99ad-4c27-a701-d989ac0eb2ea, name:com.x.wcrm.assemble.control.jaxrs.statistic.StatisticAction, message:ERROR: function year(timestamp without time zone) does not exist
o2oa_1 | Hint: No function matches the given name and argument types. You might need to add explicit type casts.
o2oa_1 | Position: 9 {prepstmnt 999630015 SELECT (year(t0.xcreateTime)||month(t0.xcreateTime)), COUNT(t0.xid) FROM WCRM_CUSTOMER t0 WHERE (t0.xowneruser = ? AND t0.xowneruser IS NOT NULL AND t0.xcreateTime >= ? AND t0.xcreateTime <= ?) GROUP BY (year(t0.xcreateTime)||month(t0.xcreateTime))} , exception:org.apache.openjpa.persistence.PersistenceException, id:0e594592-99ad-4c27-a701-d989ac0eb2ea, name:com.x.wcrm.assemble.control.jaxrs.statistic.StatisticAction, message:ERROR: function year(timestamp without time zone) does not exist
o2oa_1 | Hint: No function matches the given name and argument types. You might need to add explicit type casts.
o2oa_1 | Position: 9 {prepstmnt 999630015 SELECT (year(t0.xcreateTime)||month(t0.xcreateTime)), COUNT(t0.xid) FROM WCRM_CUSTOMER t0 WHERE (t0.xowneruser = ? AND t0.xowneruser IS NOT NULL AND t0.xcreateTime >= ? AND t0.xcreateTime <= ?) GROUP BY (year(t0.xcreateTime)||month(t0.xcreateTime))} , exception:org.apache.openjpa.persistence.PersistenceException, person:嘻嘻嘻@001@P, method:PUT, request:http://192.168.1.103/x_wcrm_assemble_control/jaxrs/statistic/countcustomerbymonth?v=7.1&l65m57o3, remoteHost:127.0.0.1, emoteAddr:127.0.0.1, head:Origin:http://192.168.1.103
o2oa_1 | Cookie:x-token=HIQH3aVxPGO-QL1iey0iaqiFKSomhzLDWucBO2mQlh9qyhsCBdts8CpS6szP6a7eqMFv5WuB9uI
o2oa_1 | Accept:text/html,application/json,*/*
o2oa_1 | User-Agent:Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:102.0) Gecko/20100101 Firefox/102.0
o2oa_1 | x-token:HIQH3aVxPGO-QL1iey0iaqiFKSomhzLDWucBO2mQlh9qyhsCBdts8CpS6szP6a7eqMFv5WuB9uI
o2oa_1 | Referer:http://192.168.1.103/x_desktop/app.html?app=CRM&status={}
o2oa_1 | X-Forwarded-Proto:http
o2oa_1 | X-Forwarded-Host:192.168.1.103
o2oa_1 | Host:192.168.1.103
o2oa_1 | Accept-Encoding:gzip, deflate
o2oa_1 | Via:1.1 db710cff11c5
o2oa_1 | Authorization:HIQH3aVxPGO-QL1iey0iaqiFKSomhzLDWucBO2mQlh9qyhsCBdts8CpS6szP6a7eqMFv5WuB9uI
o2oa_1 | X-Forwarded-For:192.168.1.222
o2oa_1 | Accept-Language:zh-CN
o2oa_1 | Content-Length:110
o2oa_1 | X-Real-IP:192.168.1.222
o2oa_1 | X-Forwarded-Server:172.18.0.4
o2oa_1 | Content-Type:application/json; charset=UTF-8, body:..
o2oa_1 | org.apache.openjpa.persistence.PersistenceException: ERROR: function year(timestamp without time zone) does not exist
o2oa_1 | Hint: No function matches the given name and argument types. You might need to add explicit type casts.
o2oa_1 | Position: 9 {prepstmnt 999630015 SELECT (year(t0.xcreateTime)||month(t0.xcreateTime)), COUNT(t0.xid) FROM WCRM_CUSTOMER t0 WHERE (t0.xowneruser = ? AND t0.xowneruser IS NOT NULL AND t0.xcreateTime >= ? AND t0.xcreateTime <= ?) GROUP BY (year(t0.xcreateTime)||month(t0.xcreateTime))}
o2oa_1 | at org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:5320) ~
o2oa_1 | at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:5280) ~
o2oa_1 | at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:134) ~
o2oa_1 | at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:115) ~
o2oa_1 | at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:67) ~
o2oa_1 | at org.apache.openjpa.jdbc.kernel.SelectResultObjectProvider.handleCheckedException(SelectResultObjectProvider.java:162) ~
o2oa_1 | at org.apache.openjpa.kernel.QueryImpl$PackingResultObjectProvider.handleCheckedException(QueryImpl.java:2235) ~
o2oa_1 | at org.apache.openjpa.lib.rop.EagerResultList.<init>(EagerResultList.java:42) ~
o2oa_1 | at org.apache.openjpa.kernel.QueryImpl.toResult(QueryImpl.java:1314) ~
o2oa_1 | at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:1061) ~
o2oa_1 | at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:911) ~
o2oa_1 | at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:842) ~
o2oa_1 | at org.apache.openjpa.kernel.DelegatingQuery.execute(DelegatingQuery.java:601) ~
o2oa_1 | at org.apache.openjpa.persistence.QueryImpl.execute(QueryImpl.java:297) ~
o2oa_1 | at org.apache.openjpa.persistence.QueryImpl.getResultList(QueryImpl.java:314) ~
o2oa_1 | at com.x.wcrm.assemble.control.factory.CustomerStatisticFactory.count_by_month(CustomerStatisticFactory.java:189) ~
o2oa_1 | at com.x.wcrm.assemble.control.jaxrs.statistic.Action_Customer_CountByMonth.execute(Action_Customer_CountByMonth.java:44) ~
o2oa_1 | at com.x.wcrm.assemble.control.jaxrs.statistic.StatisticAction.countCustomerByMonth(StatisticAction.java:118) ~
o2oa_1 | at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
o2oa_1 | at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:?]
o2oa_1 | at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
o2oa_1 | at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]
o2oa_1 | at org.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory.lambda$static$0(ResourceMethodInvocationHandlerFactory.java:52) ~
o2oa_1 | at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:124) ~
o2oa_1 | at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.invoke(AbstractJavaResourceMethodDispatcher.java:167) ~
o2oa_1 | at org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$VoidOutInvoker.doDispatch(JavaResourceMethodDispatcherProvider.java:159) ~
o2oa_1 | at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.dispatch(AbstractJavaResourceMethodDispatcher.java:79) ~
o2oa_1 | at org.glassfish.jersey.server.model.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:475) ~
o2oa_1 | at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:397) ~
o2oa_1 | at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:81) ~
o2oa_1 | at org.glassfish.jersey.server.ServerRuntime$1.run(ServerRuntime.java:255) ~
o2oa_1 | at org.glassfish.jersey.internal.Errors$1.call(Errors.java:248) ~
o2oa_1 | at org.glassfish.jersey.internal.Errors$1.call(Errors.java:244) ~
o2oa_1 | at org.glassfish.jersey.internal.Errors.process(Errors.java:292) ~
o2oa_1 | at org.glassfish.jersey.internal.Errors.process(Errors.java:274) ~
o2oa_1 | at org.glassfish.jersey.internal.Errors.process(Errors.java:244) ~
o2oa_1 | at org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:265) ~
o2oa_1 | at org.glassfish.jersey.server.ServerRuntime.process(ServerRuntime.java:234) ~
o2oa_1 | at org.glassfish.jersey.server.ApplicationHandler.handle(ApplicationHandler.java:684) ~
o2oa_1 | at org.glassfish.jersey.servlet.WebComponent.serviceImpl(WebComponent.java:394) ~
o2oa_1 | at org.glassfish.jersey.servlet.WebComponent.service(WebComponent.java:346) ~
o2oa_1 | at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:366) ~
o2oa_1 | at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:319) ~
o2oa_1 | at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:205) ~
o2oa_1 | at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:763) ~
o2oa_1 | at org.eclipse.jetty.servlet.ServletHandler$ChainEnd.doFilter(ServletHandler.java:1633) ~
o2oa_1 | at org.eclipse.jetty.websocket.server.WebSocketUpgradeFilter.doFilter(WebSocketUpgradeFilter.java:228) ~
o2oa_1 | at org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:193) ~
o2oa_1 | at org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1609) ~
o2oa_1 | at com.x.base.core.project.jaxrs.CipherManagerUserJaxrsFilter.doFilter(CipherManagerUserJaxrsFilter.java:50) ~
o2oa_1 | at org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:193) ~
o2oa_1 | at org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1609) ~
o2oa_1 | at com.alibaba.druid.support.http.WebStatFilter.doFilter(WebStatFilter.java:124) ~
o2oa_1 | at org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:193) ~
o2oa_1 | at org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1609) ~
o2oa_1 | at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:561) ~
o2oa_1 | at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143) ~
o2oa_1 | at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:602) ~
o2oa_1 | at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127) ~
o2oa_1 | at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:235) ~
o2oa_1 | at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1612) ~
o2oa_1 | at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233) ~
o2oa_1 | at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1434) ~
o2oa_1 | at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:188) ~
o2oa_1 | at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:501) ~
o2oa_1 | at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1582) ~
o2oa_1 | at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:186) ~
o2oa_1 | at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1349) ~
o2oa_1 | at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141) ~
o2oa_1 | at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:59) ~
o2oa_1 | at org.eclipse.jetty.server.handler.gzip.GzipHandler.handle(GzipHandler.java:716) ~
o2oa_1 | at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127) ~
o2oa_1 | at org.eclipse.jetty.server.Server.handle(Server.java:516) ~
o2oa_1 | at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:383) ~
o2oa_1 | at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:556) ~
o2oa_1 | at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:375) ~
o2oa_1 | at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:273) ~
o2oa_1 | at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311) ~
o2oa_1 | at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:105) ~
o2oa_1 | at org.eclipse.jetty.io.ChannelEndPoint$1.run(ChannelEndPoint.java:104) ~
o2oa_1 | at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:336) ~
o2oa_1 | at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:313) ~
o2oa_1 | at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:171) ~
o2oa_1 | at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:129) ~
o2oa_1 | at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:375) ~
o2oa_1 | at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:773) ~
o2oa_1 | at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:905) ~
o2oa_1 | at java.lang.Thread.run(Thread.java:834) ~[?:?]
o2oa_1 | Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: ERROR: function year(timestamp without time zone) does not exist
o2oa_1 | Hint: No function matches the given name and argument types. You might need to add explicit type casts.
o2oa_1 | Position: 9 {prepstmnt 999630015 SELECT (year(t0.xcreateTime)||month(t0.xcreateTime)), COUNT(t0.xid) FROM WCRM_CUSTOMER t0 WHERE (t0.xowneruser = ? AND t0.xowneruser IS NOT NULL AND t0.xcreateTime >= ? AND t0.xcreateTime <= ?) GROUP BY (year(t0.xcreateTime)||month(t0.xcreateTime))}
o2oa_1 | at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:219) ~
o2oa_1 | at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:203) ~
o2oa_1 | at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$700(LoggingConnectionDecorator.java:58) ~
o2oa_1 | at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingPreparedStatement.executeQuery(LoggingConnectionDecorator.java:1171) ~
o2oa_1 | at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:300) ~
o2oa_1 | at org.apache.openjpa.jdbc.sql.PostgresDictionary$PostgresPreparedStatement.executeQuery(PostgresDictionary.java:1099) ~
o2oa_1 | at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:300) ~
o2oa_1 | at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement.executeQuery(JDBCStoreManager.java:1870) ~
o2oa_1 | at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:290) ~
o2oa_1 | at org.apache.openjpa.jdbc.sql.SelectImpl.executeQuery(SelectImpl.java:531) ~
o2oa_1 | at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:456) ~
o2oa_1 | at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:423) ~
o2oa_1 | at org.apache.openjpa.jdbc.sql.LogicalUnion$UnionSelect.execute(LogicalUnion.java:477) ~
o2oa_1 | at org.apache.openjpa.jdbc.sql.LogicalUnion.execute(LogicalUnion.java:259) ~
o2oa_1 | at org.apache.openjpa.jdbc.sql.LogicalUnion.execute(LogicalUnion.java:248) ~
o2oa_1 | at org.apache.openjpa.jdbc.kernel.SelectResultObjectProvider.open(SelectResultObjectProvider.java:95) ~
o2oa_1 | at org.apache.openjpa.kernel.QueryImpl$PackingResultObjectProvider.open(QueryImpl.java:2187) ~
o2oa_1 | at org.apache.openjpa.lib.rop.EagerResultList.<init>(EagerResultList.java:36) ~[openjpa-3.2.0.jar:3.2.0
更正一下，刚仔细看了下github上的crm代码应该是postgresql没有这个year函数，对应postgresql用的是extract函数 已反馈给研发人员
页:
[1]