Time Zones in MariaDB (add on in container)

Configuration
1

I’m using MariaDB as my DB for HA and it works fine. However when I now try to use the data it appears all timestamps are stored in UTC. In other databases I have other data (hourly prices for example) that are in local time. In order to match (join) them I need to handle different time zones in MariaDB and in order to do that I’v found out I need to run a script that loads the time zone data from some sort of Linux files. https://mariadb.com/kb/en/mysql_tzinfo_to_sql/. So… Then my question is… how do I get into some shell for MariaDB in a container on HA?

Thanks for any pointers… :slight_smile:

2

OK, managed to figure this out. As I couldn’t find info on this I thought I’d just share the solution here :slight_smile:

  1. Start an SSH session to your HA. I use the SSH & Web Terminal Add-on from the add-on-store.
  2. Run:
    sudo docker ps --format "table {{.ID}}\t{{.Names}}"
    to get the container name. My guess is that the container is default addon_core_mariadb
  3. Start a bash session for the container
    docker exec -it addon_core_mariadb /bin/bash
  4. Run the MariaDB script to populate the time zone tables
    mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql
  5. Be patient, this took some time for me on my Pi4…
  6. Once you get the prompt back you should now see your time zone tables populated and you should be able to start converting between time zones.