pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
if 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