Postgres function script result and Thingworx service output result not matching

[SA_10100358]

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.

[jensc]

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 

[SA_10100358]

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.

[jensc]

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

[nmutter]

Similiar topic regarding timestamps: https://community.ptc.com/t5/ThingWorx-Developers/Database-and-mashup-UI-data-not-matching/m-p/874553

I'm not sure what the exact issue is you describe. In your example you say you e.g. input "From date - Mon May 01 2023 00:00:00 GMT(000) UTC," and when fetching the data via pgadmin you get "2023-05-01" in return? What is the exact issue here?

What date do you get when reading the data not via pgadmin but via TWX (which only has DateTime-Datatype) when logging it (to ScriptLog?) the same way like when you write it?

[SA_10100358]

Hi, We are not returning dates to display or to show in the report on the mashup, we are passing dates as input to PostgreSQL function to get the report - The report contains values calculated between dates filtered means selected by the user from mashup DateTime widgets. The report gives the data of calculated values example - To calculate the Count no entries were created between from and to dates.
When thingworx service calls the postgres sql function its sends date correctly but adding timestamp. -
From date - Mon May 01 2023 00:00:00 GMT(000) UTC,To date - Fri May 19 2023 00:00:00 GMT(000) UTC,
But on posrgres database side if we execute same postgresql script by passing Date input to get the count for no of entries created in between dates  we passed input in YYYY-MM-DD format - From date - 2023-05-01,To date -  2023-05-19
So the output (No of entries created count) value  which is calculated between From and to dates is not matching as expected.
eg. I have select query in Postgres script which returns the Count of entries created between from date and to date in the database - 10 records.
But when I call the posgres function from thingworx database thing service its shows fewer entries - 8 records for the same input dates.

The data type used for postgres function to accept user input is- date and from thingworx its sends with timestamp.
In database table sotred the table entries with timestamp and we are taking count of entries data based user input dates (from/to dates)
1. When i choose the date behind the current date it gives the correct output for no entries created for date inputs.
2. But when i choose till current date its show count fewer entries.

I described here what exactly facing the issue let me know if require more details.

[nmutter]

Can you maybe just adapt the TWX service which calls the postgres script to convert the TWX-date to your requested YYYY-MM-DD format?

let date = new Date();
result = date.toISOString().substring(0, 10);

I assume the 'root' issue is in the database: which converts the timestamp to a different timezone resulting in different results. I assume the entries in your database are in some timezone A and when querying via pgadmin the date's used will be in the same timezone A. But when TWX queries the DB, which passes also timezone B (GMT(000) UTC) information it will convert these into the same timezone A like the database.

If you compare the difference between the entries you get via pgadmin and via twx query I assume you see that there will be entries missing at the start/end of the range.

It would be interesting if you could somehow log which date is used in the database query in the end (after postgres converted it). Not sure how. But maybe just passing YYYY-MM-DD will already resolve it for you.

[SA_10100358]

Hi, I will try the solution that you shared and let you know the results, 
Yes, the database stored the entries in IST time by adding a +5:30 offset,
Before calling thingworx service call to database, we added offset +5:30 by using setHours and setMinutes and then called the postgressql script in thingworx DB service call. So the dates were sent correctly but with timestamps and timezone which is causing result output mismatching for TWX service result (info table) and PgAdmin result (db query edittor output).

[VVM_4]

Hi @SA_10100358 ,

Curious to know if @nmutter's solution worked. I tried to replicate your issue albeit with a lot of assumptions and I think his solution works.

But I'm thinking if this might just be a band aid on a much deeper issue, with the way data (datetime in particular) is being written and read from the DB.

Insights and discussions here will definitely help others in our Thingworx community avoid the same mistake and make better solutions. So any updates on the status, actual root cause discussions or hopefully a solution that works is definitely greatly appreciated by me and I'm sure a lot of the Thingworx community members.