Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 46A91B5DC45
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 16 Aug 2011 10:44:07 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 39138-01-3 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 16 Aug 2011 13:44:00 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-yx0-f174.google.com (mail-yx0-f174.google.com
	[209.85.213.174])
	by mail.postgresql.org (Postfix) with ESMTP id 4B963B5DC53
	for <pgsql-sql@postgresql.org>; Tue, 16 Aug 2011 10:43:59 -0300 (ADT)
Received: by yxj19 with SMTP id 19so3757825yxj.19
	for <pgsql-sql@postgresql.org>; Tue, 16 Aug 2011 06:44:00 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=from:to:subject:date:user-agent:cc:references:in-reply-to
	:mime-version:content-type:content-transfer-encoding:message-id;
	bh=23DRHZHx1dagumuLZpF1xRIh4qNSjpWcFGS/Jqlww6U=;
	b=WOwuCBgs7hPvuRwt7TAFj0mVy1MXY/+YtCmqrIf2JYtH5lIvowBsZ3nMFM8OUf1gFr
	rhcE8HlwgkXpM9ZQ0FEiMtbm2SOKdMI1pDYd4O5m6coMLTJk+A4PVextfgJ1wWV6hsxE
	7ZK97IQAKxy0L8qITmbHQvLlE8Rb3NCHGYxZE=
Received: by 10.236.155.40 with SMTP id i28mr16492225yhk.156.1313502240012;
	Tue, 16 Aug 2011 06:44:00 -0700 (PDT)
Received: from tucker.localnet (c-24-17-164-54.hsd1.wa.comcast.net
	[24.17.164.54])
	by mx.google.com with ESMTPS id r28sm48048yhm.80.2011.08.16.06.43.57
	(version=SSLv3 cipher=OTHER); Tue, 16 Aug 2011 06:43:59 -0700 (PDT)
From: Adrian Klaver <adrian.klaver@gmail.com>
To: Janiv Ratson <yanivra@essence-grp.com>
Subject: Re: bigint and unix time
Date: Tue, 16 Aug 2011 06:43:54 -0700
User-Agent: KMail/1.13.5 (Linux/2.6.32-33-generic; KDE/4.4.5; i686; ; )
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
References: 
 <9C103394ED80654B9DD6D9347E9D3001040F2014@es-ex1-il.essencesecurity.com>
	<201108150714.08500.adrian.klaver@gmail.com>
	<25B9D8D6D61CDB4CAD5D742B947F5C1A0493CBC8@ES-EX2010A-IL.essencesecurity.com>
In-Reply-To: 
 <25B9D8D6D61CDB4CAD5D742B947F5C1A0493CBC8@ES-EX2010A-IL.essencesecurity.com>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Message-Id: <201108160643.54672.adrian.klaver@gmail.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	FREEMAIL_FROM=0.001
X-Spam-Level: 
X-Archive-Number: 201108/58
X-Sequence-Number: 35726

On Tuesday, August 16, 2011 2:12:52 am Janiv Ratson wrote:
> Hi Adrain and thank you,
> Trac 0.12 uses microseconds as time value.
> What do you suggest?

extract(epoch ..) returns seconds which you are trying to compare to 
microseconds. The solution would be to divide your 'time' values by 1,000,000 to 
make them seconds. Like:

SELECT ticket, "time"/1000000, author, field, oldvalue, newvalue
  FROM ticket_change
  where "time"/1000000 
  BETWEEN 
        extract(epoch from (date 'now' - integer '30')) AND
        extract(epoch from (date 'now'));


> 
> Thanks,
> Janiv.
> 

-- 
Adrian Klaver
adrian.klaver@gmail.com