Filter with timestamp and postgresql #541

khanhpd2002 · 2024-05-06T14:12:40Z

khanhpd2002
May 6, 2024

How can I find all record created after :date with endpoint _expands (for join) with Postgresql
I did
"filter" : "created_at>'2024-05-06'"
but it still not works and return me error
"ERROR: operator does not exist: timestamp without time zone > character varying\n Hint: No operator matches the given name and argument types. You might need to add explicit type casts.\n Position: 593",

How to solve it and

Answered by grabdoc

May 6, 2024

This is a defect. Will be create an issue and resolve it and you should get it as part of release 0.3.3 soon.
@khanhpd2002 - do you mind joining our discord please so that we can assist you better.

View full answer

grabdoc · 2024-05-06T22:13:17Z

grabdoc
May 6, 2024
Collaborator

This is a defect. Will be create an issue and resolve it and you should get it as part of release 0.3.3 soon.
@khanhpd2002 - do you mind joining our discord please so that we can assist you better.

5 replies

khanhpd2002 May 7, 2024
Author

Of course, I found link to join your community yesterday but it seem an invalid link

kdhrubo May 7, 2024
Maintainer

Please use the following link to join. We will fix readme

https://discord.gg/deQgNkgt8b

kdhrubo May 7, 2024
Maintainer

BTW the fix has been added and will be released over the weekend.

khanhpd2002 May 8, 2024
Author

I saw new release version, is that for this issue?

kdhrubo May 8, 2024
Maintainer

Yes

grabdoc · 2024-05-07T01:29:24Z

grabdoc
May 7, 2024
Collaborator

`
CREATE TABLE joinplay.tbl_employees (
id serial2 NOT NULL ,
full_name varchar(100) NOT NULL,
created_at date NOT NULL DEFAULT CURRENT_DATE
);

insert into tbl_employees (full_name, created_at) values ('John Travolta', '2005-01-01');

insert into tbl_employees (full_name, created_at) values ('Al Pacino', '2006-05-20');

`

0 replies

grabdoc · 2024-05-07T01:30:26Z

grabdoc
May 7, 2024
Collaborator

http GET 'http://localhost:8080/tbl_employees?filter=created_at>2004-01-01' \ User-Agent:insomnia/8.6.1

0 replies

grabdoc · 2024-05-07T01:33:48Z

grabdoc
May 7, 2024
Collaborator

Note that at this time only ISO date and date time formats are supported.

0 replies

grabdoc · 2024-05-07T02:21:24Z

grabdoc
May 7, 2024
Collaborator

`
CREATE TABLE joinplay.tbl_depts (
id serial2 NOT NULL ,
dept_name varchar(100) NOT NULL,
created_at TIMESTAMP NOT NULL
);

insert into tbl_depts (dept_name, created_at) values ('Actor', '2016-06-20 19:11:31');

insert into tbl_depts (dept_name, created_at) values ('Director', '2018-06-21 19:10:31');
`

0 replies

grabdoc · 2024-05-07T02:22:19Z

grabdoc
May 7, 2024
Collaborator

`
http GET 'http://localhost:8080/tbl_depts?filter=created_at>2016-09-30T15:30:00+01:00'
User-Agent:insomnia/8.6.1

`

0 replies

grabdoc · 2024-05-07T02:27:08Z

grabdoc
May 7, 2024
Collaborator

`

CREATE TABLE joinplay.tbl_student (
id serial2 NOT NULL ,
full_name varchar(100) NOT NULL,
created_at TIMESTAMPTZ NOT NULL
);

insert into tbl_student (full_name, created_at) values ('Issac Newton', '2016-06-22 19:10:25-07');

insert into tbl_student (full_name, created_at) values ('Albert Einstein', '2018-06-22 19:10:25-07');

`

0 replies

grabdoc · 2024-05-07T02:27:43Z

grabdoc
May 7, 2024
Collaborator

`
http GET 'http://localhost:8080/tbl_student?filter=created_at>2016-09-30T15:30:00+01:00'
User-Agent:insomnia/8.6.1

`

0 replies

kdhrubo · 2024-05-13T00:25:53Z

kdhrubo
May 13, 2024
Maintainer

@khanhpd2002 can you please confirm if the issues have been resolved now?

3 replies

khanhpd2002 May 15, 2024
Author

I still have trouble with that

localhost:8080/tbl_my_table?filter=created_at>2024-05-01T00:00:00+07:00

And get error:

