Skip to main content
S
SA_101003587-Bedrock
7-Bedrock
May 19, 2023
Question

Postgres function script result and Thingworx service output result not matching

  • May 19, 2023
  • 1 reply
  • 2766 views

I have Mashup with date filters. calling thingworx service to pass datetime widget input From Date and To Date.
Thingworx service calls postgress function - getReportBydateFilter script to accept inputs sent by thingworx service and returns the output result as infotable. which is not matching output result (data values) returned by Postgres script function when executed in PgAdmin query editor. which shows the deviation in output values.
Can you please provide a solution on how to resolve this discrepancy of data not matching.?


Postgressql function - getReportBydateFilter(from_Date date, to_Date date)

Date input logs  from thingworx to call postgressql fucntion - 
From date - Mon May 01 2023 00:00:00 GMT(000) UTC,
To date - Fri May 19 2023 00:00:00 GMT(000) UTC,
Output - Infotable

 

Date input when postgressql function called in PgAdmin query editor -
From date - 2023-05-01
To date -  2023-05-19

Query editor - select * from getReportBydateFilter(from_Date date, to_Date date)
Output - Query Result
Expected Result - Infortable result and Query result (pgadmin query execution result) should match.

 

 

 

 

1 reply

J
jensc17-Peridot
17-Peridot
May 22, 2023

Hello,

 

Just one question regarding the dates form the PgAdmin query editor, does it perhaps put the time as 23:59:59 instead of 00:00:00 from Thingworx?

Also,

 

In what way is the data not matching?

If you could share some data (edited or redacted could work fine if there is some sensitive data) so that we can better understand the issue that'd be great!

 

Regards,

Jens 

S
SA_101003587-BedrockAuthor
7-Bedrock
May 23, 2023

Hi, I am accepting only dates for PostgreSQL without time HH:MM:SS in pgsql function script, We converted UTC time to IST by adding +5:30 offset. But when i look the report for today the resulting output is not matching as expected on the Mashup page to download the report.
I tried to send the inputs with time 23:59:59 but still its not working, the resulting output of PgADmin script execution on the database side and the resulting output on thingworx side not matching. I can see the deviations in values calculated in the report on Thingworx mashup.

J
jensc17-Peridot
17-Peridot
May 23, 2023

Hello,

 

Any datetime variable will be converted into the users local time in mashup widgets.

 

You can save your dates in IST in your database if you want, but when pulling that data to show to users it will get IST + IST (if the user is in India).

Otherwise you would have to convert it back into UTC before showing it to your users.

The "issue" you will face is that something that is saved as UTC 2023-05-22 22:30:00 is shown in the mashup for an IST user as 2023-05-23 04:00:00.

 

Without a look at the data, I'm not sure I can be of much more help.

If no one else chips in, I would suggest you open a case with PTC as they can better assist you.

 

Regards,

Jens

 

Terms & ConditionsCookie settingsAccessibility statement