How to Date/time calculations?

Macamba

The database I query contains two fields containing (PostgreSQL) timestamps, for example '9 aug 2009 17:37' and '9 aug 2009 17:54'. In my Data Set I added a computed field with the result of a subtraction of these two fields.<br />
<br />
Looking at the PostgreSQL documentation, the following calculation should be feasible: <br />
<pre class='_prettyXprint _lang-auto _linenums:0'>
timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00' = interval '1 day 15:00:00'
</pre>
<br />
However, when I try this in BIRT I get 1020000.0 with the result formatted as a Float. When I format the computation as a Time, Date or Date Time, I get the error message:<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>Fail to compute column "duration"
Cannot convert the value of 1020000.0 to Date type.

Reason:
A BIRT exception occurred</pre>
<br />
Anyone know how to proceed?

mwilliams

Hi Macamba,

If you're doing the computed column within BIRT and not in your query, I think you'll have to use the BirtDateTime.diff functions to compute the difference and then possibly figure your own "interval" in the format you want it in a string. For example, you could do:

interval = "";
seconds = BirtDateTime.diffSecond(date1, date2); //returns and integer value of seconds between the two dates
days = floor(seconds / 86400);
seconds = seconds - (86400 * days);
interval = interval + days.toString() + " days";
etc.....

Then output the final "interval" string as the column output.