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?
Click Ok
  • 147
  • 1
  • 7

3 Answers3

2

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).

J.D.
  • 37,483
  • 8
  • 54
  • 121
2

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));
Laurenz Albe
  • 51,298
  • 4
  • 39
  • 69
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.