0

Problem Summary

I would like to convert and validate the timestamp which is in YYYY-MM-DDTHH:mm:ss.SSSZ format (ex: 2022-06-15T08:27:00.599Z) in postgres.

I tried

select * from my_table WHERE my_date >= date_trunc('second', '2022-06-15T08:27:00.599Z'::timestamp)

select to_char(to_timestamp('2022-06-15T08:27:00.599Z','YYYY-MM-DD\"T\"HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')

These queries are executing fine in db_fiddle but giving an errors while running in python and postgres SQL.

Facing errors

RROR: 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

My need

Input timestamp : 2022-06-15T08:27:00.599Z
Expected timestamp : 2022-06-15 08:27:00

Appreciate your support.

Sekhar
  • 627
  • 4
  • 14
  • 34
  • Remove the `\ ` it has no meaning in SQL. `to_timestamp('2022-06-15T08:27:00.599Z','YYYY-MM-DD"T"HH24:MI:SS')` works just fine. –  Jun 15 '22 at 12:21
  • Here is the issue, I just ran my query. ERROR: TEIID30068 The function 'to_timestamp('2022-06-15T13:03:00.003Z', 'YYYY-MM-DD"T"HH24:MI:SS')' is an unknown form. Check that the function name and number of arguments is correct.org.teiid.jdbc.TeiidSQLException: TEIID30068 The function 'to_timestamp('2022-06-15T13:03:00.003Z', 'YYYY-MM-DD"T"HH24:MI:SS')' is an unknown form. Check that the function name and number of arguments is correct.;Error while executing the query nil – Sekhar Jun 15 '22 at 13:06
  • That's not a Postgres error message. My syntax [works just fine](https://dbfiddle.uk/?rdbms=postgres_14&fiddle=f54a55d3ec3f8657f06cb2ec3aee53bc) with Postgres. Which DBMS are you really usign? `select version();` should tell you. –  Jun 15 '22 at 13:16
  • version : `Teiid 8.7.13.6_2-redhat-1`. pg server info: `options': '', 'sslmode': 'prefer', 'sslcompression': '0', 'sslsni': '1', 'ssl_min_protocol_version': 'TLSv1.2', 'gssencmode': 'disable', 'krbsrvname': 'postgres', 'target_session_attrs': 'any'}` – Sekhar Jun 15 '22 at 13:27
  • That's definitely not the output of Postgres' `version();` function. –  Jun 15 '22 at 13:32
  • @rajS this is not the postgres version. Perhaps from the client? – Jim Jones Jun 15 '22 at 13:37
  • got it. Let me redirect it to TEIID group. – Sekhar Jun 15 '22 at 13:55
  • Hi a_horse_with_no_name ,Jim Jones, Thanks a lot for your finding. It is issue with SQL supported functions of TEIID. Not postgres. I found the wat to fix the issue. – Sekhar Jun 16 '22 at 06:24

1 Answers1

2

Forgive me if I am oversimplifying your question, but wouldn't a simple cast and date_trunc do the trick?

SELECT date_trunc('second','2022-06-15T08:27:00.599Z'::timestamp);

     date_trunc      
---------------------
 2022-06-15 08:27:00

An alternative is to round the seconds with ::timestamp(0) - see this other answer. But it would return a different result:

SELECT '2022-06-15T08:27:00.599Z'::timestamp(0);

      timestamp      
---------------------
 2022-06-15 08:27:01
Jim Jones
  • 18,404
  • 3
  • 35
  • 44
  • Any other tips ? – Sekhar Jun 15 '22 at 13:21
  • @rajS perhaps after you tell us what is wrong with those posted here :-D keep in mind that `TEIID30068` isn't a postgres error code. The problem seems to be in the client itself – Jim Jones Jun 15 '22 at 13:24
  • it is actually VDB. I was successfully accessed the vdb using sql queries initially. when I am converting and validating now I am facing the issues like this. – Sekhar Jun 15 '22 at 13:33
  • @rajS I honestly never even heard of VDB. These queries posted here work just fine on postgres, so your problem seems to be somewhere else. – Jim Jones Jun 15 '22 at 13:37