What is the difference between these extracting date method? Assuming I have a table called MyTable, with a TimeStamp field &quot;Created&quot;. I want only extract the Date part of this column, and in my research I found these alternatives:
SELECT created AS &quot;Original&quot;,
       date(created) AS &quot;DateFunction&quot;,
       created::date AS &quot;DoubleColonCast&quot;,
       cast(created as date) AS &quot;CastFunction&quot;,
       date_trunc('day', created) AS &quot;DateTrunc&quot;
FROM MyTable


In matter of SARGability, what is the preferred method?
What is the &quot;SQL ANSI&quot; (ie more portable) way?
What is the most common/used in Postgres?
As others have pointed out date(created), created::date and cast(created as date) are functionally equivalent.
If you want to be portable, use cast(). I think the :: operator is more common in &quot;Postgres land&quot;.
However, date_trunc('day', created) is not equivalent to the other expressions, because it returns a timestamp value, not a date.
Neither of those expressions will make use of an index on created - you would need to create an expression based index with the expression used in your queries. The cast() expression will be rewritten to :: by the query optimizer, while date(...) will not. So if you create an expression based index, using cast() or :: won't make a difference between the indexed expression and the one used in the query.
But if you use date(..) or date_trunc() you will have to use the same expression in your queries to make use of the indx.