Обсуждение: cross-database time extract?

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 :)

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

--
Adrian Klaver
aklaver@comcast.net

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
> -----------------------------------------------

As to the "time" issue see here;
http://www.postgresql.org/docs/8.4/interactive/sql-expressions.html
4.2.9. Type Casts

--
Adrian Klaver
aklaver@comcast.net

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@comcast.net
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.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
-----------------------------------------------

2009/12/29 Israel Brewster <israel@frontierflying.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

On Monday 28 December 2009 12:13:46 pm Greenhorn wrote:
> 2009/12/29 Israel Brewster <israel@frontierflying.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.

--
Adrian Klaver
aklaver@comcast.net

On Mon, Dec 28, 2009 at 6:34 PM, Adrian Klaver <aklaver@comcast.net> wrote:
> On Monday 28 December 2009 12:13:46 pm Greenhorn wrote:
>> 2009/12/29 Israel Brewster <israel@frontierflying.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.

2009/12/24 Israel Brewster <israel@frontierflying.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

On Tuesday 29 December 2009 5:01:03 am Osvaldo Kussama wrote:
> 2009/12/24 Israel Brewster <israel@frontierflying.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 Klaver
aklaver@comcast.net

Osvaldo Kussama escribió:

> 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);
>                        ^

Note that the reason the second query doesn't work is that $$ is a way
to replace quoting for string literals, i.e. what ' (single quote) does
normally.  " (double quote) is used to quote identifiers, not literals.
Different thing.

When you write "time" you are invoking the function because it's parsed
as an identifier.  When you write time (no quotes) you are invoking the
reserved keyword.  The double quotes strip the "reservedness" and it's
treated like an ordinary keyword.  When you write pg_catalog.time this
is parsed as an identifier too because keywords cannot be
schema-qualified.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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@comcast.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@comcast.net

2009/12/29 Adrian Klaver <aklaver@comcast.net>:
> On Tuesday 29 December 2009 5:01:03 am Osvaldo Kussama wrote:
>> 2009/12/24 Israel Brewster <israel@frontierflying.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

On Dec 29, 2009, at 5:41 AM, Adrian Klaver wrote:

> 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' ...
>>>> -----------------------------------------------
>>>> 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@comcast.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.

Agreed. It's fairly easy to add the quotes when needed, after which
everything works as desired. Thanks for all the feedback and
explanations!

>
>
>
> --
> Adrian Klaver
> aklaver@comcast.net
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.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
-----------------------------------------------