Re: [GENERAL] cross-database time extract?
2009/12/24 Israel Brewster isr...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] cross-database time extract?
On Tuesday 29 December 2009 5:01:03 am Osvaldo Kussama wrote: 2009/12/24 Israel Brewster isr...@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 akla...@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
Re: [GENERAL] cross-database time extract?
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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 akla...@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 akla...@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
Re: [GENERAL] cross-database time extract?
2009/12/29 Adrian Klaver akla...@comcast.net: On Tuesday 29 December 2009 5:01:03 am Osvaldo Kussama wrote: 2009/12/24 Israel Brewster isr...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] cross-database time extract?
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 akla...@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 akla...@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 --- BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 akla...@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 --- BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] cross-database time extract?
2009/12/29 Israel Brewster isr...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] cross-database time extract?
On Monday 28 December 2009 12:13:46 pm Greenhorn wrote: 2009/12/29 Israel Brewster isr...@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 akla...@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
Re: [GENERAL] cross-database time extract?
On Mon, Dec 28, 2009 at 6:34 PM, Adrian Klaver akla...@comcast.net wrote: On Monday 28 December 2009 12:13:46 pm Greenhorn wrote: 2009/12/29 Israel Brewster isr...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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 :) ---Israel BrewsterComputer SupportTechnician IIFrontier Flying Service Inc.5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7250 x293---BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD
Re: [GENERAL] 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 akla...@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
Re: [GENERAL] 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 --- 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 akla...@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
