AP-82: Timezone support for service definitions start and end times

Software Bahmni
,
1

Looks like the platform is ready to use timezones, however at least in one place that feature is not working.

When we create a service, if we select the availability time of the service, image

Those times are always on the server timezone, because, at some point, we are formatting the start/end date and remove the timezone information that comes from the client.

I was able to track the issue to here

From my point of view, we have 2 problems here:

1º when saving date/time from client to server ( we should convert from client timezone to UTC/server) 2º When sending data from server to client, we should get UTC/server from DB and then convert on the client-side to their timezone.

Ref:

2

At the end of the day AppointmentServiceDefinition's startTime and endTime are stored as java.sql.Time as seen here, meaning that there is no timezone information attached.

Since there is no timezone information attached, I would suggest that we assume that what is stored in database are times in UTC.

Can we settle on that already @angshuonline @gsluthra? @angshuonline that is I believe one of your suggested options here anyway: Slack

If we can make this assumption, then we will have to:

  1. Find all the places in the UI (and frontend code) where the user submits those service definitions times, so that they are converted from client times to UTC.
  2. Find all the places in the UI (and frontend code) where those service definitions times are read from the server, so that they are converted from UTC to client times.
3

note - java.sql.Date does not retain timezone at all. So, to really solve the problem across a server timezone as well as the app being used from different timezones - we have to really use UTC all across. Meaning

  1. All APIs must support date only in UTC
  2. All UI elements must calculate and represent dates in local time
  3. Any backend code (app omod as well as reports) while querying must also take that in consideration.

note, some of the attributes like “date_created”, “date_modified” will be still in server time (automatically filled in the ORM or set as default in DB schema for the table)

so, it seems to me that it would require looking at all the code, not just frontend …

4

I will recap here all of our findings in making Ref App 2.x timezone resilient. (Btw while doing that we came up with those official OpenMRS conventions, see here: Time Zones Conventions - Documentation - OpenMRS Wiki)

There’s up to three timezones involved:

  1. MySQL timezone (‘database timezone’)
  2. Tomcat timezone (‘server timezone’)
  3. Browsers timezone (‘client timezone’)

With java.sql.Date the JDBC connector takes care of the conversions between database and server timezone since it knows the server and database timezones. Unfortunately java.sql.Time has proven to be unreliable in that regard, and this is why we either need to conventionally set such times stored in the database to be on an agreed timezone (eg. UTC) or we need to persist their timezone information as well to have it at hand.

IMO we can narrow down the issue to this specific case of startDate and endDate of AppointmentServiceDefinition, and we don’t need to worry about a wider issue that we believe doesn’t actually exist. As per our experience with Bahmni Appointments, everything seems to be fine timezone wise, except for this very part, and precisely because it involves java.sql.Time records.

1 Like
5

Hi @mksd @angshuonline I’ve created two pull requests to correctly handle timezone definitions on service definitions. I’ve added some comments inline.

@angshuonline if possible, have a look or assign someone to review so that we can push this forward. I’ll be waiting for feedback and available for any needed clarification.

Thanks!

Backend:

Frontend: