Thread: 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. 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
> 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
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
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
> > 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