cross-database time extract?

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