Re: Date operations

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:
>
>
>
>> 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.
>
>
> --
> Dra. Bárbara M. Figueirido
> 25 de Mayo 1331
> 8400 S.C. de Bariloche - RIO NEGRO
> Argentina
> Tel.-Fax: +54 2944 456252
> 15682745
>
>
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.

--
Chetan Sutrave
Senior Software Engineer
EnterpriseDB Corporation
The Enterprise Postgres Company
Phone: +91.20.30589523

Website: www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of the
individual or entity to whom it is addressed. This message contains
information from EnterpriseDB Corporation that may be privileged,
confidential, or exempt from disclosure under applicable law. If you are not
the intended recipient or authorized to receive this for the intended
recipient, any use, dissemination, distribution, retention, archiving, or
copying of this communication is strictly prohibited. If you have received
this e-mail in error, please notify the sender immediately by reply e-mail
and delete this message.