I am new to TEIID and VDB. As per my need, I need to extract the VDB data using Postgres SQL by validating the converted timestamp.
Here is the sample query that I am trying to execute.
select col1, col2, col3 from (EXEC my_Views.get_mytable('2022-01-01 00:00:00.000', now())) as TAB where my_date >= to_char(to_timestamp('2022-06-15T08:27:00.599Z','YYYY-MM-DD\"T\"HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')
The problem is, the conversion is not happening at WHERE clause.
I am getting different issues when I try with different conversion tips each time in postgres.
(1)
ERROR: TEIID31100 Parsing error: Encountered "'2022-06-15T08:27:00.599Z','YYYY-MM-DD"T"HH24:MI:SS), '[]YYYY[]-MM" at line 1, column 672.Was expecting: "and" | "between" | "in" | "is" | "like" | "like_regex" | "not" | "or" | "order" | "similar" ...org.teiid.jdbc.TeiidSQLException: TEIID31100 Parsing error: Encountered "'2022-06-15T08:27:00.599Z','YYYY-MM-DD"T"HH24:MI:SS), '[]YYYY[]-MM" at line 1, column 672.Was expecting: "and" | "between" | "in" | "is" | "like" | "like_regex" | "not" | "or" | "order" | "similar" ...;Error while executing the query nil
(2)
ERROR: TEIID30068 The function 'date_trunc('second', cast('2022-06-15T08:31:00.731Z' AS timestamp))' is an unknown form. Check that the function name and number of arguments is correct.org.teiid.jdbc.TeiidSQLException: TEIID30068 The function 'date_trunc('second', cast('2022-06-15T08:31:00.731Z' AS timestamp))' is an unknown form. Check that the function name and number of arguments is correct.;Error while executing the query nil
select col1,col2,col3 from (EXEC my_Views.get_mytable('2022-01-01 00:00:00.000', now())) as TAB where my_date >= to_char(to_timestamp('2022-06-15T08:27:00.599Z','YYYY-MM-DD\"T\"HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')
select col1, col2, col3 from (EXEC my_Views.get_mytable('2022-01-01 00:00:00.000', now())) as TAB where my_date >= date_trunc('second', '2022-06-15T08:27:00.599Z'::timestamp)
I feel the conversion issue will resolve the problem.
Input TS: '2022-06-15T08:27:00.599Z' (YYYY-MM-DDTHH:mm:ss.SSSZ)
Expected TS: '2022-06-15 08:27:00' (YYYY-MM-DD HH:mm:ss)
I initially thought it is postgres issue, later I realize that it is not an issue with postgres. I have applied some conversion techniques in postgres but no luck. Those conversion queries is working in DB fiddle but not in python and postgres.
Hope my stuff will help you understand the issue. Please help me to resolve this.
Thanks.
There is no specific function available in teiid functions to convert my desired TS format. I used substr to capture the required TS in my case. It worked.