On Friday 21 Mar 2003 11:38 am, Christopher Kings-Lynne wrote:
> > phd=# select time(abstime(timestamp 'now')) from bookings;
> > ERROR:  parser: parse error at or near "abstime" at character 13
> > phd=# select time(timestamp 'now') from bookings;
> > ERROR:  parser: parse error at or near "timestamp" at character 13
> > phd=# select version();
> >                                version
>
> Try:
>
> select "time"(abstime(timestamp 'now')) from bookings;
> select "time"(timestamp 'now') from bookings;

First of all, thanks, it worked..

And What's so holy about "" if it is a function?

That was bummer, I admit.. Spent almost a day on it..

 Shridhar

P.S. Sorry, I didn't realized that I handn' CC hackers..


Fri, 21 Mar 2003 12:15:13 +0530
"Shridhar Daithankar<shridhar_daithankar@persistent.co.in>"
	<shridhar_daithankar@persistent.co.in>
Fwd: Re: [GENERAL] Extracting time from timestamp

> > select "time"(abstime(timestamp 'now')) from bookings;
> > select "time"(timestamp 'now') from bookings;
>
> First of all, thanks, it worked..
>
> And What's so holy about "" if it is a function?

It's really old 7.1 syntax, not supported from 7.2+.

Basically it's because time can now have a precision.  eg. a field of type
TIME(4) will have decimal places of millisecond precision.  You need to
quote the function to make it get treated as a function rather than a type
definition...

A better (standard) way to express it is probably:

select cast(cast(current_timestamp as abstime) as time) from bookings;

or even

select current_timestamp::abstime::time from bookings;

Chris


Fri, 21 Mar 2003 14:50:23 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: [GENERAL] Extracting time from timestamp
"Shridhar Daithankar<shridhar_daithankar@persistent.co.in>" <shridhar_daithankar@persistent.co.in> writes:
> And What's so holy about "" if it is a function?

The problem is that TIME(n) is a datatype name, not a function call,
according to the SQL spec.  Likewise for TIMESTAMP(n), INTERVAL(n),
NUMERIC(m,n), and maybe one or two other special cases I've forgotten.

The SQL spec's love of special-purpose syntaxes is one of its worst
features IMHO ...

			regards, tom lane

Fri, 21 Mar 2003 01:55:16 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Fwd: Re: [GENERAL] Extracting time from timestamp 
On Friday 21 Mar 2003 12:25 pm, Tom Lane wrote:
> "Shridhar Daithankar<shridhar_daithankar@persistent.co.in>" 
<shridhar_daithankar@persistent.co.in> writes:
> > And What's so holy about "" if it is a function?
>
> The problem is that TIME(n) is a datatype name, not a function call,
> according to the SQL spec.  Likewise for TIMESTAMP(n), INTERVAL(n),
> NUMERIC(m,n), and maybe one or two other special cases I've forgotten.
>
> The SQL spec's love of special-purpose syntaxes is one of its worst
> features IMHO ...

In this case, I would vote for overload as SQL extension in postgresql if 
people feel it is feasible and/or sensible..

 Shridhar

Fri, 21 Mar 2003 12:41:21 +0530
"Shridhar Daithankar<shridhar_daithankar@persistent.co.in>"
	<shridhar_daithankar@persistent.co.in>
Re: Fwd: Re: [GENERAL] Extracting time from timestamp