Thread: Can't "EXTRACT" from a field?
Simple, really ... using v7.2b3 that hasn't been released yet ...
traf_stats=# select EXTRACT(WEEK FROM TIMESTAMP runtime) from hourly_stats;
ERROR: parser: parse error at or near "runtime"
traf_stats=# \d hourly_stats Table "hourly_stats" Column | Type | Modifiers
------------+-----------------------------+-----------from_ip | inet |to_ip | inet
|port | integer |bytes | bigint |runtime |
timestamp(6)with time zone |no_records | integer |
Indexes: hourly_from_ip, hourly_to_ip
On 22 Nov 2001 at 10:02 (-0500), Marc G. Fournier wrote: | | Simple, really ... using v7.2b3 that hasn't been released yet ... | | traf_stats=# select EXTRACT(WEEK FROM TIMESTAMP runtime) from hourly_stats; | ERROR: parser: parse error at or near "runtime" The following works for me (on 7.2b3). create table test( id serial, tid timestamp default now() ); select extract(week from tid) from test; gram.y has extract_list: extract_arg FROM a_expr which appears to be in keeping with the sql99 def. part2- <extract expression> ::= part2: EXTRACT <leftparen> <extract field> part2- FROM <extract source> <right paren> I don't know if there was ever any other format for extract(), but things look normal from here. btw Marc, can you help me in getting archives of the various lists? cheers. brent -- "Develop your talent, man, and leave the world something. Records are really gifts from people. To think that an artist would love you enough to share his music with anyone is a beautiful thing." -- Duane Allman
On Thu, 22 Nov 2001, Brent Verner wrote: > On 22 Nov 2001 at 10:02 (-0500), Marc G. Fournier wrote: > | > | Simple, really ... using v7.2b3 that hasn't been released yet ... > | > | traf_stats=# select EXTRACT(WEEK FROM TIMESTAMP runtime) from hourly_stats; > | ERROR: parser: parse error at or near "runtime" > > The following works for me (on 7.2b3). > > create table test( id serial, tid timestamp default now() ); > select extract(week from tid) from test; > > > gram.y has > extract_list: extract_arg FROM a_expr > > which appears to be in keeping with the sql99 def. > part2- <extract expression> ::= > part2: EXTRACT <left paren> <extract field> > part2- FROM <extract source> <right paren> > > > I don't know if there was ever any other format for extract(), but > things look normal from here. ya, I hadn't clued in until fighting with it some more that if the fieldis already a timestamp, yyou don't have to put it in as 'EXTRACT(WEEK FROM TIMESTAMP tid) :( > btw Marc, can you help me in getting archives of the various lists? they are all at archives.postgresql.org ... no?
On 22 Nov 2001 at 11:18 (-0500), Marc G. Fournier wrote: | On Thu, 22 Nov 2001, Brent Verner wrote: | | > On 22 Nov 2001 at 10:02 (-0500), Marc G. Fournier wrote: | > | | > | Simple, really ... using v7.2b3 that hasn't been released yet ... | > | | > | traf_stats=# select EXTRACT(WEEK FROM TIMESTAMP runtime) from hourly_stats; | > | ERROR: parser: parse error at or near "runtime" | > | > The following works for me (on 7.2b3). | > | > create table test( id serial, tid timestamp default now() ); | > select extract(week from tid) from test; | > | > | > gram.y has | > extract_list: extract_arg FROM a_expr | > | > which appears to be in keeping with the sql99 def. | > part2- <extract expression> ::= | > part2: EXTRACT <left paren> <extract field> | > part2- FROM <extract source> <right paren> | > | > | > I don't know if there was ever any other format for extract(), but | > things look normal from here. | | ya, I hadn't clued in until fighting with it some more that if the fieldis | already a timestamp, yyou don't have to put it in as 'EXTRACT(WEEK FROM | TIMESTAMP tid) :( I didn't even know what the args to extract were, which is how I ended up in gram.y... note: extract() is correctly not listed as a function, but it doesn't have any '\h' help available. Is this a TODOkind-of-thing? If it is I can try to add it sometime later today. There is not note2: This documentation is incorrect as of last night's cvs. an example given in the docs is. SELECT EXTRACT(CENTURYFROM TIMESTAMP '2001-02-16 20:38:40'); I can go ahead and update func.sgml if noone else is already getting it. | > btw Marc, can you help me in getting archives of the various lists? | | they are all at archives.postgresql.org ... no? Not that I see. archives. points at www2.us. I ftp'd around every postgresql.org (and hub.org) anonftp server I could find, and sent an 'index' command to majordomo, and it has /some/ files listed, but I could not ever retreive those files... I'm really looking forward to having locally (mutt!!) searchable docs... Did you ever get the documentation issue worked out? Is there anything I can do to help with that? I did get man.tar and postgres.tar built from cvs on my debian box. Thanks, Brent -- "Develop your talent, man, and leave the world something. Records are really gifts from people. To think that an artist would love you enough to share his music with anyone is a beautiful thing." -- Duane Allman
On 22 Nov 2001 at 11:18 (-0500), Marc G. Fournier wrote:
|
| ya, I hadn't clued in until fighting with it some more that if the fieldis
| already a timestamp, yyou don't have to put it in as 'EXTRACT(WEEK FROM
| TIMESTAMP tid) :(
Ok, scratch my previous email WRT the sgml docs being wrong...
Something is strange, tho.
brent=# select extract( week from timestamp ('2001-02-06 20:38:40'::timestamp)>
ERROR: parser: parse error at or near "'"
brent=# select extract( week from "timestamp" ('2001-02-06 20:38:40'::timestam>date_part
----------- 6
(1 row)
brent=# select extract( week from timestamp ('2001-02-06 20:38:40') );
ERROR: parser: parse error at or near "'"
brent=# select extract( week from timestamp '2001-02-06 20:38:40'::timestamp );date_part
----------- 6
(1 row)
brent=# select extract( week from timestamp '2001-02-06 20:38:40' );date_part
----------- 6
(1 row)
Notice: timestamp( type ) => fail "timestamp"( type ) => OK timestamp type => OK timestamp
column_of_type => fail [1] Marc's original observation.
can't help any more... brent
--
"Develop your talent, man, and leave the world something. Records are
really gifts from people. To think that an artist would love you enough
to share his music with anyone is a beautiful thing." -- Duane Allman
Brent Verner <brent@rcfile.org> writes:
> note: extract() is correctly not listed as a function, but it doesn't
> have any '\h' help available. Is this a TODO kind-of-thing? If
> it is I can try to add it sometime later today.
There is no mechanism for keeping track of help entries for functions,
only statements.
> note2: This documentation is incorrect as of last night's cvs.
> an example given in the docs is.
> SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');
This example is fine. Try it.
regards, tom lane
Brent Verner <brent@rcfile.org> writes:
> Something is strange, tho.
The construct you're poking around the edges of here is
type-name literal-string
which is an SQLish typed constant. (Or more accurately, it's Thomas'
generalization of some type-specific constant syntaxes that appear in
SQL92. AFAIK the spec itself doesn't claim this is a type-universal
construction.)
Casting something other than a string literal requires different, more
explicit syntax; eg, CAST(foo AS type), foo::type, or if the type name
is allowable as a function name type(foo) will work.
regards, tom lane