[ovirt-devel] DWH cannot gain hourly history data from db because of the bloody time problem.

[张亚琪]

hi everybody,

        Recently, I have tested oVirt Reports. And I encountered a problem
about some reports that cannot show data. And then I setup the DWH
development environment. I found maybe this reason for missing data is the
AggregationToHourly3.5. When data from datacenter_samples_history were
inserted to datacenter_hourly_history, u will find nothing happened.
Because the select sql before inserting data reads：

"SELECT history_id,
>
> history_datetime,
>
>       datacenter_id,
>
>       datacenter_status,
>
>       minutes_in_status,
>
> datacenter_configuration_version
>
> FROM datacenter_samples_history
>
> WHERE *history_datetime >= '"+context.lastHourAggr+*"'
>
> AND history_datetime < '"+TalendDate.addDate(context.lastHourAggr,
>> 1,"HH")+"'
>
> ORDER BY history_datetime,
>
>                    datacenter_id,
>
>                    datacenter_status"
>
>

And then I queried the table of datacenter_samples_history &
history_configuration (has the field of lastHourAggr) in the db
of ovirt_engine_history. The results are as follows:

ovirt_engine_history=# select * from history_configuration;
>
>      var_name      | var_value |      var_datetime
>
> -------------------+-----------+------------------------
>
>  MinimalETLVersion | 3.5.0     |
>
>  default_language  | en_US     |
>
>  firstSync         | false     | 2014-10-13 19:42:00+08
>
>  lastDayAggr       |           | 2014-11-14 00:00:00+08
>
> * lastHourAggr      |           | *2014-*11-15* 06:00:00+08
>
>  HourlyAggFailed   | false     |
>
> (6 rows)
>
>
>


ovirt_engine_history=# select *history_datetime* from
> datacenter_samples_history;

      history_datetime

----------------------------

 2014-*11-13* 03:07:00.23+08

 2014-11-13 03:08:00.238+08

 2014-11-13 03:09:00.229+08

 2014-11-13 03:10:00.221+08

 2014-11-13 03:11:00.229+08

 2014-11-13 03:12:00.221+08

 2014-11-13 03:13:00.237+08

 2014-11-13 03:14:00.22+08

 2014-11-13 03:15:00.221+08

 2014-11-13 03:16:00.238+08

 2014-11-13 03:17:00.238+08


>


Obviously， history_datetime < lastHourAggr , the data will never be
inserted to the datacenter_hourly_history. And the place where I bold is
the root cause of the error. Then , I try to update the *lastHourAggr *in
the table of history_configuration. Reports works successfully. However,
the lastHourAggr will change to 2014-11-15 afterwards. But u know Today is
2014-11-14 ! I have no idea about why the value of lastHourAggr is
2014-11-15. Would u help me solve this problem. Thanks a lot !
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.ovirt.org/pipermail/devel/attachments/20141114/95c2ad42/attachment-0001.html>