[SQL] Datetime problem
Hello
there,
I'm with a little
trouble with postgresql and date/time conversions:
- select
to_timestamp('2004 10 10 00 00 00', ' MM DD HH MI SS')
the output
is:
- 2004-10-09
23:00:00-03
Anybody can help
me?
[]'s
Eric
Lemes de Godoy Cintra
Analista
de Sistemas Líder Zanthus
Sistemas e Equip. Automação S/A Desenvolvimento
de Software
[EMAIL PROTECTED] Fone:
55(11) 3750-7077
<>
Re: [SQL] Datetime problem
Hello Eric,
Are you looking for something like :
select to_char(timestamp 'now',' MM DD HH MI SS');
or the values in your example below :
select to_char(timestamp '20041010 00:00:00',' MM DD HH MI SS');
Eric Lemes mentioned :
=> Hello there,
=>
=> I'm with a little trouble with postgresql and date/time conversions:
=>
=> - select to_timestamp('2004 10 10 00 00 00', ' MM DD HH MI SS')
=>
=> the output is:
=>
=> - 2004-10-09 23:00:00-03
=>
=> Anybody can help me?
=>
=>
=> []'s
=>
=> Eric Lemes de Godoy Cintra
=> Analista de Sistemas Líder
=> Zanthus Sistemas e Equip. Automação S/A
=> Desenvolvimento de Software
=>
=>
=>
=> [EMAIL PROTECTED]
=> Fone: 55(11) 3750-7077
=>
=>
=>
=> ---
=> Outgoing mail is certified Virus Free.
=> Checked by AVG anti-virus system (http://www.grisoft.com).
=> Version: 6.0.693 / Virus Database: 454 - Release Date: 31/5/2004
=>
pgps87G3ux9sg.pgp
Description: PGP signature
Re: [SQL] Datetime problem
O kyrios Eric Lemes egrapse stis Jun 14, 2004 :
> Hello there,
>
> I'm with a little trouble with postgresql and date/time conversions:
>
> - select to_timestamp('2004 10 10 00 00 00', ' MM DD HH MI SS')
>
> the output is:
>
> - 2004-10-09 23:00:00-03
Just do select to_timestamp('2004 10 10 00 00 00', ' MM DD HH MI
SS')::timestamp
to get rid off timezone info.
>
> Anybody can help me?
>
>
> []'s
>
> Eric Lemes de Godoy Cintra
> Analista de Sistemas Lνder
> Zanthus Sistemas e Equip. Automaηγo S/A
> Desenvolvimento de Software
>
>
>
> [EMAIL PROTECTED]
> Fone: 55(11) 3750-7077
>
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.693 / Virus Database: 454 - Release Date: 31/5/2004
>
--
-Achilleus
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] Datetime problem
"Eric Lemes" <[EMAIL PROTECTED]> writes:
> - select to_timestamp('2004 10 10 00 00 00', ' MM DD HH MI SS')
> the output is:
> - 2004-10-09 23:00:00-03
What PG version is this, on what platform, and what's your current
timezone setting?
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[SQL] max value from join
I have a court program with related tables
citation
citkey varchar(16) NOT NULL PRIMARY KEY,
cdefendant
citkey varchar(16) NOT NULL PRIMARY KEY references citation,
ccourt
citkey varchar(16) NOT NULL PRIMARY KEY references citation,
disposition
citkeyvarchar(16) NOT NULL PRIMARY KEY references citation,
ccontinue
citkeyvarchar(16) NOT NULL references citation,
warrant
citkeyvarchar(16) NOT NULL references citation,
I am trying to extract data related to the last conttinue date using
select
c.citkey, /* c.cdate,
c.badge, c.vioDesc,
b.lname, b.fname,b.mi, b.race, b.dob, b.sex,
d.docket, d.plea, d.fine,d.costs, d.ddate, d.abdocket, d.bond,
p.disDate, p.disDesc, p.disCode, p.amount,
*/
t.contDate,
t.abcontinue,
w.bndType, w.bndAmt
from citation c, cdefendant b, ccourt d, ccontinue t,
disposition p, warrant w
where c.citkey = b.citkey and
b.citkey = d.citkey and
d.citkey = t.citkey and
t.citkey = p.citkey and
p.citkey = w.citkey
group by
c.citkey, c.cdate, c.badge, c.vioDesc,
b.lname, b.fname, b.mi, b.race, b.dob, b.sex,
d.docket, d.plea, d.fine, d.costs, d.ddate, d.abdocket, d.bond,
p.disDate, p.disDesc, p.disCode, p.amount,
t.abcontinue, t.contDate,
w.bndType, w.bndAmt
having max(t.contDate) = t.contDate
order by c.citkey
I cannot seem to get unique rows with only the max contDate??
i.e.
citkey | contdate | abcontinue | bndtype | bndamt
+-++-+-
991164031 | 06/07/2000 | 6 | Bond|0.00
991164031 | 07/19/2000 | 6 | Bond|0.00
thanks
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
RES: [SQL] Datetime problem
Hello,
- PostgreSQL 7.3.2 on i386-redhat-linux GCC 3.2.2
- Timezone: Brazil (GMT-3, I think).
I think my problem is with the time zone. Using a SET TIME ZONE GMT, the
result is Ok. But I don't know how to work with time zones correctly.
When I send a date to to_timestamp, pgsql thinks this date is in GMT?
Thanks for all your help.
[]'s
Eric Lemes
> -Mensagem original-
> De: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] nome de Tom Lane
> Enviada em: segunda-feira, 14 de junho de 2004 11:56
> Para: Eric Lemes
> Cc: [EMAIL PROTECTED]
> Assunto: Re: [SQL] Datetime problem
>
>
> "Eric Lemes" <[EMAIL PROTECTED]> writes:
> > - select to_timestamp('2004 10 10 00 00 00', ' MM DD HH MI SS')
> > the output is:
> > - 2004-10-09 23:00:00-03
>
> What PG version is this, on what platform, and what's your current
> timezone setting?
>
> regards, tom lane
>
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings
> ---
> Incoming mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.693 / Virus Database: 454 - Release Date: 31/5/2004
>
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.693 / Virus Database: 454 - Release Date: 31/5/2004
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
[SQL] query with =ALL
Hi all, i have an strange result here, i'm using 7.4.2 on redhat 8 i have a query like this SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor, CPA.cpa_fechavencimiento FROM rec_m_cuadropagos CPA, rec_m_rubro RUB WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo AND CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo = '2004-2005' AND CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND CPA.cur_paralelo = ALL (SELECT cur_paralelo FROM aca_t_curso WHERE ent_codigo = 1 AND sec_codigo = 1 AND ani_codigo = '2004-2005' AND cic_codigo = 1 AND esp_codigo = 0 AND cur_codigo = 1) that bring no values but if i do this (the same query without the =ALL part) SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor, CPA.cpa_fechavencimiento FROM rec_m_cuadropagos CPA, rec_m_rubro RUB WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo AND CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo = '2004-2005' AND CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 get a resultset, one would think that the =ALL subquery is filtering out that rows but the cur_paralelo has same values in both sides select distinct cur_paralelo from rec_m_cuadropagos WHERE ent_codigo = 1 AND sec_codigo = 1 AND ani_codigo = '2004-2005' AND cic_codigo = 1 AND esp_codigo = 0 AND cur_codigo = 1 intersect SELECT cur_paralelo FROM aca_t_curso WHERE ent_codigo = 1 AND sec_codigo = 1 AND ani_codigo = '2004-2005' AND cic_codigo = 1 AND esp_codigo = 0 AND cur_codigo = 1 this query proves that its result is 'A', 'B', 'C' any idea, is something wrong in my thinking? thanx in advance, Jaime Casanova _ The new MSN 8: advanced junk mail protection and 2 months FREE* http://join.msn.com/?page=features/junkmail ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] a query with = ALL
Hi all, some more info a think can be useful to probe there is somting wrong. I pointed that the query select cur_paralelo from rec_m_cuadropagos WHERE ent_codigo = 1 AND sec_codigo = 1 AND ani_codigo = '2004-2005' AND cic_codigo = 1 AND esp_codigo = 0 AND cur_codigo = 1 intersect SELECT cur_paralelo FROM aca_t_curso WHERE ent_codigo = 1 AND sec_codigo = 1 AND ani_codigo = '2004-2005' AND cic_codigo = 1 AND esp_codigo = 0 AND cur_codigo = 1 brings 'A ', 'B ', 'C ' well when i cHange the real query SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor, CPA.cpa_fechavencimiento FROM rec_m_cuadropagos CPA, rec_m_rubro RUB WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo AND CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo = '2004-2005' AND CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND CPA.cur_paralelo = 'A ' then it brings the result that it would but when i do the same with =ALL it doesn't work thanx in advance, Jaime Casanova _ MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. http://join.msn.com/?page=features/virus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: RES: [SQL] Datetime problem
On Mon, Jun 14, 2004 at 01:20:14PM -0300, Eric Lemes wrote:
> Hello,
>
> - PostgreSQL 7.3.2 on i386-redhat-linux GCC 3.2.2
> - Timezone: Brazil (GMT-3, I think).
What's about daylight saving time for you?
I'm almost sure the DST boundary is near the date in your example.
However, with 7.3.4 on FreeBSD I get:
fduch=# SHOW TimeZone ;
TimeZone
---
Europe/Moscow
(1 row)
fduch=# SELECT to_timestamp('2004 10 31 00 00 00', ' MM DD HH MI SS');
to_timestamp
2004-10-31 00:00:00+04
(1 row)
fduch=# SELECT to_timestamp('2004 11 01 00 00 00', ' MM DD HH MI SS');
to_timestamp
2004-11-01 00:00:00+03
(1 row)
So both timestamps before and after boundary are parsed well for me.
> I think my problem is with the time zone. Using a SET TIME ZONE GMT, the
> result is Ok. But I don't know how to work with time zones correctly.
>
> When I send a date to to_timestamp, pgsql thinks this date is in GMT?
Hmm, 7.3 and 7.4 docs say that it returns timestamp (WITHOUT time zone
is default since 7.3 IIRC), but in fact it accepts and returns timestamp
WITH time zone. This is probably a documentation bug...
--
Fduch M. Pravking
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] query with =ALL
On Mon, 14 Jun 2004, Jaime Casanova wrote: > i have an strange result here, i'm using 7.4.2 on redhat 8 > > i have a query like this > > SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor, > CPA.cpa_fechavencimiento > FROM rec_m_cuadropagos CPA, rec_m_rubro RUB > WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo > AND > CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo = > '2004-2005' AND > CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND > CPA.cur_paralelo = ALL (SELECT cur_paralelo FROM aca_t_curso WHERE > ent_codigo = 1 AND > > sec_codigo = 1 AND > > ani_codigo = '2004-2005' AND > > cic_codigo = 1 AND > > esp_codigo = 0 AND > > cur_codigo = 1) > > that bring no values but if i do this (the same query without the =ALL part) > > SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor, > CPA.cpa_fechavencimiento > FROM rec_m_cuadropagos CPA, rec_m_rubro RUB > WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo > AND > CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo = > '2004-2005' AND > CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 > > get a resultset, one would think that the =ALL subquery is filtering out > that rows but the cur_paralelo has same values in both sides > > select distinct cur_paralelo from rec_m_cuadropagos WHERE ent_codigo = 1 AND > > sec_codigo = 1 AND > > ani_codigo = '2004-2005' AND > > cic_codigo = 1 AND > > esp_codigo = 0 AND > > cur_codigo = 1 > intersect > SELECT cur_paralelo FROM aca_t_curso WHERE ent_codigo = 1 AND > > sec_codigo = 1 AND > > ani_codigo = '2004-2005' AND > > cic_codigo = 1 AND > > esp_codigo = 0 AND > > cur_codigo = 1 > > this query proves that its result is 'A', 'B', 'C' > > any idea, is something wrong in my thinking? AFAIK, The query isn't check all values that meet the other criteria against all values of the subquery but instead check the value from each row that meets the other criteria against all the values in the subquery. If the values in the subquery are distinct, it's not going to return true. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] query with =ALL
Jaime Casanova wrote: Hi all, i have an strange result here, i'm using 7.4.2 on redhat 8 i have a query like this [snip] this query proves that its result is 'A', 'B', 'C' any idea, is something wrong in my thinking? Do you have any null values involved? That might well interfere (though I admit I haven't looked in detail) -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] query with =ALL
Jaime Casanova wrote: Hi all, i have an strange result here, i'm using 7.4.2 on redhat 8 i have a query like this [snip] this query proves that its result is 'A', 'B', 'C' any idea, is something wrong in my thinking? Do you have any null values involved? That might well interfere (though I admit I haven't looked in >detail) There are no null values here, i'm sure of that because all the fields in the subquery included the one i'm returning are part of the PK -- Richard Huxton Archonet Ltd and when i do the selects individually i got the correct results SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor, CPA.cpa_fechavencimiento FROM rec_m_cuadropagos CPA, rec_m_rubro RUB WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo AND CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo = '2004-2005' AND CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND CPA.cur_paralelo = 'A ' 1 Matrícula 100 05/10/2005 SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor, CPA.cpa_fechavencimiento FROM rec_m_cuadropagos CPA, rec_m_rubro RUB WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo AND CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo = '2004-2005' AND CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND CPA.cur_paralelo = 'B ' 1 Matrícula 100 05/10/2005 SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor, CPA.cpa_fechavencimiento FROM rec_m_cuadropagos CPA, rec_m_rubro RUB WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo AND CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo = '2004-2005' AND CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND CPA.cur_paralelo = 'C ' 1 Matrícula 100 05/10/2005 _ The new MSN 8: advanced junk mail protection and 2 months FREE* http://join.msn.com/?page=features/junkmail ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] query with =ALL
On Mon, 14 Jun 2004, Jaime Casanova wrote: > i have an strange result here, i'm using 7.4.2 on redhat 8 > > i have a query like this > > SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor, > CPA.cpa_fechavencimiento > FROM rec_m_cuadropagos CPA, rec_m_rubro RUB > WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo > AND > CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo = > '2004-2005' AND > CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND > CPA.cur_paralelo = ALL (SELECT cur_paralelo FROM aca_t_curso WHERE > ent_codigo = 1 AND > > sec_codigo = 1 AND > > ani_codigo = '2004-2005' AND > > cic_codigo = 1 AND > > esp_codigo = 0 AND > > cur_codigo = 1) > > that bring no values but if i do this (the same query without the =ALL part) > > SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor, > CPA.cpa_fechavencimiento > FROM rec_m_cuadropagos CPA, rec_m_rubro RUB > WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo > AND > CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo = > '2004-2005' AND > CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 > > get a resultset, one would think that the =ALL subquery is filtering out > that rows but the cur_paralelo has same values in both sides > > select distinct cur_paralelo from rec_m_cuadropagos WHERE ent_codigo = 1 AND > > sec_codigo = 1 AND > > ani_codigo = '2004-2005' AND > > cic_codigo = 1 AND > > esp_codigo = 0 AND > > cur_codigo = 1 > intersect > SELECT cur_paralelo FROM aca_t_curso WHERE ent_codigo = 1 AND > > sec_codigo = 1 AND > > ani_codigo = '2004-2005' AND > > cic_codigo = 1 AND > > esp_codigo = 0 AND > > cur_codigo = 1 > > this query proves that its result is 'A', 'B', 'C' > > any idea, is something wrong in my thinking? AFAIK, The query isn't check all values that meet the other criteria against all values of the subquery but instead check the value from each row that meets the other criteria against all the values in the subquery. If the values in the subquery are distinct, it's not going to return true. Is there another solution instead the one i'm using can you help me in that? _ Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] query with =ALL
On Mon, 14 Jun 2004, Jaime Casanova wrote: > On Mon, 14 Jun 2004, Jaime Casanova wrote: > > > i have an strange result here, i'm using 7.4.2 on redhat 8 > > > > i have a query like this > > > > SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor, > > CPA.cpa_fechavencimiento > > FROM rec_m_cuadropagos CPA, rec_m_rubro RUB > > WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo > > AND > > CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo = > > '2004-2005' AND > > CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 > AND > > CPA.cur_paralelo = ALL (SELECT cur_paralelo FROM aca_t_curso WHERE > > ent_codigo = 1 AND > > > > sec_codigo = 1 AND > > > > ani_codigo = '2004-2005' AND > > > > cic_codigo = 1 AND > > > > esp_codigo = 0 AND > > > > cur_codigo = 1) > > > > that bring no values but if i do this (the same query without the =ALL > part) > AFAIK, The query isn't check all values that meet the other criteria > against all values of the subquery but instead check the value from each > row that meets the other criteria against all the values in the subquery. > If the values in the subquery are distinct, it's not going to return true. > Is there another solution instead the one i'm using can you help me in that? What precisely are you looking for as your output? Only those combinations of the four output attributes that meet the other criteria and that have exactly the same set as in the subselect? Hmm, I'm not sure how to do that off hand, will think about it, but hopefully someone will have an answer. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] max value from join
On Mon, Jun 14, 2004 at 11:02:06AM -0500, hook wrote: > I have a court program with related tables > I am trying to extract data related to the last conttinue date using > select >c.citkey, /* c.cdate, >c.badge, c.vioDesc, >b.lname, b.fname,b.mi, b.race, b.dob, b.sex, >d.docket, d.plea, d.fine,d.costs, d.ddate, d.abdocket, d.bond, >p.disDate, p.disDesc, p.disCode, p.amount, >*/ >t.contDate, >t.abcontinue, >w.bndType, w.bndAmt >from citation c, cdefendant b, ccourt d, ccontinue t, > disposition p, warrant w >where c.citkey = b.citkey and > b.citkey = d.citkey and > d.citkey = t.citkey and > t.citkey = p.citkey and > p.citkey = w.citkey >group by > c.citkey, c.cdate, c.badge, c.vioDesc, > b.lname, b.fname, b.mi, b.race, b.dob, b.sex, > d.docket, d.plea, d.fine, d.costs, d.ddate, d.abdocket, d.bond, > p.disDate, p.disDesc, p.disCode, p.amount, > t.abcontinue, t.contDate, > w.bndType, w.bndAmt >having max(t.contDate) = t.contDate >order by c.citkey > > > I cannot seem to get unique rows with only the max contDate?? A subselect may be useful to you: SELECT c.citkey, t.contDate -- other fields... FROM citation c, ccontinue t -- other tables... WHERE c.citkey = t.citkey-- other join clauses... AND t.contDate = ( SELECT max(contDate) FROM ccontinue ) -- no need for GROUP BY / HAVING ORDER BY c.citkey Richard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] a query with = ALL
"Jaime Casanova" <[EMAIL PROTECTED]> writes: > brings 'A ', 'B ', 'C ' well when i cHange the real query Given that you spelled it like that, I wonder whether you aren't confused about the behavior of cross-data-type comparisons. If one column is char(n) and the other is text or varchar(n) then you may get results you didn't expect. These types have different ideas about whether trailing blanks are significant or not. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: RES: [SQL] Datetime problem
"Eric Lemes" <[EMAIL PROTECTED]> writes: > - PostgreSQL 7.3.2 on i386-redhat-linux GCC 3.2.2 > - Timezone: Brazil (GMT-3, I think). Ah, and 2004-10-10 is a daylight savings transition day where you live, right? (Or at least the obsolete timezone file you have thinks so...) So local midnight on that day doesn't really exist: you go from 11:59:59 standard time to 01:00:00 summer time. PG 7.3 and before got this boundary condition wrong in some cases, but I think 7.4 gets it right. The rule we use now is to interpret invalid or ambiguous times as local standard time (hence, this input means midnight standard time), which is what to_timestamp is doing. However, on reverse conversion you'll get the normalized form of the time, which is 01:00:00 summer time. I think what is happening in 7.3 is that the input is interpreted as midnight summer time, which would reverse-convert as 11:00 PM standard time... regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: RES: [SQL] Datetime problem
"Alexander M. Pravking" <[EMAIL PROTECTED]> writes: > Hmm, 7.3 and 7.4 docs say that it returns timestamp (WITHOUT time zone > is default since 7.3 IIRC), but in fact it accepts and returns timestamp > WITH time zone. This is probably a documentation bug... Yeah, it is. Fixed in CVS tip --- thanks for pointing it out. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] query with =ALL
> On Mon, 14 Jun 2004, Jaime Casanova wrote: > > > On Mon, 14 Jun 2004, Jaime Casanova wrote: > > > > > i have an strange result here, i'm using 7.4.2 on redhat 8 > > > > > > i have a query like this > > > > > > SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor, > > > CPA.cpa_fechavencimiento > > > FROM rec_m_cuadropagos CPA, rec_m_rubro RUB > > > WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo > > > AND > > > CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo = > > > '2004-2005' AND > > > CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 > > AND > > > CPA.cur_paralelo = ALL (SELECT cur_paralelo FROM aca_t_curso WHERE > > > ent_codigo = 1 AND > > > > > > sec_codigo = 1 AND > > > > > > ani_codigo = '2004-2005' AND > > > > > > cic_codigo = 1 AND > > > > > > esp_codigo = 0 AND > > > > > > cur_codigo = 1) > > > > > > that bring no values but if i do this (the same query without the =ALL > > part) > > AFAIK, The query isn't check all values that meet the other criteria > > against all values of the subquery but instead check the value from each > > row that meets the other criteria against all the values in the subquery. > > If the values in the subquery are distinct, it's not going to return true. > > Is there another solution instead the one i'm using can you help me in that? > What precisely are you looking for as your output? Only those > combinations of the four output attributes that meet the other criteria > and that have exactly the same set as in the subselect? Hmm, I'm not sure > how to do that off hand, will think about it, but hopefully someone will > have an answer. hi all, What i'm trying to do is to obtain the same result that i should obtain if i do: SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor, CPA.cpa_fechavencimiento FROM rec_m_cuadropagos CPA, rec_m_rubro RUB WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo AND CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo = '2004-2005' AND CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND CPA.cur_paralelo = 'A ' intersect SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor, CPA.cpa_fechavencimiento FROM rec_m_cuadropagos CPA, rec_m_rubro RUB WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo AND CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo = '2004-2005' AND CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND CPA.cur_paralelo = 'B ' intersect SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor, CPA.cpa_fechavencimiento FROM rec_m_cuadropagos CPA, rec_m_rubro RUB WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo AND CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo = '2004-2005' AND CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND CPA.cur_paralelo = 'C ' but i cannot use the intersect solution becuase i don't know how many values of cur_paralelo could be involved becuase that depends on user input. maybe someone can help. thanx in advance, Jaime Casanova _ The new MSN 8: advanced junk mail protection and 2 months FREE* http://join.msn.com/?page=features/junkmail ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] a query with = ALL
"Jaime Casanova" <[EMAIL PROTECTED]> writes: brings 'A ', 'B ', 'C ' well when i cHange the real query Given that you spelled it like that, I wonder whether you aren't confused about the behavior of cross-data-type comparisons. If one column is char(n) and the other is text or varchar(n) then you may get results you didn't expect. These types have different ideas about whether trailing blanks are significant or not. both CPA.cur_paralelo and the cur_paralelo obtained in the subquery are char(2) i assume there are no problem, i'm wrong? thanx in advance, Jaime Casanova _ MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. http://join.msn.com/?page=features/virus ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] query with =ALL
"Jaime Casanova" <[EMAIL PROTECTED]> writes: > AND > CPA.cur_paralelo = ALL (SELECT cur_paralelo FROM aca_t_curso ...) Wait a second ... we are all overthinking the problem. The subselect returns three *different* values. It is not possible for any CPA.cur_paralelo values to be simultaneously equal to all three. So this test certainly fails at every row of CPA. Perhaps you meant "= ANY"? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Scalar subqueries
__sorry for posting it second time__ Not sure what am I missing. I really appreciate if anyone could point it out to me. I've a logs table that has both sign-in and sign-out records which are differentiated by action flag. Records with action flag = (1,2) => sign-in records and action flag = (3,4,5,6,7) => sign-out records. playfield=# select * from logs; log_id | log_creation_date | account_id | user_id | service | action | mac +-++-+-++--- -- 1 | 2004-04-29 10:48:36 | Robert | 3 | 5 | 2 | 00-00-00-00 3 | 2004-04-29 10:53:44 | Robert | 3 | 5 | 3 | 00-00-00-00 5 | 2004-04-29 11:11:35 | Robert | 3 | 5 | 1 | 00-00-00-00 1003 | 2004-05-03 15:18:53 | Robert | 3 | 5 | 5 | 00-00-00-00 1004 | 2004-05-03 15:19:50 | Robert | 8 | 5 | 1 | 00-00-00-00 (5 rows) All I'm trying to do is print signin id and corresponding sign-out id's in single row. Select I wrote : select log_id as signin_id, (select foo.log_id from (select foo1.log_id from logs as foo1 where foo1.action in (3,4,5,6,7) and l.log_id > foo1.log_id order by foo1.account_id, foo1.user_id, foo1.mac, foo1.log_creation_date) as foo limit 1) as signout_id from logs as l where action in (1,2); Gives... signin_id | signout_id ---+ 1 | 5 | 3 1004 | 3 (3 rows) Expected output : signin_id | signout_id ---+ 1 | 3 5 | 1003 1004 | (3 rows) Thanks, Stalin ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] query with =ALL
Hi all, > AND > CPA.cur_paralelo = ALL (SELECT cur_paralelo FROM aca_t_curso ...) Wait a second ... we are all overthinking the problem. The subselect returns three *different* values. It is not possible for any CPA.cur_paralelo values to be simultaneously equal to all three. So this test certainly fails at every row of CPA. Perhaps you meant "= ANY"? Not really becuase ANY has the same efect that IN and what i want is all the results that are equal in all the rows in any of the cur_paralelo values. But must be in all the cur_paralelo or nothing. thanx in advance, Jaime Casanova _ MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. http://join.msn.com/?page=features/virus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] query with =ALL
"Jaime Casanova" <[EMAIL PROTECTED]> writes: >> Perhaps you meant "= ANY"? > Not really becuase ANY has the same efect that IN and what i want is all the > results that are equal in all the rows in any of the cur_paralelo values. > But must be in all the cur_paralelo or nothing. You're not expressing yourself clearly, because as far as I can understand you there are guaranteed to be no such results. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] query with =ALL
You're not expressing yourself clearly, because as far as I can understand you there are guaranteed to be no such results. regards, tom lane ok, the output i want is equivalent to: SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor, CPA.cpa_fechavencimiento FROM rec_m_cuadropagos CPA, rec_m_rubro RUB WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo AND CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo = '2004-2005' AND CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND CPA.cur_paralelo = 'A ' intersect SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor, CPA.cpa_fechavencimiento FROM rec_m_cuadropagos CPA, rec_m_rubro RUB WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo AND CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo = '2004-2005' AND CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND CPA.cur_paralelo = 'B ' intersect SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor, CPA.cpa_fechavencimiento FROM rec_m_cuadropagos CPA, rec_m_rubro RUB WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo AND CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo = '2004-2005' AND CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND CPA.cur_paralelo = 'C ' but i cannot use that solution because the columns in wich i want to so such type of query are various and depends on user input _ Help STOP SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] query with =ALL
On Mon, 14 Jun 2004, Tom Lane wrote: > "Jaime Casanova" <[EMAIL PROTECTED]> writes: > > AND > > CPA.cur_paralelo = ALL (SELECT cur_paralelo FROM aca_t_curso ...) > > Wait a second ... we are all overthinking the problem. The subselect > returns three *different* values. It is not possible for any > CPA.cur_paralelo values to be simultaneously equal to all three. > So this test certainly fails at every row of CPA. > > Perhaps you meant "= ANY"? I think what he wants is to output distinct sets of output columns where for each of these sets of output columns there exists a set of rows that meet the other conditions and that the set of cur_paralelo values for that set of rows is the same as (or perhaps is a superset of) the set of values returned from the subselect. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Scalar subqueries
On Mon, Jun 14, 2004 at 12:38:44PM -0700, Subbiah, Stalin wrote: > > I've a logs table that has both sign-in and sign-out records which are > differentiated by action flag. Records with action flag = (1,2) => sign-in > records and action flag = (3,4,5,6,7) => sign-out records. > All I'm trying to do is print signin id and corresponding sign-out id's in > single row. You're writing one more level of sub-select than you actually need. SELECT inlog.log_id AS signin_id, ( SELECT MIN(outlog.log_id) FROM logs outlog WHERE outlog.log_id > inlog.log_id AND action IN (3, 4, 5, 6, 7) ) AS signout_id FROM logs inlog WHERE inlog.action IN (1, 2); Assuming you want to match signins and signouts by the same account to the same service, or whatever, you can add in clauses like AND outlog.account_id = inlog.account_id or whatever else you like, to the inner select. Richard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Prepare Statement
Hi, I have a question about performance, in SQL commands: there is a prepare/execute command, document says it will improve the performance while repeatly execute a statement. In java.sql: there is a PreparedStatement object, which can store precompiled SQL statement, document says it can improve the performance also. If I use java jdbc to connect postgresql database, which one I should use? Can I use both? Thanks. Jie Liang ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
