Dealing with salesforce data type date with postgres date and timezone

(SF means salesforce)

Right now, our app(db is PostgreSQL) is connected to SF through heroku-connect.

Say, there are two columns. One is created_at with Timestamp data type. This column is connected to createdate on SF (data type is datetime on SF).

Another one is called startdate with date data type. This column is connected to startdate__c on SF with date type. We set this data with date type because we do not need time for this field.

The issue is timezone. Right now, data is saved in postgres as UTC date/time without timezone. SF assumes that the data is UTC and it converts to JST so SF operator sees JST datetime on SF.

createdate and startdate should be the same value, but we found that sometimes they not the same. For example, when createdate is Dec 12 08:00, startdate should be Dec 12 but for some cases, startdate was Dec 11. This is because startdate is date type so it does not have time in it. SF assumes that the time is 00:00 and converts it to JST.

We are thinking to change the data type for startdate to datetime, but then, we need to change the data type for startdate__c on SF and operator will see the time for that fields (which is not relevant for this field) and we also need to change backends to make the converted time has correct date and time.

Is there any better way to solve this problem? Any help would be appreciated!

https://stackoverflow.com/questions/65402333/dealing-with-salesforce-data-type-date-with-postgres-date-and-timezone December 22, 2020 at 09:56AM