Обсуждение: Casting timestamp
Hi all, I posting this again... Why the second casting don't work? --- start with this set datestyle = 'iso'; show datestyle; select current_timestamp::text::timestamp; --- now try this set datestyle = 'postgres'; show datestyle; select current_timestamp::text::timestamp; In the first one, I get timestamptz ------------------------------- 2002-05-13 10:19:55.158434-03 In the last one, I get ERROR: Bad timestamp external representation 'Mon May 13 10:19:56.671847 2002 BRT' []'s Edipo Elder F. de Melo
> -----Original Message----- > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Edipo E. F. Melo > Sent: Saturday, May 18, 2002 7:55 PM > To: pgsql-sql > Subject: [SQL] Casting timestamp > > > Hi all, > > I posting this again... > > Why the second casting don't work? > > --- start with this > set datestyle = 'iso'; > show datestyle; > select current_timestamp::text::timestamp; > --- now try this > set datestyle = 'postgres'; > show datestyle; > select current_timestamp::text::timestamp; > > In the first one, I get > timestamptz > ------------------------------- > 2002-05-13 10:19:55.158434-03 > > In the last one, I get > ERROR: Bad timestamp external representation 'Mon May 13 10:19:56.671847 > 2002 BRT' Edipo -- The second csating doesn't work because PG doesn't recognize the time zone "BRT" -- despite the fact that it appeared to output it (!). [Full list of PG timezones at http://candle.pha.pa.us/main/writings/pgsql/sgml/timezones.html) What output do you get from SET DATESTYLE='iso'; SELECT current_timestamp; SET DATESTYLE='postgres'; SELECT current_timestamp; And where are you located geographically?
I have a table answers_boolean: question_id | integer | not nullevaluation_id | integer | not nullvalue | boolean | I'd like output in the form: question_id, count_true, count_false ....where count_true is the number of questions answered "true" SELECT question_id AS id, value AS val , count(value) AS cnt FROM answers_boolean GROUP BY question_id,value; gives me:id | val | cnt ----+-----+----- 2 | f | 3 2 | t | 3 3 | f | 2 3 | t | 4 ...which is sorta what I want. I've looked through the docs & archive but haven't found the answer. TIA
SELECT question_id, COUNT(*) FROM Ansers_Boolean WHERE value=TRUEGROUP BY question_id; sort of works; it's fast & correct, but if a question has no true responses, it leaves off that question. This may or may not be acceptable to you. You could fix that with: SELECT question_id, COUNT(*) FROM Answers_Boolean WHERE value=TRUEGROUP BY question_id UNION ALL SELECT question_id, 0 FROM Answers_Boolean AS AB0 WHERE NOT EXISTS (SELECT * FROM Answers_BooleanAS AB1 WHERE AB0.question_id = AB1.question_id AND value = TRUE); But it performs slowly. I normally create turn the bools into 1 or 0 and add them up (called a "Characteristic function"): SELECT question_id, SUM(CASE value WHEN TRUE THEN 1 ELSE 0 END) AS num_true FROM Answers_Boolean GROUP BY question_id; Plus, it's easy this way to add a num_false column, by just copying num_true. Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton Knowledge Management & Technology Consultant > -----Original Message----- > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Dan MacNeil > Sent: Sunday, May 19, 2002 10:17 AM > To: pgsql-sql > Subject: [SQL] count(boolean) > > > I have a table answers_boolean: > > question_id | integer | not null > evaluation_id | integer | not null > value | boolean | > > I'd like output in the form: > > question_id, count_true, count_false > > ....where count_true is the number of questions answered "true" > > SELECT > question_id AS id, value AS val , count(value) AS cnt > FROM > answers_boolean > GROUP BY > question_id,value; > > gives me: > id | val | cnt > ----+-----+----- > 2 | f | 3 > 2 | t | 3 > 3 | f | 2 > 3 | t | 4 > > ...which is sorta what I want. > > I've looked through the docs & archive but haven't found the answer. > > TIA > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
On Sun, 19 May 2002 09:43:04 -0400, Joel Burton wrote: >> set datestyle = 'postgres'; >> show datestyle; >> select current_timestamp::text::timestamp; >> >> In the first one, I get >> timestamptz >> ------------------------------- >> 2002-05-13 10:19:55.158434-03 >> >> In the last one, I get >> ERROR: Bad timestamp external representation 'Mon May 13 10:19:56.671847 >> 2002 BRT' > >Edipo -- > >The second csating doesn't work because PG doesn't recognize the time zone >"BRT" -- despite the fact that it appeared to output it (!). [Full list of >PG timezones at >http://candle.pha.pa.us/main/writings/pgsql/sgml/timezones.html) Ok. I will try some confs next monday. (The server is at work) >What output do you get from > >SET DATESTYLE='iso'; >SELECT current_timestamp; >SET DATESTYLE='postgres'; >SELECT current_timestamp; bench=# SET DATESTYLE='iso'; SET VARIABLE bench=# SELECT current_timestamp; timestamptz -------------------------------2002-05-19 17:37:12.617842-03 (1 row) bench=# SET DATESTYLE='postgres'; SET VARIABLE bench=# SELECT current_timestamp; timestamptz -------------------------------------Sun May 19 17:37:14.144733 2002 BRT (1 row) >And where are you located geographically? Brazil, Rio Grande do Norte, Natal.http://www.mapquest.com/maps/map.adp?zoom=0&mapdata=1FkKZg0N7LmqNpewo16MXL%2faxoMdIexib8Fmh8NEuL%2bgpCpk4% 2fpYs2AOLhVjNTzqyOmiu%2f3aWdA2FLUXHfelRY3Uo7w1EvAuROTwR4nQJWxe6SarHXPaqh5CKM36kni4N2Su351LXh5nGHrS%2fe5ZbI292rCxqIg% 2fLeCXhGtDD%2b5W4jkwM3s9s0S0Q%2bGqQdq4REF4aRET8EBFDAYUuBw7BXdOEd%2fHGCcOs3MTKBAyU2i8noDt%2fI0QeM38CI1A7uqymkh% 2fta59Zq6efUe0QuYcJSb1ekJbNE%2bkdX0UKtAkBeLNWr%2b%2bzLEu0rOny6DTLnLC3BTQItOmpWg3C6X1T%2fKtEQ%3d%3d (sorry for big link above. I'm the red star on map.) []'s Edipo Elder F. de Melo
Dan, Try: SELECT question_id AS id, count( case when value then 1 else 0 end) as count_true, count( case when value then 0 else 1end) as count_false FROM answers_boolean GROUP BY question_id; jll Dan MacNeil wrote: > > I have a table answers_boolean: > > question_id | integer | not null > evaluation_id | integer | not null > value | boolean | > > I'd like output in the form: > > question_id, count_true, count_false > > ....where count_true is the number of questions answered "true" > > SELECT > question_id AS id, value AS val , count(value) AS cnt > FROM > answers_boolean > GROUP BY > question_id,value; > > gives me: > id | val | cnt > ----+-----+----- > 2 | f | 3 > 2 | t | 3 > 3 | f | 2 > 3 | t | 4 > > ...which is sorta what I want. > > I've looked through the docs & archive but haven't found the answer. > > TIA > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
Actually, "count" should be "sum" SELECT question_id AS id, sum( case when value then 1 else 0 end) as count_true, sum( case when value then 0 else 1 end)as count_false FROM answers_boolean GROUP BY question_id; I wrote: > > Dan, > > Try: > > SELECT > question_id AS id, > count( case when value then 1 else 0 end) as count_true, > count( case when value then 0 else 1 end) as count_false > FROM > answers_boolean > GROUP BY > question_id; > > jll > > Dan MacNeil wrote: > > > > I have a table answers_boolean: > > > > question_id | integer | not null > > evaluation_id | integer | not null > > value | boolean | > > > > I'd like output in the form: > > > > question_id, count_true, count_false > > > > ....where count_true is the number of questions answered "true" > > > > SELECT > > question_id AS id, value AS val , count(value) AS cnt > > FROM > > answers_boolean > > GROUP BY > > question_id,value; > > > > gives me: > > id | val | cnt > > ----+-----+----- > > 2 | f | 3 > > 2 | t | 3 > > 3 | f | 2 > > 3 | t | 4 > > > > ...which is sorta what I want. > > > > I've looked through the docs & archive but haven't found the answer. > > > > TIA > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to majordomo@postgresql.org so that your > > message can get through to the mailing list cleanly > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)