0

Assuming I have a table called MyTable, with a TimeStamp field "Created". I want only extract the Date part of this column, and in my research I found these alternatives:

SELECT created AS "Original",
       date(created) AS "DateFunction",
       created::date AS "DoubleColonCast",
       cast(created as date) AS "CastFunction",
       date_trunc('day', created) AS "DateTrunc"
FROM MyTable
  1. In matter of SARGability, what is the preferred method?
  2. What is the "SQL ANSI" (ie more portable) way?
  3. What is the most common/used in Postgres?
0

3 Answers 3

2

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 "Postgres land". 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.

1

All of these forms are equally good.

If any of these expressions appears in a WHERE clause like

WHERE date(created) = '2021-01-30'

then you have to create the index with exactly the same expression:

CREATE INDEX ON mytable (date(created));
1

created::date and cast(created as date) are syntactically equivalent but cast(created as date) will be the most portable of them all as this is the most closest to ANSI SQL.

I don't believe any of them are SARGable, but date_trunc('day', created) can result in an extra cast operation to occur to convert the date value to a timestamp prior to applying it's logic, so your best bet all around, out of all them, would be to use cast(created as date).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.