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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Sean Davis | 2007-06-07 19:23:55 | Re: querying the age of a row |
Previous Message | Lonni J Friedman | 2007-06-07 19:16:38 | Re: querying the age of a row |