Re: Inconsistent results when calculating "age" of db records

On Thu, Jul 26, 2012 at 7:11 AM, Raheem Sarcar <raheemm1@yahoo.com> wrote:
> Hey All,
>
> I'm getting inconsistent results when looking up the age of db entries. For items that were added only minutes ago, I
getanswers like 12 or 13 hours ago. 
> In my troubleshooting attempts I ran the following query directly in postgres
>
>
> select age(pub_date) as days from hackerbiz_story where id=(13);
>
>
> I get the following output -->
> 11:24:47.082  (which is correct)

Are you sure this is the result of "age()" on a record of a few
minutes ago? The function returns the difference from the midnight of
today. For a date "a few minutes ago" it should return something
negative for most of the day. Don't know if it's a matter of timezone,
but I don't think so.

    =# select now(), age(now());
    -[ RECORD 1 ]----------------------
    now | 2012-07-26 10:08:32.298033+01
    age | -10:08:32.298033


> Then I ran the foll query via psycopg2
>
> cur = conn.cursor()
> SQL = "select age(pub_date) as days from hackerbiz_story where id=(%s);"
> cur.execute(SQL, [storyID])   //storyID is passed as argument
>
> From which I get the following result -->
> datetime.timedelta(-1, 45312, 918000).

Which is indeed a negative timestamp. Python representation is funny.

> I then apply the formula "t.seconds/3600" and get the result "12" hrs (which is incorrect).

As Ghislain pointed out, you have left the days out. You can use the
timedelta.total_seconds() method in Python 2.7 IIRC, or use the
"complete" formula, which should be:

    ts.days * 24*60*60 + ts.seconds + ts.microseconds/1e6

> Can someone help me to troubleshoot this further? The db is locally installed and all the code is also running
locally.I'm on the Dhaka timezone. Thanks. 

psycopg should handle correctly negative intervals: this can be
checked by the fact that the number of seconds is the same for two
postgres interval with opposite sign:

>>> cur.execute("select '-11:24:47.082'::interval")
>>> cur.fetchone()[0].total_seconds()
-41087.082

>>> cur.execute("select '11:24:47.082'::interval")
>>> cur.fetchone()[0].total_seconds()
41087.082

However, if you still find problems, please post us:

- an example not depending on data we don't know (e.g. your pub_date,
furthermore the result of 'age()' depends on the current date): a text
literal would be perfect,
- your time zone (output of the "show timezone;" command in postgres).

Cheers,

-- Daniele