Обсуждение: if else query help

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?

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 t2where t1.itemid=t2.itemid     -- to link
thetables  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

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
informationtechnology      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

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

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