Hi,
When I chart my data, it does not show the most recent data, this happened …in OpenHab 2.5.x, I recently upgraded to 3.3.0, and it's still happening. Specifically, I live in New York, my timezone is America/New_York (GMT-05:00). I have this correctly set in the region settings.
## Expected Behavior
Charts should show up to the second data
## Current Behavior
Charts omit the last 5 hours of data.
## Possible Solution
Queries should be written in UTC, I do not beleive the fix in https://github.com/openhab/openhab-addons/pull/9445 was ever actually correct
## Steps to Reproduce (for Bugs)
1. Set timezone to something other than UTC (I think it needs to be a GMT-XX:XX. America/New_York is a good example
2. Persist some data
3. Chart the data
## Context
This is related to the bugs referenced in https://github.com/openhab/openhab-addons/pull/9445
I can see that the queries insert with NOW() for time, and that's stored in a native database format. That means dates/times need to be queried and displayed in the connection local. This typically should be UTC for a variety of reasons, regardless of the users local.
However, that's not what's happening, the insert of data looks like this (and it's correct)
`
11:26:29.069 [DEBUG] [g.openhab.persistence.jdbc.dto.ItemVO] - JDBC:ItemVO tableName=solar_system_power_0103; newTableName=null;
11:26:29.070 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: Try to use ItemType NUMBERITEM for Item solar_system_power
11:26:29.070 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: item 'solar_system_power' as Type 'NUMBERITEM' in 'solar_system_power_0103' with state '3.926'
11:26:29.070 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: itemState: '3.926'
11:26:29.071 [DEBUG] [g.openhab.persistence.jdbc.dto.ItemVO] - JDBC:ItemVO setValueTypes dbType=DOUBLE; javaType=class java.lang.Double;
11:26:29.071 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: newVal.doubleValue: '3.926'
11:26:29.071 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doStoreItemValue sql=INSERT INTO solar_system_power_0103 (TIME, VALUE) VALUES( NOW(3), ? ) ON DUPLICATE KEY UPDATE VALUE= ? value='3.926'
11:26:29.073 [DEBUG] [.jdbc.internal.JdbcPersistenceService] - JDBC: Stored item 'solar_system_power' as '3.926' in SQL database at Thu Nov 24 11:26:29 EST 2022 in 4 ms.
`
However, the querying of that same data looks like this
`
5001-2022-11-24 11:33:37.025 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::query: item is solar_system_power
5002-2022-11-24 11:33:37.026 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getHistItemFilterQuery filter='true' numberDecimalcount='3' table='solar_system_power_0103' item='solar_system_power (Type=NumberItem, State=4.2055, Label=Current Solar Power [%.3f kW], Category=energy, Groups=[SolarSystem])' itemName='solar_system_power'
5003-2022-11-24 11:33:37.026 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getHistItemFilterQueryProvider filter = FilterCriteria [itemName=solar_system_power, beginDate=2022-11-23T16:33:37.014Z, endDate=2022-11-24T16:33:37.013Z, pageNumber=0, pageSize=2147483647, operator=EQ, ordering=ASCENDING, state=null], numberDecimalcount = 3, table = solar_system_power_0103, simpleName = solar_system_power
5004:2022-11-24 11:33:37.027 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::query queryString = SELECT time, value FROM solar_system_power_0103 WHERE TIME>'2022-11-23 11:33:37' AND TIME<'2022-11-24 11:33:37' ORDER BY time ASC
5005:2022-11-24 11:33:37.028 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doGetHistItemFilterQuery sql=SELECT time, value FROM solar_system_power_0103 WHERE TIME>'2022-11-23 11:33:37' AND TIME<'2022-11-24 11:33:37' ORDER BY time ASC
5006-2022-11-24 11:33:37.029 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '0.0084', unit = 'null', getClass = 'class java.lang.Double', clazz = 'Double'
5007-2022-11-24 11:33:37.030 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '0.008', unit = 'null', getClass = 'class java.lang.Double', clazz = 'Double'
5008-2022-11-24 11:33:37.030 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '0.0081', unit = 'null', getClass = 'class java.lang.Double', clazz = 'Double'
<snip>
5097-2022-11-24 11:33:37.074 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '0.0', unit = 'null', getClass = 'class java.lang.Double', clazz = 'Double'
5098-2022-11-24 11:33:37.074 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '0.0069', unit = 'null', getClass = 'class java.lang.Double', clazz = 'Double'
5099-2022-11-24 11:33:37.074 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '0.0', unit = 'null', getClass = 'class java.lang.Double', clazz = 'Double'
5100-2022-11-24 11:33:37.075 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC: Query for item 'solar_system_power' returned 94 rows in 49 ms
`
The previous pull request seems to have changed it to query in localtime which isn't correct, it should query in UTC and then convert the results to localtime
Querying in localtime?
`
//JdbcBaseDAO.java
filterString += " TIME>='" + JDBC_DATE_FORMAT.format(filter.getBeginDate().withZoneSameInstant(timeZone))
`
## Your Environment
debian bullseye
openhab 3.3.0
