age
filter is used to express a filter based on the "age" of a timestamp at a particular date. Age can be passed with no parameters (indicating age at current date) or can be used with a parameter (indicating age at specified date).value
is an ISO8601 duration format.value
is any HDSI operator and an ISO8601 duration.TimeSpan
and the total seconds are used for comparison.DateTime
or DateTimeOffset
object to only the precision specified.DateTime
or DateTimeOffset
truncated to only the portion specified.@minDate
and @maxDate
are computed in .NET based on precision where:new DateTime(value.Year, 01, 01)
new DateTime(value.Year, 12, 31)
new DateTime(value.Year, value.Month, 01)
new DateTime(value.Year, value.Month, DateTime.DaysInMonth(value.Month)
value.Date
value.Date
levenshtein
function has a performance penalty in that the database tables storing the values (identifiers, addresses, etc.) needs to be sequentially scanned. If you can, consider using similarity_lev
which can uses PostgreSQL's trigram index (see similarity
extension).:(similarity)
filter function will be translated into an optimized lookup of column % 'SMITH' AND similarity(column, 'SMITH') > 0.8
. It is therefore important to properly set the default (used by the %
operator) via: similarity_lev
filter acts similar to the similarity in that it uses the underlying database technology's GIN indexing to perform a similarity, however the final result is run through the levenshtein
function. word_similarity_threshold
to 0.4 since SIN numbers with 5 edits would be represent a 60% difference in source strings.