Обсуждение: 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