Re: TO_DATE function between PostgreSQL 8.2 and 9.4

gzh <gzhcoder(at)126(dot)com> writes:
> I run following sql in PostgreSQL 8.2, it return my expected result as ¡®YYYY-MM-DD¡¯ format.

> --PostgreSQL 8.2
> ---------------

> SELECT to_date(now() + '-7day', 'YYYY-MM-DD');

TBH, this was poor SQL code in 8.2, and it's poor SQL code now.
to_date doesn't take timestamp, and never has: it takes text.
The reason the query succeeded in 8.2 was that at the time, the
server would silently cast just about anything to text if necessary
to find a workable interpretation of the query. We got rid of that
behavior in 8.3, because way too often the server's opinion of a
"workable interpretation" was surprising.

So you could make it work the way it used to by inserting the text
coercion explicitly:

=# SELECT to_date((now() + '-7day')::text, 'YYYY-MM-DD');
to_date
------------
2022-05-10

However, this hasn't done anything to make the call safer or more
performant. It's slow because of an unnecessary formatting and
deformatting of the text equivalent of the timestamp, and it's
unsafe because your format choice represents a hard-wired assumption
that DateStyle is ISO. For example, if I do

=# set datestyle = postgres;
SET

which means that timestamps will render to text like

=# select (now() + '-7day');
?column?
-------------------------------------
Tue May 10 12:11:25.474873 2022 EDT
(1 row)

then it falls over completely:

=# SELECT to_date((now() + '-7day')::text, 'YYYY-MM-DD');
ERROR: invalid value "Tue " for "YYYY"
DETAIL: Value must be an integer.

You could get the equivalent behavior quicker and more safely by just
casting the timestamp value to date:

=# select (now() + '-7day')::date;
date
------------
2022-05-10
(1 row)

You might also want to look at other non-text-based manipulations
such as date_trunc() and date_part().

regards, tom lane