Re: Date operations

On 03/02/2011 09:09 AM, Chetan Suttraway wrote:
> I tried my own setup of queries. Not sure if this fits your
> requirement though.
>
> create table tab1(a int, t timestamp default now());
> create table tab2(a int, t timestamp default now());
> create view view1 as  select tab1.a a1, tab1.t t1,tab2.a a2, tab2.t t2
> from tab1,tab2 where tab1.a = tab2.a ;
>
> --after inserting few record
> pg=# select * from tab1;
>  a |             t
> ---+---------------------------
>  1 | 02-MAR-11 16:44:36.890285
>  2 | 02-JAN-11 16:44:36.890285
>  2 | 02-DEC-10 16:44:36.890285
>  2 | 02-FEB-10 16:44:36.890285
> (4 rows)
>
> pg=# select * from tab2;
>  a |             t
> ---+---------------------------
>  1 | 02-MAR-11 16:44:36.890285
>  2 | 02-JAN-11 16:44:36.890285
>  2 | 02-DEC-10 16:44:36.890285
>  2 | 02-FEB-10 16:44:36.890285
> (4 rows)
>
>
> Now tried below query to find records where the time difference
> between current time and inserted time
> is atleast 3 months. ie values of t1 or t2 which are older than 3
> months wrt current time.
>
> pg=# select a1,t1 from view1 where age(now(),t1) > interval '3 mons'
> order by t1;
>  a1 |            t1
> ----+---------------------------
>   2 | 02-FEB-10 16:44:36.890285
>   2 | 02-FEB-10 16:44:36.890285
>   2 | 02-FEB-10 16:44:36.890285
>   2 | 02-DEC-10 16:44:36.890285
>   2 | 02-DEC-10 16:44:36.890285
>   2 | 02-DEC-10 16:44:36.890285
> (6 rows)
>
>

Once again, thank you very, very much for your insight. It pointed me in
the right direction. What eventually came up was as follows:

-- 1st: creation of a temp table where the relevant data went:

CREATE TEMP TABLE revisar AS (select max(gest_fecha) gest_fecha,
exp_name from (select gest_fecha, exp_name from olx_gst_exp order by
exp_name) AS revisar GROUP BY exp_name);

-- Then a SELECT on that data, looking only for those records more
ancient than 3 months

SELECT gest_fecha, exp_name FROM revisar WHERE age(now(),gest_fecha) >
interval '3 mons' ORDER BY gest_fecha;

Kind regards to all,
Barbara F.

--
Dra. Bárbara M. Figueirido
25 de Mayo 1331
8400 S.C. de Bariloche - RIO NEGRO
Argentina
Tel.-Fax: +54 2944 456252
                   15682745