Use CURRENT_TIMESTAMP instead of CURRENT_DATE, timestamp is number of seconds since 1.1.1970, it takes less space in the DB instead of a string and you can easier calculate with it.
Im using mariadb addon and its still upgrading and it has grown 30gb too a total of 99gb and still running. My database is always +/- 50gb because i store 356 days of data but i dont get why its grown 30gb since the update and still growing. The update is running for 13 hours now.
OK, thanks, now i get a number, but i think get min or max from all history. Now iād need to add something to select only today, from midnihgt 'till nowā¦
(sorry, i only know basics of SQL)ā¦
What about this query, what is wrong with it?
SELECT * FROM states WHERE entity_id = 'sensor.ore_functionare_cazan' AND DATE(last_updated) = DATE('now', '-1 day') ORDER BY last_updated DESC LIMIT 1;
1 Like
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to
your MariaDB server version for the right syntax to use near ā ā-1 dayā) ORDER BY last_updated
DESC LIMIT 1ā at line 1
This one should work:
SELECT * FROM states WHERE entity_id = 'sensor.ore_functionare_cazan' AND DATE(last_updated) = NOW() - INTERVAL 1 DAY ORDER BY last_updated DESC LIMIT 1;
AFAIK is a SQLite database and the query was perfectly ok until last night. Thank you for your input, but your query obviously returns an error on my system, just as mine returns an error on yours:
near "1": syntax error
The Battery for Oral B isnāt coming up for me, is any extra setup needed?
Maybe this will help you to figure out, I donāt know. Below by old and new queries, SQLite database:
Old (stopped working after the update):
SELECT * FROM states WHERE entity_id = 'sensor.ore_functionare_cazan' AND DATE(last_updated) = DATE('now', '-1 day') ORDER BY last_updated DESC LIMIT 1;
New (seems to work now):
SELECT * FROM states WHERE entity_id = 'sensor.ore_functionare_cazan' AND DATE(DATETIME(last_updated_ts, 'unixepoch')) = DATE('now', '-1 day') ORDER BY last_updated_ts DESC LIMIT 1;
Very interested in this! If Iām understanding this correctly does this mean I could buy the HLK-LD2410B version and wire it up to a usb-c breakout board for power without needing to use something like a D1 mini at all? Literally only need to power it and then use the integration to access it over bluetooth? 
Would make for a very small and very cheap mmwave sensor!
1 Like
I also use temperature min/max sensors for the current day (from midnight to now). This is how I changed my sensorsā SQL queries to make them work with the ālast_updated_tsā field:
SELECT MIN(CAST(state AS DECIMAL(3, 1))) AS min_temp
FROM states
WHERE entity_id = 'sensor.sonoff_erker_ds18b20_temperature'
AND state IS NOT NULL
AND state NOT IN ('unknown', 'unavailable')
AND FROM_UNIXTIME(last_updated_ts) >= CURRENT_DATE();
2 Likes
True, but the base sentences and intents work only in the selected language. Thatās your āturn onā āturn offā etc.
I think so. Iām prepared to order some to find out!
1 Like
Shame about the month long aliexpress wait, do quite like getting things in the post that Iāve forgot I ordered mind! Iāll probably grab a couple to test before the FP2 comes out later this year.
This release broke the Sonos integration for meā¦ Every 15 minutes or so I get messages for three of my devices, to several URLs on each device.
Subscription renewal failed for [DEVICE]: 412, message=āPrecondition Failedā, ā¦
This is noticeable as I see the media-control card āblankā for a while. After a minute or two, it fixes itself. Then the whole cycle repeats.
Iāve backed out to the 2023.1.7 release, and the issue goes away, which does suggest itās a change made in this release.
My configuration isnāt straight-forward, as I have the speakers on a different VLAN to HASS which runs in docker, using host networking mode, so this may be a factorā¦
I seem to recall having similar issues early last year, but donāt recall the details now.
Is anyone else seeing this?
Hi All,
Does someone know how you known when migration of database is done?
below is answered. Above question is still open.
and second there Assist icon is not shown. cleared cache, restart browser etc already done.
I have no default: in my configā¦ I think that I need to add
assist:
to configuration file?
Must be conversation:
1 Like
Itās conversation:
1 Like
Thanks saw a other post so was little to fast.
Then only my migration database question is still there 
Hi
Iām using the custom integration āDaily Sensorā for this purpose, doesnāt help for your query of course but itās an alternative without dependencies to SQL-Queries.
Armin
Yep, this appears to work! Many thanks!!!
Solution from m0wlheld gives me empty result. But, i guess i forgot to mention that i use mariadb on synology, not built-in database from HAā¦sorry for thatā¦ some commands are a bit differentā¦
Although i donāt know what iām gonna do, since synology hasnāt updated mariadb yet, so iām stuck with critical warning in HAā¦
Assist will always use the system language that has been set in the user settings (at least for nowā¦ The devs are still continuing the development - so more features and options are likely)
So if you do have a user with language setting āenglishā - Assist will work in english.
If you do have another user witch Czech language setting, assist will use czech for this user.
For your entities - you should use Aliases - if you want to translate the entity name into different languages for assist.