Re: cross-database time extract?

On Tuesday 29 December 2009 5:01:03 am Osvaldo Kussama wrote:
> 2009/12/24 Israel Brewster <israel(at)frontierflying(dot)com>:
> > This is sort of a PostgreSQL question/sort of a general SQL question, so
> > I apologize if this isn't the best place to ask. At any rate, I know in
> > PostgreSQL you can issue a command like 'SELECT "time"(timestamp_column)
> > from table_name' to get the time part of a timestamp. The problem is that
> > this command for some reason requires quotes around the "time" function
> > name, which breaks the command when used in SQLite (I don't know about
> > MySQL yet, but I suspect the same would be true there). The program I am
> > working on is designed to work with all three types of databases (SQLite,
> > PostgreSQL, and MySQL) so it would be nice (save me some programing) if
> > there was a single SQL statement to get the time portion of a timestamp
> > that would work with all three. Is there such a beast? On a related note,
> > why do we need the quotes around "time" for the function to work in
> > PostgreSQL? the date function doesn't need them, so I know it's not just
> > a general PostgreSQL formating difference. Thanks :)
> > -----------------------------------------------
>
> It's a bug?
>
> bdteste=# SELECT time(CURRENT_TIMESTAMP);
> ERRO: erro de sintaxe em ou próximo a "CURRENT_TIMESTAMP"
> LINE 1: SELECT time(CURRENT_TIMESTAMP);
> ^
> bdteste=# SELECT pg_catalog.time(CURRENT_TIMESTAMP);
> time
> -----------------
> 10:55:07.073911
> (1 registro)
>
> bdteste=# SELECT "time"(CURRENT_TIMESTAMP);
> time
> -----------------
> 10:55:20.679684
> (1 registro)
>
> bdteste=# SELECT $$time$$(CURRENT_TIMESTAMP);
> ERRO: erro de sintaxe em ou próximo a "("
> LINE 1: SELECT $$time$$(CURRENT_TIMESTAMP);
> ^
>
> Osvaldo

It is documented behavior.

To quote from here:
http://www.postgresql.org/docs/8.4/interactive/sql-expressions.html#SQL-SYNTAX-TYPE-CASTS

" It is also possible to specify a type cast using a function-like syntax:

typename ( expression )

However, this only works for types whose names are also valid as function names.
For example, double precision cannot be used this way, but the equivalent
float8 can. Also, the names interval, time, and timestamp can only be used in
this fashion if they are double-quoted, because of syntactic conflicts.
Therefore, the use of the function-like cast syntax leads to inconsistencies
and should probably be avoided.

Note: The function-like syntax is in fact just a function call. When one of
the two standard cast syntaxes is used to do a run-time conversion, it will
internally invoke a registered function to perform the conversion. By
convention, these conversion functions have the same name as their output type,
and thus the "function-like syntax" is nothing more than a direct invocation of
the underlying conversion function. Obviously, this is not something that a
portable application should rely on. For further details see CREATE CAST. "

--
Adrian Klaver
aklaver(at)comcast(dot)net