Just updated...

peter=# SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
ERROR:  Timestamp with time zone units 'dow' not recognized

This is documented to work.

peter=# SELECT EXTRACT(DOW FROM TIME '20:38:40');
ERROR:  Interval units 'dow' not recognized

The expression is nonsensical, but so is the result.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Wed, 10 Oct 2001 00:35:25 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
EXTRACT broken
> Just updated...
> peter=# SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
> ERROR:  Timestamp with time zone units 'dow' not recognized
> This is documented to work.

Ah, I broke this with some recent additions to implement more ISO
conventions (I changed the behavior of the date/time parser so that it
does not willingly ignore unrecognized fields).

I see the problem and the solution, but am in the middle of a few
changes to SET code and can't test at the moment. Hopefully I'll get
this fixed in the next couple of days, and if not I'll get it done early
next week.

Would you like to add some tests to the regression suite? Clearly this
isn't covered there...

> peter=# SELECT EXTRACT(DOW FROM TIME '20:38:40');
> ERROR:  Interval units 'dow' not recognized
> The expression is nonsensical, but so is the result.

Hmm. Why is the result nonsensical? "day of week" does not have meaning
for intervals, so it should not be recognized, right?

It is the same result as saying

  SELECT timestamp_part('yabadabadoo', time '20:38:40');

                       - Thomas

Thu, 11 Oct 2001 06:46:12 +0000
Thomas Lockhart <lockhart@fourpalms.org>
Re: EXTRACT broken
Thomas Lockhart writes:

> > peter=# SELECT EXTRACT(DOW FROM TIME '20:38:40');
> > ERROR:  Interval units 'dow' not recognized
> > The expression is nonsensical, but so is the result.
>
> Hmm. Why is the result nonsensical? "day of week" does not have meaning
> for intervals, so it should not be recognized, right?

It's the "interval" part that's troubling me, since it appears nowhere in
the original expression.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Thu, 11 Oct 2001 22:02:42 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: EXTRACT broken
> > > peter=# SELECT EXTRACT(DOW FROM TIME '20:38:40');
> > > ERROR:  Interval units 'dow' not recognized
> > > The expression is nonsensical, but so is the result.
> > Hmm. Why is the result nonsensical? "day of week" does not have meaning
> > for intervals, so it should not be recognized, right?
> It's the "interval" part that's troubling me, since it appears nowhere in
> the original expression.

Oh yeah. We don't have a date_part(units, time) function defined, so it
is getting converted to interval (which in other contexts *does* have
some usefulness as a "time equivalent").

We could fairly easily define a date_part() for the time and timetz data
types.

                      - Thomas

Fri, 12 Oct 2001 05:37:52 +0000
Thomas Lockhart <lockhart@fourpalms.org>
Re: EXTRACT broken
Thomas Lockhart writes:

> Oh yeah. We don't have a date_part(units, time) function defined, so it
> is getting converted to interval (which in other contexts *does* have
> some usefulness as a "time equivalent").

You're going to have an extremely hard time convincing me of that.

> We could fairly easily define a date_part() for the time and timetz data
> types.

I had figured that time would be cast to timestamp.  Which is probably
what it used to do.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Sat, 13 Oct 2001 20:15:39 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: EXTRACT broken
Peter Eisentraut <peter_e@gmx.net> writes:
> I had figured that time would be cast to timestamp.

How would you do that?  With no date available, you're short all the
high-order bits ...

			regards, tom lane

Sat, 13 Oct 2001 15:08:19 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: EXTRACT broken 
Tom Lane writes:

> Peter Eisentraut <peter_e@gmx.net> writes:
> > I had figured that time would be cast to timestamp.
>
> How would you do that?  With no date available, you're short all the
> high-order bits ...

For the purpose of extracting the fields that time does provide, namely
hour, minute, and second, it wouldn't matter.  At least it gives me a much
better feeling than casting to interval, which is a completely different
kind of quantity.

Of course, a separate date_part for time and date would make the most
sense.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Sat, 13 Oct 2001 21:26:00 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: EXTRACT broken 
> > Oh yeah. We don't have a date_part(units, time) function defined, so it
> > is getting converted to interval (which in other contexts *does* have
> > some usefulness as a "time equivalent").
> You're going to have an extremely hard time convincing me of that.

OK, thanks for the warning. I'll try later when I have more time...

> > We could fairly easily define a date_part() for the time and timetz data
> > types.
> I had figured that time would be cast to timestamp.  Which is probably
> what it used to do.

Tom Lane pointed out the problem of inferring an appropriate date for
the upcast.

                       - Thomas

Mon, 15 Oct 2001 05:34:12 +0000
Thomas Lockhart <lockhart@fourpalms.org>
Re: EXTRACT broken