Re: cross-database time extract?

On Monday 28 December 2009 8:58:38 am Israel Brewster wrote:
> 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

It would seem that the best solution is your original one of SELECT
time(timestamp_field). This works in the three databases you mentioned with the
provision that you have to double quote "time" in Postgres.

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