Thread: querying the age of a row

querying the age of a row

From

"Lonni J Friedman"

Date:

07 June 2007, 18:43:08

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?

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
L. Friedman                                    netllama@gmail.com
LlamaLand                       http://netllama.linux-sxs.org

Re: querying the age of a row

From

Sean Davis

Date:

07 June 2007, 18:49:38

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.

Sean

Re: querying the age of a row

From

"Lonni J Friedman"

Date:

07 June 2007, 18:56:52

On 6/7/07, Sean Davis <sdavis2@mail.nih.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')


--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
L. Friedman                                    netllama@gmail.com
LlamaLand                       http://netllama.linux-sxs.org

Re: querying the age of a row

From

Sean Davis

Date:

07 June 2007, 19:08:38

Lonni J Friedman wrote:
> On 6/7/07, Sean Davis <sdavis2@mail.nih.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:

select now() - interval '24 hours';

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

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

Sean

Re: querying the age of a row

From

"Lonni J Friedman"

Date:

07 June 2007, 19:16:47

On 6/7/07, Sean Davis <sdavis2@mail.nih.gov> wrote:
> Lonni J Friedman wrote:
> > On 6/7/07, Sean Davis <sdavis2@mail.nih.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:
>
> select now() - interval '24 hours';
>
> select '06-06-2007 23:22:11'::timestamp - interval '24 hours';
>
> select now() - interval '24 hours' < '06-06-2007 23:22:11'::timestamp;

All 3 of the above queries do work as expected.

Unfortunately, if I port that over to the actual SQL query, i'm back
to square one again, as all the returned rows are all dated after
midnight (even though its only been about 12 hours since midnight
here, and there are definitely rows before midnight which match the
criteria):

select last_update, subtest, current_status from cudasmoke where
(select now() - interval '24 hours' < to_date(date_created,
'MM-DD-YYYY HH24:MI:SS'))='t' ;

The column with the date/time in it is:
date_created   | character(20) | not null

thanks for your help so far.

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
L. Friedman                                    netllama@gmail.com
LlamaLand                       http://netllama.linux-sxs.org

Re: querying the age of a row

From

"Lonni J Friedman"

Date:

07 June 2007, 19:18:28

On 6/7/07, Sean Davis <sdavis2@mail.nih.gov> wrote:
> Lonni J Friedman wrote:
> > On 6/7/07, Sean Davis <sdavis2@mail.nih.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@gmail.com
LlamaLand                       http://netllama.linux-sxs.org

Re: querying the age of a row

From

Sean Davis

Date:

07 June 2007, 19:24:09

Lonni J Friedman wrote:
> On 6/7/07, Sean Davis <sdavis2@mail.nih.gov> wrote:
>> Lonni J Friedman wrote:
>> > On 6/7/07, Sean Davis <sdavis2@mail.nih.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:
>>
>> select now() - interval '24 hours';
>>
>> select '06-06-2007 23:22:11'::timestamp - interval '24 hours';
>>
>> select now() - interval '24 hours' < '06-06-2007 23:22:11'::timestamp;
>
> All 3 of the above queries do work as expected.
>
> Unfortunately, if I port that over to the actual SQL query, i'm back
> to square one again, as all the returned rows are all dated after
> midnight (even though its only been about 12 hours since midnight
> here, and there are definitely rows before midnight which match the
> criteria):
>
> select last_update, subtest, current_status from cudasmoke where
> (select now() - interval '24 hours' < to_date(date_created,
> 'MM-DD-YYYY HH24:MI:SS'))='t' ;

A date it just that, a date.  It does not include the time.  Try:

select last_update, subtest, current_status from cudasmoke where (select
now() - interval '24 hours' < date_created::timestamp))='t' ;

Sean

Re: querying the age of a row

From

"Lonni J Friedman"

Date:

07 June 2007, 19:43:21

On 6/7/07, Sean Davis <sdavis2@mail.nih.gov> wrote:
> Lonni J Friedman wrote:
> > On 6/7/07, Sean Davis <sdavis2@mail.nih.gov> wrote:
> >> Lonni J Friedman wrote:
> >> > On 6/7/07, Sean Davis <sdavis2@mail.nih.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:
> >>
> >> select now() - interval '24 hours';
> >>
> >> select '06-06-2007 23:22:11'::timestamp - interval '24 hours';
> >>
> >> select now() - interval '24 hours' < '06-06-2007 23:22:11'::timestamp;
> >
> > All 3 of the above queries do work as expected.
> >
> > Unfortunately, if I port that over to the actual SQL query, i'm back
> > to square one again, as all the returned rows are all dated after
> > midnight (even though its only been about 12 hours since midnight
> > here, and there are definitely rows before midnight which match the
> > criteria):
> >
> > select last_update, subtest, current_status from cudasmoke where
> > (select now() - interval '24 hours' < to_date(date_created,
> > 'MM-DD-YYYY HH24:MI:SS'))='t' ;
>
> A date it just that, a date.  It does not include the time.  Try:
>
> select last_update, subtest, current_status from cudasmoke where (select
> now() - interval '24 hours' < date_created::timestamp))='t' ;

excellent!  that is exactly what I needed.  thanks so much for your help!

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
L. Friedman                                    netllama@gmail.com
LlamaLand                       http://netllama.linux-sxs.org