0

I have an instance of Oracle DB running in 2 locations - US and Europe. The java app servers connected to these are also in different locations. Eg, DB in US is connected to java server in India and DB in Europe is connected to java server in Japan. My task is, to pick a date from database from US and send it via Kafka to Europe and vice versa.

The problem is, the time I send from first region will not match with the time in other region. I want that if I send "22-Aug-2022 12:00:00" from US, it should get converted into equivalent time in Europe that is "22-Aug-2022 06:00:00" (depends on DB time only, not app server time).

What I thought of this problem was that I would take the UTC time from one region, send it over and in the destination region, insert the equivalent time. But whatever I have tried is not working due to lack of experience. I am not able to get the correct UTC timestamp in java.

Can someone suggest the best approach for this problem which takes care of converting and fetching the timestamp at DB level.

Edit:-

PS: Please ignore the time conversion logic in the given example. It's just an example and may be it is not factually correct. Please provide suggestions on time conversion in a generic way

Mukul Bindal
  • 351
  • 1
  • 14
  • as you know the timezomes, so change the timezone – nbk Aug 12 '22 at 14:17
  • The best approach would be to work only with UTC on the back-end and display it however needed on the front-end – David דודו Markovitz Aug 12 '22 at 14:24
  • Related: [Java Best Practice for Date Manipulation/Storage for Geographically Diverse Users](https://stackoverflow.com/questions/40075780/java-best-practice-for-date-manipulation-storage-for-geographically-diverse-user) – Ole V.V. Aug 12 '22 at 16:49
  • US has a lot of time zones. Europe too. Do you know which US time zone and which European time zone your databases are in? BTW US time is *behind* time in Europe, so your example conversion is probably reversed. – Ole V.V. Aug 12 '22 at 18:05
  • If 22-Aug-2022 12:00:00 is in US Eastern time, it corresponds to 17:00 in Portugal, Europe. If 12:00 were in Pacific time, they correspond to 22:00 in Ukraine, Europe. There’s no way the conversion should result in 22-Aug-2022 06:00:00. – Ole V.V. Aug 13 '22 at 05:19

1 Answers1

0

The most accepted solution is to:

  1. Store all dates in UTC in database and backend so it is consistent.
  2. Then convert the dates to local date and time zone on frontend to display to user.

as how to convert to UTC maybe this answer can help you: Java: How do you convert a UTC timestamp to local time?

xMilos
  • 1,519
  • 4
  • 21
  • 36