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

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

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

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

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

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

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