Re: Date operations

From: Barbara Figueirido <barbara(at)bariloche(dot)com(dot)ar>
To: Chetan Suttraway <chetan(dot)suttraway(at)enterprisedb(dot)com>
Cc: pgsql-novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Date operations
Date: 2011-03-05 19:39:57
Message-ID: 4D72918D.8040605@bariloche.com.ar
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Attachment Content-Type Size
barbara.vcf text/x-vcard 224 bytes

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Chetan Suttraway 2011-03-07 12:47:31 Re: Simple table creation
Previous Message David Patricola 2011-03-03 17:35:26 sslv3 connection error