java.lang.IllegalStateException: Could not resolve parameter [0] in org.springframework.http.ProblemDetail com.homihq.db2rest.exception.GlobalExceptionHandler.handleGenericDataAccessException(com.homihq.db2rest.core.exception.GenericDataAccessException): No suitable resolver
	at org.springframework.web.method.support.InvocableHandlerMethod.getMethodArgumentValues(InvocableHandlerMethod.java:221) ~[spring-web-6.1.6.jar:6.1.6]
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:178) ~[spring-web-6.1.6.jar:6.1.6]
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:118) ~[spring-webmvc-6.1.6.jar:6.1.6]
	at org.springframework.web.servlet.mvc.method.annotation.ExceptionHandlerExceptionResolver.doResolveHandlerMethodException(ExceptionHandlerExceptionResolver.java:432) ~[spring-webmvc-6.1.6.jar:6.1.6]
	at org.springframework.web.servlet.handler.AbstractHandlerMethodExceptionResolver.doResolveException(AbstractHandlerMethodExceptionResolver.java:74) ~[spring-webmvc-6.1.6.jar:6.1.6]
	at org.springframework.web.servlet.handler.AbstractHandlerExceptionResolver.resolveException(AbstractHandlerExceptionResolver.java:175) ~[spring-webmvc-6.1.6.jar:6.1.6]
	at org.springframework.web.servlet.handler.HandlerExceptionResolverComposite.resolveException(HandlerExceptionResolverComposite.java:80) ~[spring-webmvc-6.1.6.jar:6.1.6]
	at org.springframework.web.servlet.DispatcherServlet.processHandlerException(DispatcherServlet.java:1357) ~[spring-webmvc-6.1.6.jar:6.1.6]
	at org.springframework.web.servlet.DispatcherServlet.processDispatchResult(DispatcherServlet.java:1160) ~[spring-webmvc-6.1.6.jar:6.1.6]
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1106) ~[spring-webmvc-6.1.6.jar:6.1.6]
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:979) ~[spring-webmvc-6.1.6.jar:6.1.6]
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1014) ~[spring-webmvc-6.1.6.jar:6.1.6]
	at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:903) ~[spring-webmvc-6.1.6.jar:6.1.6]
	at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:564) ~[tomcat-embed-core-10.1.20.jar:6.0]
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:885) ~[spring-webmvc-6.1.6.jar:6.1.6]
	at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:658) ~[tomcat-embed-core-10.1.20.jar:6.0]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:206) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:150) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51) ~[tomcat-embed-websocket-10.1.20.jar:10.1.20]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:175) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:150) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) ~[spring-web-6.1.6.jar:6.1.6]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.1.6.jar:6.1.6]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:175) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:150) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) ~[spring-web-6.1.6.jar:6.1.6]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.1.6.jar:6.1.6]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:175) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:150) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.springframework.web.filter.ServerHttpObservationFilter.doFilterInternal(ServerHttpObservationFilter.java:109) ~[spring-web-6.1.6.jar:6.1.6]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.1.6.jar:6.1.6]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:175) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:150) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) ~[spring-web-6.1.6.jar:6.1.6]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.1.6.jar:6.1.6]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:175) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:150) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:101) ~[spring-web-6.1.6.jar:6.1.6]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:175) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:150) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:167) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:482) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:115) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:344) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:391) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:896) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1736) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at java.base/java.lang.VirtualThread.run(VirtualThread.java:309) ~[na:na]

2024-05-15T13:45:59.764+07:00 ERROR 86510 --- [pg-ssl-extension] [mcat-handler-11] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: cz.jirutka.rsql.parser.RSQLParserException: cz.jirutka.rsql.parser.ParseException: Encountered " <UNRESERVED_STR> "07:00 "" at line 1, column 32.
Was expecting one of:
    <EOF> 
    <AND> ...
    <OR> ...
    ] with root cause

cz.jirutka.rsql.parser.ParseException: Encountered " <UNRESERVED_STR> "07:00 "" at line 1, column 32.
Was expecting one of:
    <EOF> 
    <AND> ...
    <OR> ...
    
	at cz.jirutka.rsql.parser.Parser.generateParseException(Parser.java:434) ~[rsql-parser-2.2.1.jar:na]
	at cz.jirutka.rsql.parser.Parser.jj_consume_token(Parser.java:372) ~[rsql-parser-2.2.1.jar:na]
	at cz.jirutka.rsql.parser.Parser.Input(Parser.java:45) ~[rsql-parser-2.2.1.jar:na]
	at cz.jirutka.rsql.parser.RSQLParser.parse(RSQLParser.java:126) ~[rsql-parser-2.2.1.jar:na]
	at com.homihq.db2rest.jdbc.processor.RootWhereProcessor.process(RootWhereProcessor.java:31) ~[classes/:na]
	at com.homihq.db2rest.jdbc.core.service.JdbcReadService.findAll(JdbcReadService.java:25) ~[classes/:na]
	at com.homihq.db2rest.jdbc.rest.read.ReadController.findAll(ReadController.java:39) ~[classes/:na]
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103) ~[na:na]
	at java.base/java.lang.reflect.Method.invoke(Method.java:580) ~[na:na]
	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:255) ~[spring-web-6.1.6.jar:6.1.6]
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:188) ~[spring-web-6.1.6.jar:6.1.6]
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:118) ~[spring-webmvc-6.1.6.jar:6.1.6]
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:926) ~[spring-webmvc-6.1.6.jar:6.1.6]
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:831) ~[spring-webmvc-6.1.6.jar:6.1.6]
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-6.1.6.jar:6.1.6]
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1089) ~[spring-webmvc-6.1.6.jar:6.1.6]
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:979) ~[spring-webmvc-6.1.6.jar:6.1.6]
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1014) ~[spring-webmvc-6.1.6.jar:6.1.6]
	at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:903) ~[spring-webmvc-6.1.6.jar:6.1.6]
	at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:564) ~[tomcat-embed-core-10.1.20.jar:6.0]
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:885) ~[spring-webmvc-6.1.6.jar:6.1.6]
	at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:658) ~[tomcat-embed-core-10.1.20.jar:6.0]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:206) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:150) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51) ~[tomcat-embed-websocket-10.1.20.jar:10.1.20]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:175) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:150) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) ~[spring-web-6.1.6.jar:6.1.6]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.1.6.jar:6.1.6]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:175) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:150) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) ~[spring-web-6.1.6.jar:6.1.6]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.1.6.jar:6.1.6]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:175) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:150) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.springframework.web.filter.ServerHttpObservationFilter.doFilterInternal(ServerHttpObservationFilter.java:109) ~[spring-web-6.1.6.jar:6.1.6]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.1.6.jar:6.1.6]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:175) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:150) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) ~[spring-web-6.1.6.jar:6.1.6]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.1.6.jar:6.1.6]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:175) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:150) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:101) ~[spring-web-6.1.6.jar:6.1.6]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:175) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:150) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:167) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:482) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:115) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:344) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:391) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:896) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1736) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52) ~[tomcat-embed-core-10.1.20.jar:10.1.20]
	at java.base/java.lang.VirtualThread.run(VirtualThread.java:309) ~[na:na]

thadguidry May 15, 2024
Collaborator

@khanhpd2002 The .ffffff part stands for "fractional", i.e. seconds with fractional digits specified in the seconds field.
(a microsecond is 1000 nanoseconds or one millionth (0.000001 or 10−6 or 1⁄1,000,000) of a second. Its symbol is μs)

Hmm, also good to check, just in case, in your PostgreSQL is your IntervalStyle set to sql_standard or set to postgres_verbose ?
https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES

local with default precision (unspecified)

SELECT LOCALTIMESTAMP;
Result: 2019-12-23 14:39:53.662522

with 3 digits of precision specified and +7 hours offset from UTC

SELECT CURRENT_TIMESTAMP(3);
Result: 2019-12-23 14:39:53.662+07

The main problem you have is that of an unrecognized INTERVAL parameter specified as +07:00.
https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

In these expressions, the desired time zone can be specified either as a text value (e.g., 'America/Los_Angeles') or as an interval (e.g., INTERVAL '-08:00'). In the text case, a time zone name can be specified in any of the ways described in Section 8.5.3. The interval case is only useful for zones that have fixed offsets from UTC, so it is not very common in practice.

But it seems your column was created with something like LOCALTIMESTAMP hence the .ffffff fractional seconds part being stored in the columns' data.

You can try specifying the fractional part fully and with/without a time zone offset:

localhost:8080/tbl_my_table?filter=created_at>2024-05-01T00:00:00.000000+07:00
localhost:8080/tbl_my_table?filter=created_at>2024-05-01T00:00:00.000000
or fully wrap with double quotes " " around the date part?
localhost:8080/tbl_my_table?filter=created_at>"2024-05-01T00:00:00.000000+07:00"

@kdhrubo But we should be able to support that I think. Or convert stuff when necessary using PostgreSQL DATETIME Functions? Depends on what they are really wanting to do actually. It's not 100% clear to me if he was actually trying to specify an offset but I think he was?

khanhpd2002 May 15, 2024
Author

@thadguidry tks for your reply, but when I use that filter format in endpoint "_expands" (for join table) then it works.
Beside that, I tried 3 way as you suggest, it still doesnt work.

grabdoc · 2024-05-15T17:12:31Z

grabdoc
May 15, 2024
Collaborator

I entered another row in the database like this

insert into tbl_student (full_name, created_at) values ('Albert Einstein', '2024-05-14 19:10:25-07');

