Thread: Fwd: Re: [GENERAL] Extracting time from timestamp

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

> > 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

"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

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