Thread: Date operations

Hello to all!
(Please excuse me if this is too noob and if it has already been
asked/answered, in that case would you please point me towards the
corresponding post or documentation? - TIA)

I have a table where I record the history of some
file-cabinet-paperwork, classifying it according to the persons
affected. There is a table (in fact, it's a view getting its input from
a couple of underlying tables) where I record the date of some action,
the name of the files and the type of action taken.

Now I want to search for those files that haven't been subject to any
action for some lapse of time, say backwards 3 months from now, but I
cannot figure how to phrase the search, since a "SELECT .... WHERE date
< somedate" retrieves all files where some action has been taken before
that period but doesn't exclude those that were being acted upon afterwards.

What I would like, if possible, is something on the line of "SELECT
DISTINCT ON file ..... WHERE date NOT GREATER THAN somedate [ORDER BY
date desc]" but cannot seem to find a way to do it.

I would greatly appreciate any ideas on this subject, 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

<meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">


    On 03/01/2011 08:14 AM, Chetan Suttraway wrote:
    <blockquote
      cite="mid:AANLkTimrq39kZzCk8iNVOEbELWjWYAx11e1kfCkC_oAy@mail.gmail.com"
      type="cite">

        <blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt
          0.8ex; border-left: 1px solid rgb(204, 204, 204);
          padding-left: 1ex;">

          What I would like, if possible, is something on the line of
          "SELECT
          DISTINCT ON file ..... WHERE date NOT GREATER THAN somedate
          [ORDER BY
          date desc]" but cannot seem to find a way to do it.

          I would greatly appreciate any ideas on this subject, kind
          regards to all,
          Barbara F.



          Would it be possible to post the setup in terms of sql
          queries?



    Hello, thanks for your answer.
    The view in question has this definition:
    --
    CREATE VIEW olx_gst_exp AS
        SELECT e.exp_name, e.exp_number, g.gest_fecha, g.gest_tipo FROM
    olx_expedient e, olx_gestiones g WHERE (e.exp_id = g.olx_exp_id)
    ORDER BY e.exp_name;

    whereas the corresponding tables have this setup:

    openlex=# \d olx_expedient

                                            
Table
    "public.olx_expedient"
         Column           
|          
Type                        
    |           
Modifiers                                

----------------+--------------------------+--------------------------------------------------------------------------
     exp_number        |
text                   
              | not 
    null
     exp_ext_id           |
integer                              
| 
     crt_id                   |
integer                              
|

     exp_sec_id           |
integer                      
      |  
     exp_start             | timestamp with time zone | default
now()
     exp_end              | timestamp with time
zone|  
     exp_user_stamp | character varying(64)        | default
    "current_user"()
     exp_time_stamp | timestamp with time zone | default now()
     exp_id                 |
integer               
             | 
    not null default
    nextval(('"olx_expedient_exp_id_seq"'::text)::regclass)
     exp_name          | character varying(64)        |
     exp_nick             |
text                         
        |  
    activo                 |
boolean              
             | 
    default true
    ....

    and

    openlex=# \d olx_gestiones

                                      
Table"public.olx_gestiones" 
         Column          
|          
Type          

|                         
Modifiers 

                         

----------------+--------------------------+--------------------------------------------------------------
     gest_id                |
integer                 | default 
    nextval(('"olx_gestiones_gest_id"'::text)::regclass)
     gest_tipo             |
text       
             |  
     gest_fecha          | date      
              | default 
    ('now'::text)::date
     olx_exp_id           |
integer                 | not null 
     plazo                   |
interval                |  
     fechas_cl            |
date                    
| 
     vencim               |
integer                 |  
     timestamp         | timestamp with time zone | default now()
     fojas                   |
integer                 |  
     exp_user_stamp |
text                    
|default 
    "current_user"()


    I hope this is clear enough, thanks again for your response.
    Barbara F.
    <blockquote
      cite="mid:AANLkTimrq39kZzCk8iNVOEbELWjWYAx11e1kfCkC_oAy@mail.gmail.com"
      type="cite">

         


      Chetan Sutrave
      http://www.enterprisedb.com




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

> On Tue, Mar 1, 2011 at 5:47 PM, Barbara Figueirido
> <barbara@bariloche.com.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.

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