Re: cross-database time extract?

On Mon, Dec 28, 2009 at 6:34 PM, Adrian Klaver <aklaver(at)comcast(dot)net> wrote:
> On Monday 28 December 2009 12:13:46 pm Greenhorn wrote:
>> 2009/12/29 Israel Brewster <israel(at)frontierflying(dot)com>:
>> > 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
>>
>> you could try select timestamp_column::time from table_name
>
> That would work in Postgres, but the OP was looking for a cast method that would
> also work in MySQL and SQLite. The cast(value as type) is SQL standard :) The
> question remains why SQLite is not behaving correctly? Datetime awareness in
> SQLite is still relatively new, I will have to do some exploring on that issue.

Also, MySQL's time math is basically functionally retarded when you
start trying to set timezones.