Re: cross-database time extract?

On Dec 24, 2009, at 12:53 PM, Adrian Klaver wrote:

> On Thursday 24 December 2009 1:44:58 pm Israel Brewster wrote:
>> 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 :)
>> -----------------------------------------------
>> Israel Brewster
>> Computer Support Technician II
>> Frontier Flying Service Inc.
>> 5245 Airport Industrial Rd
>> Fairbanks, AK 99709
>> (907) 450-7250 x293
>> -----------------------------------------------
>
> select cast(timestamp_column as time) from table_name

Didn't realize you could do that- thanks. This does work, sort of...
In PosgreSQL, it returns the time portion of the timestamp as desired.
However, in SQLite, while the function runs, it returns the year
portion of the timestamp, rather than the time. That would seem to be
a SQLite issue/question however. Thanks for the suggestion.

>
> --
> Adrian Klaver
> aklaver(at)comcast(dot)net
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

-----------------------------------------------
Israel Brewster
Computer Support Technician II
Frontier Flying Service Inc.
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7250 x293
-----------------------------------------------