Stefano Buliani wrote:
> Richard,
>
> understand I shouldn't be comparing a date to a timestamp. Fact is I
> need the full timestamp to process other info in the rest of the function.
>
> My question is: why is the planner casting the timestamp to date when I
> run the query from psql and the other way around from the function?
It's not. As I said, a quoted literal isn't necessarily a timestamp.
This: '2008-12-09 18:23:00' is not a timestamp.
It is an untyped quoted literal that contains something I'll grant
*looks* like a timestamp, but we can't tell what it is really supposed
to be until it's used.
SELECT length('2008-12-09 18:00:00');
Here it must be text (because we don't have a length() defined for
timestamps - see \df length).
=> SELECT date_trunc('month', '2008-12-09 18:00:00');
ERROR: function date_trunc(unknown, unknown) is not unique
LINE 1: SELECT date_trunc('month', '2008-12-09 18:00:00'); ^
HINT: Could not choose a best candidate function. You might need to add
explicit type casts.
Here it couldn't decide (\df date_trunc to see what it was choosing between)
And in the next one it guesses it has an interval (because that's what
the other thing is, I'm guessing).
=> SELECT '2008-12-09 18:00:00' + '2 hours'::interval;
ERROR: invalid input syntax for type interval: "2008-12-09 18:00:00"
So - it's not casting from timestamp to date, it's casting from
"unknown" to date in your interactive sql.
-- Richard Huxton Archonet Ltd