Thread: Inconsistent results when calculating "age" of db records
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) 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). I then apply the formula "t.seconds/3600" and get the result "12" hrs (which is incorrect). Can someone help me to troubleshoot this further? The db is locally installed and all the code is also running locally. I'mon the Dhaka timezone. Thanks. - Raheem M. Sarcar
Le 26/07/2012 08:11, Raheem Sarcar a écrit : > I get the following output --> > 11:24:47.082 (which is correct) > [...] > From which I get the following result --> > datetime.timedelta(-1, 45312, 918000). > > > I then apply the formula "t.seconds/3600" and get the result "12" hrs (which is incorrect). Did you forget do take into account the days member of your timedelta ? -1 day + 12 hours = 12 hours -- Ghislain LEVEQUE - Clarisys Informatique http://www.clarisys.fr - 09 72 11 43 60
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
- Raheem M. Sarcar
----- Original Message -----
From: Daniele Varrazzo <daniele.varrazzo@gmail.com>
To: Raheem Sarcar <raheemm1@yahoo.com>
Cc: "psycopg@postgresql.org" <psycopg@postgresql.org>
Sent: Thursday, July 26, 2012 3:31 PM
Subject: Re: [psycopg] 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
--
Sent via psycopg mailing list (psycopg@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/psycopg
I just added a record and tried again:
Output directly on postgres: -15:34:42.877 (its 3:37pm as I write this)
Output using psycopg2: datetime.timedelta( -1, 30319, 123000 )
I think the problem maybe the way I'm interpreting the second output (the one from psycopg2). At the moment, I'm
sendingthat output to the following function:
def days_hours_minutes(td):
return td.days, td.seconds/3600, (td.seconds/60)%60
But looks like I need to do some arithmetic to get the actual timedelta.
- Raheem M. Sarcar
----- Original Message -----
From: Daniele Varrazzo <daniele.varrazzo@gmail.com>
To: Raheem Sarcar <raheemm1@yahoo.com>
Cc: "psycopg@postgresql.org" <psycopg@postgresql.org>
Sent: Thursday, July 26, 2012 3:31 PM
Subject: Re: [psycopg] 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
--
Sent via psycopg mailing list (psycopg@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/psycopg
Ghislain, Im ignoring the -1. I assumed that for times when the timedelta is less than 1 day, that item is ignored. But maybe now?Would you mind elaborating on how to get find the hours ago a record was created from something like ( -1, 30319, 123000)? Thanks - Raheem M. Sarcar ----- Original Message ----- From: Ghislain LEVEQUE <ghislain.leveque@clarisys.fr> To: psycopg@postgresql.org Cc: Sent: Thursday, July 26, 2012 1:08 PM Subject: Re: [psycopg] Inconsistent results when calculating "age" of db records Le 26/07/2012 08:11, Raheem Sarcar a écrit : > I get the following output --> > 11:24:47.082 (which is correct) > [...] > From which I get the following result --> > datetime.timedelta(-1, 45312, 918000). > > > I then apply the formula "t.seconds/3600" and get the result "12" hrs (which is incorrect). Did you forget do take into account the days member of your timedelta ? -1 day + 12 hours = 12 hours -- Ghislain LEVEQUE - Clarisys Informatique http://www.clarisys.fr - 09 72 11 43 60 -- Sent via psycopg mailing list (psycopg@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/psycopg
Never mind all. It was a mistake in my conversion from timedelta to hours:mins format. Thanks for your help :) - Raheem M. Sarcar ----- Original Message ----- From: Raheem Sarcar <raheemm1@yahoo.com> To: "psycopg@postgresql.org" <psycopg@postgresql.org> Cc: Sent: Thursday, July 26, 2012 4:02 PM Subject: Re: [psycopg] Inconsistent results when calculating "age" of db records Ghislain, Im ignoring the -1. I assumed that for times when the timedelta is less than 1 day, that item is ignored. But maybe now?Would you mind elaborating on how to get find the hours ago a record was created from something like ( -1, 30319, 123000)? Thanks - Raheem M. Sarcar ----- Original Message ----- From: Ghislain LEVEQUE <ghislain.leveque@clarisys.fr> To: psycopg@postgresql.org Cc: Sent: Thursday, July 26, 2012 1:08 PM Subject: Re: [psycopg] Inconsistent results when calculating "age" of db records Le 26/07/2012 08:11, Raheem Sarcar a écrit : > I get the following output --> > 11:24:47.082 (which is correct) > [...] > From which I get the following result --> > datetime.timedelta(-1, 45312, 918000). > > > I then apply the formula "t.seconds/3600" and get the result "12" hrs (which is incorrect). Did you forget do take into account the days member of your timedelta ? -1 day + 12 hours = 12 hours -- Ghislain LEVEQUE - Clarisys Informatique http://www.clarisys.fr - 09 72 11 43 60 -- Sent via psycopg mailing list (psycopg@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/psycopg -- Sent via psycopg mailing list (psycopg@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/psycopg