Re: cross-database time extract?

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