Problem with the diffmin function when I have different time zone

Hi, 
 
I'm tring to understand something with the datetime. 
 
I'm using a date in the database, i received the good date in the data binding. But I need to have the difference between now and the date in my record. 
 
My data is on one french server (for example) and I'm at Hawaii or Alaska [I changed the time zone]. I didn't receive the same minutes. [hawaii : 30 min more and Alaska : 59 min more]. 
 
To understand what I did : 
I create one date : 
<pre class="_prettyXprint">
var dateOne = new Date();</pre>
and the second with my data : 
<pre class="_prettyXprint">
var dateTwo = new Date(dataSetRow["column"]);</pre>
and I try to take the difference minutes : 
<pre class="_prettyXprint">
var mins = BirtDateTime.diffMinute(dateOne, dateTwo);</pre>
 I tried to change the web.xml : 
<pre class="_prettyXprint _lang-">

<context-param>
<param-name>BIRT_VIEWER_TIMEZONE</param-name>
<param-value></param-value>
</context-param>
</pre>
with the <param-value></param-value> GTM+1 but it didn't work. 
 
Could you help me to "force" the new date to take the french time zone (or another one)? 
 
Thanks,

Find more posts tagged with

Comments

fannyV

any idea?

shamo

Just use Birtdatetime.diffMinute(datefield,Birtdatetime.now())

fannyV

Hi Shamo,
Thanks for your help.
 
I've the same problem with BirtDateTime.now().
I took a look on Internet and found : <a data-ipb='nomediaparse' href='https://www.eclipse.org/forums/index.php/t/1065349/'>https://www.eclipse.org/forums/index.php/t/1065349/</a>
 
I don't know if I can find a workaround. Is it a known issue? Did Birt fix it in a earlier version?

Clement Wong

To better assist you, I would like to understand your environment...
 
1. Are you using Open Source BIRT, or commercial BIRT? What version?
2. What timezone is set your database machine ?
3. Can you provide one sample value it is returning?
4. What timezone is set on your Application Server (if running OS BIRT), or iHub (if running commercial BIRT) machine?
5. Can you provide sample values of what you are expecting?
 
BIRT uses the timezone as defined in the JRE.

fannyV

Hi Clement, 
<blockquote class="ipsBlockquote">1. Are you using Open Source BIRT, or commercial BIRT? What version?
 
</blockquote>
 
I'm using an open source BIRT. The version is 3.7.1. 
 
 
<blockquote class="ipsBlockquote">2. What timezone is set your database machine ?
 
</blockquote>
 
The timezone is (UTC +01:00) Bruxelles, Copenhague, Madrid, Paris.
 
 
<blockquote class="ipsBlockquote">3. Can you provide one sample value it is returning? 5. Can you provide sample values of what you are expecting?</blockquote>
 
 if I should have a result 03:00, 
<ul><li>UTC - 10:00 Hawaii, I received 03:30, so 30 min diff</li>
<li>UTC - 09:00 Alaska,I received 03:59, so 59 min diff</li>
<li>UTC - 08:00 Baja California,I received 02:48, so -12 min diff</li>
<li>UTC - 09:00 Alaska,I received 03:59, so 59 min diff</li>
<li>...</li>
</ul><blockquote class="ipsBlockquote">4. What timezone is set on your Application Server (if running OS BIRT), or iHub (if running commercial BIRT) machine?
 
</blockquote>
 
The timezone on my application server is (UTC +01:00) Bruxelles, Copenhague, Madrid, Paris.
 
 
However, If I change the timezone on my database machine, the diff still existe and if I change the timezone on my application server, it's working (for some timezone)! Like : 
<ol><li>(UTC+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna, is working well.</li>
<li>(UTC +01:00) Bruxelles, Copenhague, Madrid, Paris. is not working</li>
</ol> 
Thanks a lot for your help! 
 

For (UTC+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna, it's working well.

Clement Wong

Still trying to understand your setup to figure where the discrepancy is....
 
1. What database server and version are you running?
2. What database client and version with BIRT are you using? Is there a specific JDBC URL used?
3. So let's say the time is stored in your database as 03:00 UTC +01:00, or you can use one real value returned:
 
 A. Outside of BIRT, if you use a database client, what does it show?
 B. In the BIRT Designer, if you just Preview Data in the Edit Data Set, what does it show?
 
4. I'm confused about your last observation where if you have a Berlin timezone which is UTC+01:00 and Paris timezone which is also UTC+01:00, there is different result?
 
 
5. Now that we have retrieved the time from the database, you want to calculate the current date/time on the application server, and subtract it from the date/time from the database to get the minutes?
 
6. Where does the other time zones come into play? Is that something that you'll have the user select before running a report?
 
 
Have you thought of using retrieving the Unix / Epoch / POSIX time from the database? It might be easier to calculate since you'll be within a fixed reference of seconds that have elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970.
 
For example, MySQL:
<pre>
SELECT UNIX_TIMESTAMP(thedate) FROM thetable</pre>
Then, in BIRT, you can calculate:
 
// GMT: Tue, 23 Aug 2016 22:28:12 GMT 
// Your time zone: Tuesday, August 23, 2016 3:28:12 PM GMT-7:00 DST 
var dbTime = 1471991292; 
var epochTime = BirtDateTime.now().getTime()/1000; 
(epochTime - dbTime) / 60;
 
 
Attached is a .rptdesign that I would like you to run in your Designer and Application Server and note what time zones you set for Java before running the report. Include the output if you would like to troubleshoot further.