Querying Seconds on DateTime(Offset) can cause issues in e.g PostgreSQL

[gliljas]

Again, something I stumbled upon when working with TimeOnly. Everything worked fine, until I ran the tests on PostgreSQL.

The issue is that the seconds HQL method yields an extract(second from ....) for PostgreSQL, but that SQL function returns a double, with the fractional seconds included. In other words, querying for where seconds(prop)=3 or .Where(x => x.Seconds == 3) will not match if prop actually contained 3.001. Just reading the value works fine, since Int32Type truncates the value.

Apparently, the HQL methods seconds and extract(second from ....) are explicitly defined in HQL (Java) to return the seconds with fractional precision, so they instead make sure that other dialects conform to this. E.g the SQL Server dialect defines seconds as (datepart(second,?2)+datepart(nanosecond,?2)/1e9)

I'm not sure Nhibernate's HQL has to align with Hibernate's, so an option would be to ensure that e.g the Postgres dialect aligns with SQL Server et. al. and redefines seconds as extract(seconds from date_trunc('seconds', value)). Another option would be to add a new HQL function and use that from DateTimePropertiesHqlGenerator.