Then ran a query
`
{{ _.SERVER }}/pgdb/tbl_student?filter=created_at>2024-05-01T00:00:00+07:00

[ { "id": 3, "full_name": "Albert Einstein", "created_at": "2024-05-15T02:10:25.000+00:00" } ]
`

2 replies

grabdoc May 15, 2024
Collaborator

Please check with 1.0.0-RC1 if possible.

khanhpd2002 May 16, 2024
Author

create table configs
(
    id           bigint    default nextval('configs_id_seq'::regclass) not null
        primary key,
    notification varchar(255),
    title        text,
    body         text,
    image        text,
    created_at   timestamp default now(),
    updated_at   timestamp default now()
);

INSERT INTO schema.configs (id, notification, title, body, image, created_at, updated_at) VALUES (12, 'check.test', 'test', 'test', 'image.jpeg', '2024-05-15 08:00:19.477006', null);

@grabdoc this is table script and 1 row data

grabdoc · 2024-05-16T13:40:46Z

grabdoc
May 16, 2024
Collaborator

Thanks @khanhpd2002
I ran the script and created data as follows

4 replies

grabdoc May 16, 2024
Collaborator

Here is how the data looks like in DB

grabdoc May 16, 2024
Collaborator

Now I query data using DB2Rest 1.0.0-RC1 and able to get the response

grabdoc May 16, 2024
Collaborator

Please do try with 1.0.0-RC1 and let us know.

khanhpd2002 May 17, 2024
Author

Tks for your reply,

I tried with 1.0.0-RC1 (code from master branch)

and still got error:

And when query for all records, without filter, it works:

kdhrubo · 2024-05-18T06:08:09Z

kdhrubo
May 18, 2024
Maintainer

Turns out to be a postman character encoding issue :D

1 reply

kdhrubo May 22, 2024
Maintainer

@khanhpd2002 - closing this issue. Please reopen an issue if you have any other issue.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Filter with timestamp and postgresql #541

{{title}}

Replies: 12 comments 15 replies

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

Select a reply

Filter with timestamp and postgresql #541

khanhpd2002 May 6, 2024

Replies: 12 comments · 15 replies

grabdoc May 6, 2024 Collaborator

khanhpd2002 May 7, 2024 Author

kdhrubo May 7, 2024 Maintainer

kdhrubo May 7, 2024 Maintainer

khanhpd2002 May 8, 2024 Author

kdhrubo May 8, 2024 Maintainer

grabdoc May 7, 2024 Collaborator

grabdoc May 7, 2024 Collaborator

grabdoc May 7, 2024 Collaborator

grabdoc May 7, 2024 Collaborator

grabdoc May 7, 2024 Collaborator

grabdoc May 7, 2024 Collaborator

grabdoc May 7, 2024 Collaborator

kdhrubo May 13, 2024 Maintainer

khanhpd2002 May 15, 2024 Author

thadguidry May 15, 2024 Collaborator

khanhpd2002 May 15, 2024 Author

grabdoc May 15, 2024 Collaborator

grabdoc May 15, 2024 Collaborator

khanhpd2002 May 16, 2024 Author

grabdoc May 16, 2024 Collaborator

grabdoc May 16, 2024 Collaborator

grabdoc May 16, 2024 Collaborator

grabdoc May 16, 2024 Collaborator

khanhpd2002 May 17, 2024 Author

kdhrubo May 18, 2024 Maintainer

kdhrubo May 22, 2024 Maintainer

khanhpd2002
May 6, 2024

Replies: 12 comments 15 replies

grabdoc
May 6, 2024
Collaborator

khanhpd2002 May 7, 2024
Author

kdhrubo May 7, 2024
Maintainer

kdhrubo May 7, 2024
Maintainer

khanhpd2002 May 8, 2024
Author

kdhrubo May 8, 2024
Maintainer

grabdoc
May 7, 2024
Collaborator

grabdoc
May 7, 2024
Collaborator

grabdoc
May 7, 2024
Collaborator

grabdoc
May 7, 2024
Collaborator

grabdoc
May 7, 2024
Collaborator

grabdoc
May 7, 2024
Collaborator

grabdoc
May 7, 2024
Collaborator

kdhrubo
May 13, 2024
Maintainer

khanhpd2002 May 15, 2024
Author

thadguidry May 15, 2024
Collaborator

khanhpd2002 May 15, 2024
Author

grabdoc
May 15, 2024
Collaborator

grabdoc May 15, 2024
Collaborator

khanhpd2002 May 16, 2024
Author

grabdoc
May 16, 2024
Collaborator

grabdoc May 16, 2024
Collaborator

grabdoc May 16, 2024
Collaborator

grabdoc May 16, 2024
Collaborator

khanhpd2002 May 17, 2024
Author

kdhrubo
May 18, 2024
Maintainer

kdhrubo May 22, 2024
Maintainer