pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedif else query help
5+ messages / 5 participants
[nested] [flat]
* if else query help
@ 2000-10-12 14:14 Brian C. Doyle <bcdoyle@mindspring.com>
2000-10-12 14:56 ` Re: if else query help Jean-Christophe Boggio <cat@thefreecat.org>
0 siblings, 1 reply; 5+ messages in thread
From: Brian C. Doyle @ 2000-10-12 14:14 UTC (permalink / raw)
To: pgsql-sql
Hello all,
I need to write a query that will pull information from table2 if
information in table1 is older then xdate.
My laymen example:
SELECT table2.date, count(table2.name) as count
WHERE table1.startdate > 2 weeks
AND table2.submitdate > 2 weeks
;
So i Guess my real questions is how do I determine the age of an entry to
another table?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: if else query help
2000-10-12 14:14 if else query help Brian C. Doyle <bcdoyle@mindspring.com>
@ 2000-10-12 14:56 ` Jean-Christophe Boggio <cat@thefreecat.org>
2000-10-12 15:35 ` Re: if else query help Josh Berkus <josh@agliodbs.com>
2000-10-12 18:37 ` Re: if else query help John McKown <joarmc@swbell.net>
0 siblings, 2 replies; 5+ messages in thread
From: Jean-Christophe Boggio @ 2000-10-12 14:56 UTC (permalink / raw)
To: Brian C. Doyle <bcdoyle@mindspring.com>; +Cc: pgsql-sql
hi Brian,
BCD> I need to write a query that will pull information from table2 if
BCD> information in table1 is older then xdate.
BCD> My laymen example:
BCD> SELECT table2.date, count(table2.name) as count
BCD> WHERE table1.startdate > 2 weeks
BCD> AND table2.submitdate > 2 weeks
BCD> ;
BCD> So i Guess my real questions is how do I determine the age of an entry to
BCD> another table?
Try this :
Select table2.date,count(table2.name) as count
from table1 as t1, table2 as t2
where t1.itemid=t2.itemid -- to link the tables
and t1.startdate>now()-14
and t2.submitdate>now()-14;
Someone corrects me if I'm wrong, I come from the Oracle world...
Dates (or I should say TimeStamps) are stored as floating point values
: the integer part is the number of days since a certain date
(epoch or 01/01/1970 on unix-based databases) and the fractionnal part is the
portion of the day (although I don't know --yet-- how to convert
date2-date1 to an integer, trunc does not work).
HTH
--
Jean-Christophe Boggio
cat@thefreecat.org
Independant Consultant and Developer
Delphi, Linux, Oracle, Perl
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: if else query help
2000-10-12 14:14 if else query help Brian C. Doyle <bcdoyle@mindspring.com>
2000-10-12 14:56 ` Re: if else query help Jean-Christophe Boggio <cat@thefreecat.org>
@ 2000-10-12 15:35 ` Josh Berkus <josh@agliodbs.com>
2000-10-12 17:36 ` Re: if else query help Jie Liang <jliang@ipinc.com>
1 sibling, 1 reply; 5+ messages in thread
From: Josh Berkus @ 2000-10-12 15:35 UTC (permalink / raw)
To: pgsql-sql
Brian, Jean-Christophe,
>Someone corrects me if I'm wrong, I come from the Oracle world...
>
> Dates (or I should say TimeStamps) are stored as floating point values
> : the integer part is the number of days since a certain date
> (epoch or 01/01/1970 on unix-based databases) and the fractionnal part is the
> portion of the day (although I don't know --yet-- how to convert
> date2-date1 to an integer, trunc does not work).
You're doing this the hard way. One of Postgres' best features is its
rich collection of date-manipulation functions. Please see:
... Hmmmm. The online docs appear to be down. When they're back up,
please check the sections on: Date/Time data types, and Date/Time
manipulation functions.
-Josh Berkus
P.S. Brian, a general tutorial on writing SQL, such as O'Reilly's
soon-to-be released SQL book, might help you a great deal.
--
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: if else query help
2000-10-12 14:14 if else query help Brian C. Doyle <bcdoyle@mindspring.com>
2000-10-12 14:56 ` Re: if else query help Jean-Christophe Boggio <cat@thefreecat.org>
2000-10-12 15:35 ` Re: if else query help Josh Berkus <josh@agliodbs.com>
@ 2000-10-12 17:36 ` Jie Liang <jliang@ipinc.com>
0 siblings, 0 replies; 5+ messages in thread
From: Jie Liang @ 2000-10-12 17:36 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql
Hey, there,
This is very interesting.
I have similar problem:
I want drop some junky table in my database, how can I detect a table when last
time it is used.
I try to say that I want to know how long this table has NOT been used at all.
I don't which system table holds this statistics.
Josh Berkus wrote:
> Brian, Jean-Christophe,
>
> >Someone corrects me if I'm wrong, I come from the Oracle world...
> >
> > Dates (or I should say TimeStamps) are stored as floating point values
> > : the integer part is the number of days since a certain date
> > (epoch or 01/01/1970 on unix-based databases) and the fractionnal part is the
> > portion of the day (although I don't know --yet-- how to convert
> > date2-date1 to an integer, trunc does not work).
>
> You're doing this the hard way. One of Postgres' best features is its
> rich collection of date-manipulation functions. Please see:
>
> ... Hmmmm. The online docs appear to be down. When they're back up,
> please check the sections on: Date/Time data types, and Date/Time
> manipulation functions.
>
> -Josh Berkus
>
> P.S. Brian, a general tutorial on writing SQL, such as O'Reilly's
> soon-to-be released SQL book, might help you a great deal.
>
> --
> ______AGLIO DATABASE SOLUTIONS___________________________
> Josh Berkus
> Complete information technology josh@agliodbs.com
> and data management solutions (415) 436-9166
> for law firms, small businesses fax 436-0137
> and non-profit organizations. pager 338-4078
> San Francisco
--
Jie LIANG
Internet Products Inc.
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
jliang@ipinc.com
www.ipinc.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: if else query help
2000-10-12 14:14 if else query help Brian C. Doyle <bcdoyle@mindspring.com>
2000-10-12 14:56 ` Re: if else query help Jean-Christophe Boggio <cat@thefreecat.org>
@ 2000-10-12 18:37 ` John McKown <joarmc@swbell.net>
1 sibling, 0 replies; 5+ messages in thread
From: John McKown @ 2000-10-12 18:37 UTC (permalink / raw)
To: Jean-Christophe Boggio <cat@thefreecat.org>; +Cc: Brian C. Doyle <bcdoyle@mindspring.com>; pgsql-sql
On Thu, 12 Oct 2000, Jean-Christophe Boggio wrote:
<snip>
> portion of the day (although I don't know --yet-- how to convert
> date2-date1 to an integer, trunc does not work).
reltime(date2-date1)::int
Will subtract date1 from date2, then cast it to an integer.
John
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2000-10-12 18:37 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-10-12 14:14 if else query help Brian C. Doyle <bcdoyle@mindspring.com>
2000-10-12 14:56 ` Jean-Christophe Boggio <cat@thefreecat.org>
2000-10-12 15:35 ` Josh Berkus <josh@agliodbs.com>
2000-10-12 17:36 ` Jie Liang <jliang@ipinc.com>
2000-10-12 18:37 ` John McKown <joarmc@swbell.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox