Re: cross-database time extract?

2009/12/29 Adrian Klaver <aklaver(at)comcast(dot)net>:
> 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 and Alvaro, thanks for explanation.

Osvaldo