Skip site navigation (1) Skip section navigation (2)

Re: querying the age of a row

From: "Lonni J Friedman" <netllama(at)gmail(dot)com>
To: "Sean Davis" <sdavis2(at)mail(dot)nih(dot)gov>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: querying the age of a row
Date: 2007-06-07 19:18:18
Message-ID: 7c1574a90706071218i252d42bejd4caa65e4dda0b9a@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
On 6/7/07, Sean Davis <sdavis2(at)mail(dot)nih(dot)gov> wrote:
> Lonni J Friedman wrote:
> > On 6/7/07, Sean Davis <sdavis2(at)mail(dot)nih(dot)gov> wrote:
> >> Lonni J Friedman wrote:
> >> > Greetings,
> >> > I've got a PostgreSQL-8.1.x database on a Linux box.  I have a need to
> >> > determine which rows in a specific table are less than 24 hours old.
> >> > I've tried (and failed) to do this with the age() function.  From what
> >> > I can tell, age() only has granularity down to days, and seems to
> >> > assume that anything matching today's date is less than 24 hours old,
> >> > even if there are rows from yesterday's date that existed less than 24
> >> > hours ago.
> >> >
> >> > I've googled on this off and on for a few days, and have come up dry.
> >> > Someone on a different list suggested that I add a column that get
> >> > now() each time a new row is inserted, but that unfortunately won't
> >> > help me for all the pre-existing rows in this database.
> >> >
> >> > At any rate, is there a reliable way of querying a table for rows
> >> > which have existed for a specific period of time?
> >> >
> >>
> >> So your table has no date or time stored in it at all?  If not, then you
> >> cannot do the query that you are suggesting.
> >
> > It does have a column that is populated with a date/timestamp from the
> > following query:
> > select to_char(current_timestamp, 'MM-DD-YYYY HH24:MI:SS')
>
> So, the column is a text column?  Try these to see if it helps:

oh, and here's the output from your queries:

>
> select now() - interval '24 hours';

nightly=# select now() -interval '24 hours';
?column?
-------------------------------
 2007-06-06 12:17:27.860958-07
(1 row)

>
> select '06-06-2007 23:22:11'::timestamp - interval '24 hours';

nightly=# select '06-06-2007 23:22:11'::timestamp - interval '24 hours';
      ?column?
---------------------
 2007-06-05 23:22:11
(1 row)

>
> select now() - interval '24 hours' < '06-06-2007 23:22:11'::timestamp;

nightly=# select now() - interval '24 hours' < '06-06-2007 23:22:11'::timestamp;
 ?column?
----------
 t
(1 row)


-- 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
L. Friedman                                    netllama(at)gmail(dot)com
LlamaLand                       http://netllama.linux-sxs.org

In response to

pgsql-novice by date

Next:From: Sean DavisDate: 2007-06-07 19:23:55
Subject: Re: querying the age of a row
Previous:From: Lonni J FriedmanDate: 2007-06-07 19:16:38
Subject: Re: querying the age of a row

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group