Thread: Re: Adding time to DATE type

Re: Adding time to DATE type

From
Bruce Momjian
Date:
Can someone give me a TODO summary for this issue?


[ Charset ISO-8859-1 unsupported, converting... ]
> On Fri, 17 Mar 2000, Bruce Momjian wrote:
> 
> >     test=> SELECT date('1/1/1992') + timespan('1 year');
> 
> If I may point something out here, the correct syntax for this in SQL92 is
> 
> SELECT DATE '1/1/1992' + INTERVAL '1 year'
> 
> (Ignoring the fact that neither the date nor the interval strings have the
> correct format.)
> 
> This converts to a cast in PostgreSQL, which is fine, but the standard
> makes a semantic distinction:
> 
>     CAST('2000-02-29' AS DATE)
> 
> converts a character literal to date
> 
>     DATE '2000-02-29'
> 
> *is* a date literal. Furthermore, just
> 
>     '2000-02-29'
> 
> is not a date literal.
> 
> I've been doing some lobbying to get rid of the "unknown" type because SQL
> is perfectly clear about what "quote-stuff-quote" means (character type)
> and in absence of any evidence to the contrary (such as a function only
> taking date arguments, inserting it into a date field) it should be
> treated as such. That will get rid of such embarrassments as
> 
>     SELECT 'a' LIKE 'a' -- try it
> 
> Tom believes that this will create a pain for the odd data type crowd but
> I don't think that this is so (or at least has to be so) whereas the
> current behavior creates a pain for the normal data type crowd.
> 
> Just my ideas.
> 
> 
> -- 
> Peter Eisentraut                  Sernanders v?g 10:115
> peter_e@gmx.net                   75262 Uppsala
> http://yi.org/peter-e/            Sweden
> 
> 


--  Bruce Momjian                        |  http://www.op.net/~candle pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: Adding time to DATE type

From
Peter Eisentraut
Date:
Bruce Momjian writes:

> Can someone give me a TODO summary for this issue?

* make 'text' constants default to text type (not unknown)

(I think not everyone's completely convinced on this issue, but I don't
recall anyone being firmly opposed to it.)

* add SQL interval syntax


> > On Fri, 17 Mar 2000, Bruce Momjian wrote:
> > 
> > >     test=> SELECT date('1/1/1992') + timespan('1 year');
> > 
> > If I may point something out here, the correct syntax for this in SQL92 is
> > 
> > SELECT DATE '1/1/1992' + INTERVAL '1 year'
> > 
> > (Ignoring the fact that neither the date nor the interval strings have the
> > correct format.)
> > 
> > This converts to a cast in PostgreSQL, which is fine, but the standard
> > makes a semantic distinction:
> > 
> >     CAST('2000-02-29' AS DATE)
> > 
> > converts a character literal to date
> > 
> >     DATE '2000-02-29'
> > 
> > *is* a date literal. Furthermore, just
> > 
> >     '2000-02-29'
> > 
> > is not a date literal.
> > 
> > I've been doing some lobbying to get rid of the "unknown" type because SQL
> > is perfectly clear about what "quote-stuff-quote" means (character type)
> > and in absence of any evidence to the contrary (such as a function only
> > taking date arguments, inserting it into a date field) it should be
> > treated as such. That will get rid of such embarrassments as
> > 
> >     SELECT 'a' LIKE 'a' -- try it
> > 
> > Tom believes that this will create a pain for the odd data type crowd but
> > I don't think that this is so (or at least has to be so) whereas the
> > current behavior creates a pain for the normal data type crowd.
> > 
> > Just my ideas.
> > 
> > 
> > -- 
> > Peter Eisentraut                  Sernanders v?g 10:115
> > peter_e@gmx.net                   75262 Uppsala
> > http://yi.org/peter-e/            Sweden
> > 
> > 
> 
> 
> 

-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden




Re: Adding time to DATE type

From
Bruce Momjian
Date:
[ Charset ISO-8859-1 unsupported, converting... ]
> Bruce Momjian writes:
> 
> > Can someone give me a TODO summary for this issue?
> 
> * make 'text' constants default to text type (not unknown)
> 
> (I think not everyone's completely convinced on this issue, but I don't
> recall anyone being firmly opposed to it.)

I don't know but I know it came up in the last month.  Something about
character strings not being considered TEXT, and they had to be cast to
TEXT to be used.

> 
> * add SQL interval syntax

These must be your own items. I don't see them on the main TODO list.

--  Bruce Momjian                        |  http://www.op.net/~candle pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: Adding time to DATE type

From
Tom Lane
Date:
Peter Eisentraut <peter_e@gmx.net> writes:
> Bruce Momjian writes:
>> Can someone give me a TODO summary for this issue?

> * make 'text' constants default to text type (not unknown)

> (I think not everyone's completely convinced on this issue, but I don't
> recall anyone being firmly opposed to it.)

It would be a mistake to eliminate the distinction between unknown and
text.  See for example my just-posted response to John Cochran on
pgsql-general about why 'BOULEVARD'::text behaves differently from
'BOULEVARD'::char.  If string literals are immediately assigned type
text then we will have serious problems with char(n) fields.

I think it's fine to assign string literals a type of 'unknown'
initially.  What we need to do is add a phase of type resolution that
considers treating them as text, but only after the existing logic fails
to deduce a type.

(BTW it might be better to treat string literals as defaulting to char(n)
instead of text, allowing the normal promotion rules to replace char(n)
with text if necessary.  Not sure if that would make things more or less
confusing for operations that intermix fixed- and variable-width char
types.)
        regards, tom lane


Re: Adding time to DATE type

From
Peter Eisentraut
Date:
On Mon, 12 Jun 2000, Tom Lane wrote:

> > * make 'text' constants default to text type (not unknown)

> I think it's fine to assign string literals a type of 'unknown'
> initially.  What we need to do is add a phase of type resolution that
> considers treating them as text, but only after the existing logic fails
> to deduce a type.

Hence "default to"


-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden



Re: Adding time to DATE type

From
Peter Eisentraut
Date:
On Sun, 11 Jun 2000, Bruce Momjian wrote:

> > > Can someone give me a TODO summary for this issue?
> > 
> > * make 'text' constants default to text type (not unknown)
> > 
> > (I think not everyone's completely convinced on this issue, but I don't
> > recall anyone being firmly opposed to it.)
> 
> I don't know but I know it came up in the last month.  Something about
> character strings not being considered TEXT, and they had to be cast to
> TEXT to be used.
> 
> > 
> > * add SQL interval syntax
> 
> These must be your own items. I don't see them on the main TODO list.

?? You asked for a TODO summary, and these are the things that would need
TO be DOne in order to address the issue originally at hand.

-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden



Re: Adding time to DATE type

From
Bruce Momjian
Date:
Oh, OK.

[ Charset ISO-8859-1 unsupported, converting... ]
> On Sun, 11 Jun 2000, Bruce Momjian wrote:
> 
> > > > Can someone give me a TODO summary for this issue?
> > > 
> > > * make 'text' constants default to text type (not unknown)
> > > 
> > > (I think not everyone's completely convinced on this issue, but I don't
> > > recall anyone being firmly opposed to it.)
> > 
> > I don't know but I know it came up in the last month.  Something about
> > character strings not being considered TEXT, and they had to be cast to
> > TEXT to be used.
> > 
> > > 
> > > * add SQL interval syntax
> > 
> > These must be your own items. I don't see them on the main TODO list.
> 
> ?? You asked for a TODO summary, and these are the things that would need
> TO be DOne in order to address the issue originally at hand.
> 
> -- 
> Peter Eisentraut                  Sernanders v?g 10:115
> peter_e@gmx.net                   75262 Uppsala
> http://yi.org/peter-e/            Sweden
> 
> 


--  Bruce Momjian                        |  http://www.op.net/~candle pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: Adding time to DATE type

From
Bruce Momjian
Date:
Is this something worth addressing?

> Peter Eisentraut <peter_e@gmx.net> writes:
> > Bruce Momjian writes:
> >> Can someone give me a TODO summary for this issue?
> 
> > * make 'text' constants default to text type (not unknown)
> 
> > (I think not everyone's completely convinced on this issue, but I don't
> > recall anyone being firmly opposed to it.)
> 
> It would be a mistake to eliminate the distinction between unknown and
> text.  See for example my just-posted response to John Cochran on
> pgsql-general about why 'BOULEVARD'::text behaves differently from
> 'BOULEVARD'::char.  If string literals are immediately assigned type
> text then we will have serious problems with char(n) fields.
> 
> I think it's fine to assign string literals a type of 'unknown'
> initially.  What we need to do is add a phase of type resolution that
> considers treating them as text, but only after the existing logic fails
> to deduce a type.
> 
> (BTW it might be better to treat string literals as defaulting to char(n)
> instead of text, allowing the normal promotion rules to replace char(n)
> with text if necessary.  Not sure if that would make things more or less
> confusing for operations that intermix fixed- and variable-width char
> types.)
> 
>             regards, tom lane
> 


--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: Adding time to DATE type

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Is this something worth addressing?

Yes, but not when we're already overdue for beta.  We've been around
on the question of type promotion rules several times, and no one has
yet put forward a solution that everyone else liked.  I don't expect
to see a usable solution both proposed and implemented in the next
three weeks...
        regards, tom lane