REST / Charts API Timezone bug (may be JDK-specific?)

I’ve dug around in the forum for other mentions of this and while there are a few similar posts, none of the suggested fixes seem to work for me.

The problem: I’m in Ireland. During daylight savings time, the timezone gets rendered as IST (“Irish Summer Time”). Unfortunately, most date-parsing systems don’t recognise that as Irish Summer Time, they recognise it as Indian Summer Time, which is at a 5 1/2 hour offset from UTC, or a 4 1/2 hour offset from me. 4 1/2 hours neatly corresponds to the the problem I’m seeing.

  • Platform is MacOS High Sierra 10.13.6 (17G14019), Correto Java 1.8, OpenHAB 2.5.0 release build, persistence via MySQL 5.7.16
  • System time is Europe/Dublin, currently “Irish Summer Time”, UTC + 1
  • Database time is system time, so that’s also UTC + 1
  • mysql.cfg has “localtime=false”; localtime=true doesn’t fix the problem (and may have made it worse by persisting the Indian time instead of the Irish time)
  • OpenHAB persistence timestamps in log messages are correct
  • I’ve accidentally persisted my NTP item, so I can check that the values being stored in the database are correct (i.e. ItemNN.Time ~= ItemNN.Value)
  • when I ask mysql - via the command line - for the most recent update to an item, it gives me the correct time, e.g. 2020-08-08 09:50:46
  • when I ask the REST UI for the same information, it gives me 2020-08-08 05:20:46 2020 - 04:30 ahead of what it should be, i.e. it’s taking the database time as IST and re-rendering it to localtime.

This principally affects charting. Oddly, up until a couple of months ago I was running this stack on an older Mac (and an older MacOS) with Oracle’s Java instead of Corretto, and this problem didn’t show up. I’ve also tried using OpenJDK. So it’s entirely possible this is a combination of my specific timezone and my choice of JDK.

Any advice on fixing this welcome. It’s marginally annoying right now, because the only thing it really impacts are charts that I don’t particularly use, but the breakage on the REST API means I can’t really use this for anything without hacking on it.

Could you login to the karaf console and check if / what timezone is set there ?
Login to karaf console and execute the command:

env |grep user.timezone

It needs to show: Europe/Dublin
if that is not the case go to PaperUI => Configuration => System and check Regional Settings

Hmm. Is connecting using runtime/bin/client what you mean? I don’t have an “env” command there, but:

openhab> system:property | grep timezone
user. timezone =Europe/Dublin

Lot of moving parts here.
I think some users have been recommending to use openHAB JDBC persistence driver with MySQL. No idea if that might be better behaved in this case.

Okay, that’s the MacOS I guess.
Both Java and openHAB have timezone settings too, which may be defaulted but are obviously relevant.
Locale may be relevant too - a clever system could use locale to decide if IST meant Irish, Indian, or Israeli

MySQL has own timekeeping too, but you have already said that is defaulting (to host setting). Locale defaulting too?

How are you doing that exactly? Just to be sure it’s
GET …/rest/persistence/items/blah
That should provide epoch timestamps - which carry no information about timezone. Assumptions have to be made.
Beware anything converting that to readable format in case it is being “helpful” and applying corrections.

Aha, good point. It’s returned as an epoch time value in milliseconds which, well, I don’t speak epoch natively so I used perl to pretty-print it in something I could read. But that epoch value that’s out by 4.5 hours.

I’m using the openHAB “rest documentation” API to fetch the values. Trying with curl:

    $ curl -s -X GET --header "Accept: application/json" "http://flatbread.local:8080/rest/persistence/items/StudyTRV_SensorTemperature" | json_pp
    [... lots of output ...]
           "time" : 1596867045000,
           "state" : "24.42"

vs. the database:

mysql> select * from item185 order by item185.time desc limit 10;
| 2020-08-08 11:40:45 | 24.42 |

and for good measure, current epoch time:

$ date +%s

so 1596884000 is 1596884000000 ms which is ~4.5 hours from 1596867045000

(edited to fix formatting)

I find this epoch time converter useful for copy paste.
But yes, I concur, 1596867045000 represents 06:10 in some unspecified timezone. We can conclude it’s not the interpretation of epoch from REST that is the problem, which is all I was groping for.

Info only - REST Querying my own (rrd4j) persistence returns epochs in UTC (GMT), which is a pretty standard way to handle epochs (the consumer is expected to make their own locale correction)
I’m saying if everything worked properly I’d expect your REST timestamps to be an “hour out” while you’re on summertime. Clearly things aren’t working properly, still a lot of potential culprits!

The MySQL persistence configuration explains the definition for localtime in mysql.cfg:

use MySQL server time to store item values (if set to false ) or use openHAB server time (if set to true ). For new installations, setting this to true is recommended.

Do you see any difference in behavior of the values that are stored if you switch that value back to the default value false ? Can you provide example values for for this, too ?

I had initially thought this was the problem. When I set locatime=true the off-by-4.5-hours value is stored in the database. Obviously this has “corrupted” some of the data (again, thanks to my accidental persistence of NTP I have the means to fix it) so I’m reluctant to turn it on again to show an exact demonstration. Let me show you a version from when that setting was in effect:

mysql> select unix_timestamp(time) as actual, unix_timestamp(value) as saved from item1 where abs(unix_timestamp(time) - unix_timestamp(value)) > 7200 limit 10; 
| actual     | saved      |
| 1592078320 | 1592062119 |

$ perl -le 'print(scalar(gmtime(1592078320)))'
Sat Jun 13 19:58:40 2020
$ perl -le 'print(scalar(gmtime(1592062119)))'
Sat Jun 13 15:28:39 2020

(using 7200 to capture cases where there’s > 2 hours of difference to avoid accidentally picking up any daylight savings-related problems!)

By comparison, with localtime=false:

mysql> select unix_timestamp(time) as actual, unix_timestamp(value) as saved from item1 limit 10; 
| actual     | saved      |
| 1577836815 | 1577836815 |

rossko57 had mentioned locale:

$ env | grep LANG

no LC_ variables set, so this is the only locale control in the environment. has a couple of things set to en:

openhab> system:property |grep '=en'
openhab> system:property |grep 'IE'

This - not specifically the locale setting, but the parsing of IST as Indian rather than Irish time - sort of bothers me, since it implies that with all the information provided to the system about timezones, something is still trying to do a text parse on what should be a value that’s clearly defined, but is being expressed ambiguously instead.

I’ve not fully debugged this and frankly my Java’s a bit rusty but it looks like this is exactly the problem: the Mysql connector transfers everything as Strings and then converts to the appropriate type. For types that need a timezone, it will use java.util.TimeZone.getTimeZone() and, well:

$ cat
import java.util.TimeZone;

public class TimeZoneTest {
  public static void main(String[] args) {
    TimeZone tz = TimeZone.getTimeZone("IST");
    tz = TimeZone.getTimeZone("Europe/Dublin");
$ java TimeZoneTest

and aside from the difference between the two of these, 19800000 is an offset of 5.5 hours from GMT or 4.5 hours from my current timezone. So it looks like the fixes for this are:

  • convert my entire persistence system to UTC and never worry about timezones again
  • use a database driver that gets this right
  • figure out how to tell this database driver that the timezone is Europe/Dublin rather than IST.

Done. Adding ?serverTimezone=Europe/Dublin to the database URL in conf/services/mysql.cfg has fixed the problem. From the reading I’ve done around this it would appear that there’s still potential for things to break during daylight savings transitions, but that’s my own fault for not using UTC in the first place.