CODEWITHSUNDEEP
javadatabasetimezone
Ananth
Ananth

Reputation: 206

Reports for multiple timezones

In our application, we have a reporting module in the UI from which an admin can view reports. The admin will be given an option of selecting from_date and to_date. During transactions we store the live data in few tables which we can all as core tables. The data is stored in these tables in UTC timezone. We run a scheduler say every 30 minutes and convert the data from the core tables into what is needed for reports like number of transactions, response time etc. for a day. The requirement is that, for the admin, the data shown should be based on the timezone of his browser. The admins are spread across multiple timezones. How do we store the data and how do we retrieve it? Each time a request is sent, we cannot compute the values.

Upvotes: 1

Views: 716

Answers (2)

Matt Johnson-Pint
Matt Johnson-Pint

Reputation: 241475

Storing transactions as UTC is ok, and often a recommended practice.

Storing using TIMESTAMP WITH TIMEZONE as some suggested in comments would have the added benefit of knowing what local time and timezone applied to the transaction data. That is also ok, but it's not what you asked.

You said:

The requirement is that, for the admin, the data shown should be based on the timezone of his browser.

I interpret this to mean that regardless of whether the time in the database is set in UTC or in local time with a time zone, that you want the data converted during output to the timezone of the person who is viewing the report.

But you also said:

Each time a request is sent, we cannot compute the values.

Well, since every viewer might have potentially different data, I'm sorry to tell you but that's exactly how you'll have to do it. Whatever application logic is generating the report will have to convert the data's timestamps to the local time of the viewer. This is not usually done in the database, but in application logic on a middle tier. If you are generating reports directly against a database, some reporting engines have functions for manipulating the timestamps in code or script associated with the report itself.

Whatever your result, you should probably make sure that somewhere on these reports (perhaps in the footer) that the time zone is output. Humans will often do things like email output of reports to their supervisors, customers, etc., who obviously could be in a different time zone.

Upvotes: 1

gartcimore
gartcimore

Reputation: 43

In oracle store a Date 

create table myTable  (
   Id NUMBER(19) not null,
   MyTime DATE not null,
);

In Java, handle a java.util.Date but before storing it, convert it to a java.util.Calendar

Calendar gmtCal = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
gmtCal.setTime(date);

Now you can store it in base

Upvotes: 0

Related Questions