From: | barbara(at)bariloche(dot)com(dot)ar |
---|---|
To: | "Chetan Suttraway" <chetan(dot)suttraway(at)enterprisedb(dot)com> |
Cc: | "Barbara Figueirido" <barbara(at)bariloche(dot)com(dot)ar>, "pgsql-novice" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Date operations |
Date: | 2011-03-02 12:17:30 |
Message-ID: | 20214.201.251.134.237.1299068250.squirrel@webmail.bariloche.com.ar |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
> On Tue, Mar 1, 2011 at 5:47 PM, Barbara Figueirido
> <barbara(at)bariloche(dot)com(dot)ar
>> wrote:
>
>> On 03/01/2011 08:14 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)
>
>
> Google pointed below links, which might be interesting :
> http://archives.postgresql.org/pgsql-sql/2008-01/msg00164.php
> http://wiki.postgresql.org/wiki/Working_with_Dates_and_Times_in_PostgreSQL
>
> Please go through the Date/Time section of the pg documentation for more
> details.
Thank you so much for your trouble. For now I'm away from the server, but
I'll try your ideas as soon as possible. It seems to be exactly what I was
looking for! (I wasn't aware of the 'age' function, shame on me:( )
Kind regards,
Barbara F.
From | Date | Subject | |
---|---|---|---|
Next Message | suresh ramasamy | 2011-03-02 14:31:00 | how are you? |
Previous Message | Chetan Suttraway | 2011-03-02 12:09:58 | Re: Date operations |