Thread: Unexpected parse behaviour for date to timestamp conversion

I have this field:

submit   | timestamp(0) without time zone


The following two SQL queries return different values depending on
wether the date is quoted or not ...

I am assuming it is because the date is being parsed differently.

# select submit from invoices where submit <= 2003-03-09;
  submit
----------
(0 rows)

# select submit from invoices where submit <= '2003-03-09';
  submit
---------------------
  2003-03-08 00:24:25
(1 row)


Why is the '2003-03-09' parsed/unserstood differently than 2003-03-09?

Thanks,

Jc

On Sat, 8 Mar 2003, Jean-Christian Imbeault wrote:

> I have this field:
>
> submit   | timestamp(0) without time zone
>
>
> The following two SQL queries return different values depending on
> wether the date is quoted or not ...
>
> I am assuming it is because the date is being parsed differently.
>
> # select submit from invoices where submit <= 2003-03-09;

The right side isn't a date.  It's an integral expression that evaluates
to 1991 I believe.

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> On Sat, 8 Mar 2003, Jean-Christian Imbeault wrote:
>> # select submit from invoices where submit <= 2003-03-09;

> The right side isn't a date.  It's an integral expression that evaluates
> to 1991 I believe.

Yeah.  And IMHO this should be rejected entirely; there is no "date <= int4"
operator.  If you dig into it you find that the parser is actually
interpreting the query as WHERE submit::text <= '1991'::text.
Apparently "text <= text" is the closest match it can find.

This is just another example of why allowing implicit coercions from
everything to text is a Bad Idea ...

            regards, tom lane

Tom Lane wrote:
>
> Yeah.  And IMHO this should be rejected entirely; there is no "date <= int4"
> operator.  If you dig into it you find that the parser is actually
> interpreting the query as WHERE submit::text <= '1991'::text.
> Apparently "text <= text" is the closest match it can find.
>
> This is just another example of why allowing implicit coercions from
> everything to text is a Bad Idea ...

I agree. Of course my syntax was wrong and I should have spotted the
error right away, but I agree that the parser should have thrown an error.

Could this be put on the the to-do list?

As an aside, and for my own edificaton, what is the "correct" was to
pass a date in as an SQL argument? Using '2003-01-01' works but it seems
the parser is actually viewing this as text and not a date? Is this the
way it should be?

Jc

Jean-Christian Imbeault <jc@mega-bucks.co.jp> writes:
> As an aside, and for my own edificaton, what is the "correct" was to
> pass a date in as an SQL argument? Using '2003-01-01' works but it seems
> the parser is actually viewing this as text and not a date?

No.  That is actually the preferred way to do it.  A literal encased in
single quotes is not text to Postgres --- it is an untyped literal that
will be cast automatically to the datatype needed by its context.  In
particular, given an expression like
        columnref operator 'literal'
the untyped literal will preferentially be cast to the same datatype as
the column reference.  (I believe the parser will explore other possible
interpretations if that one doesn't match any available operator, but
that will definitely be what's chosen if there's a matching operator.)

            regards, tom lane