SQL integration date() and datetime() in queries have different time zone behavior

[boern99]

The problem

Trying this Query directly via SQLite gives me 2h offset for GMT+2; select timediff(datetime('now', 'localtime'), datetime('now', 'utc')) as utc_offset; Trying the same Query in Home Assistant SQL Integration gives 0 offset on the entity:

This leads to unexpected behavior, when counting values for yesterday or today using date("now"), for example, following works, but not between 0:00 and 2:00 in the morning:

SELECT (Max(state) - Min(state)) as val FROM states  
WHERE metadata_id = (SELECT metadata_id FROM states_meta  
WHERE entity_id = 'sensor.pv_panels_energy') and 
last_updated_ts  > (SELECT unixepoch(datetime('now'),'-1 days')) and 
last_updated_ts  < (SELECT unixepoch(datetime(date('now'),'utc')));

i am using the integrated sqlite-DB

What version of Home Assistant Core has the issue?

core-2024.8.2

What was the last working version of Home Assistant Core?

core-2024.8.2

What type of installation are you running?

Home Assistant Container

Integration causing the issue

SQL Integration

Link to integration documentation on our website

No response

Diagnostics information

No response

Example YAML snippet

No response

Anything in the logs that might be useful for us?

No response

Additional information

No response

[boern99]

think there is a difference between datetime and date behaviour: SELECT datetime('now') gives me for my GMT+2 Setup an 2 hour Offset: 2024-08-28 05:30:06 as output: its now: 2024-08-28 07:30:06 the 2024-08-28 05:30:06 can be easy used for databasecomparisen as the timestamp is also UTC if i do a select date('now') there is no offset and therefor the comparisons against the database are -2hours from the UTC-Timestamps. Even the workaround using the timezone in an query does not work: date('now', 'utc') does not correct this: utc is ignored in Home Assistant SQL Integration.

[home-assistant[bot]]

Hey there @gjohansson-st, @dougiteixeira, mind taking a look at this issue as it has been labeled with an integration (sql) you are listed as a code owner for? Thanks!

Code owner commands

Code owners of `sql` can trigger bot actions by commenting: - `@home-assistant close` Closes the issue. - `@home-assistant rename Awesome new title` Renames the issue. - `@home-assistant reopen` Reopen the issue. - `@home-assistant unassign sql` Removes the current integration label and assignees on the issue, add the integration domain after the command. - `@home-assistant add-label needs-more-information` Add a label (needs-more-information, problem in dependency, problem in custom component) to the issue. - `@home-assistant remove-label needs-more-information` Remove a label (needs-more-information, problem in dependency, problem in custom component) on the issue.

_{^{(message by CodeOwnersMention)}}

---

sql documentation sql source _{^{(message by IssueLinks)}}

[issue-triage-workflows[bot]]

There hasn't been any activity on this issue recently. Due to the high number of incoming GitHub notifications, we have to clean some of the old issues, as many of them have already been resolved with the latest updates. Please make sure to update to the latest Home Assistant version and check if that solves the issue. Let us know if that works for you by adding a comment 👍 This issue has now been marked as stale and will be closed if no further activity occurs. Thank you for your contributions.