What is the difference between these extracting date method?
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.