From: Adrian Klaver <adrian.klaver@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: bigint and unix time
Date: Sun, 14 Aug 2011 10:57:05 -0700
User-Agent: KMail/1.13.5 (Linux/2.6.32-33-generic; KDE/4.4.5; i686; ; )
Cc: "Janiv Ratson" <janivra@essence-grp.com>
References: 
 <9C103394ED80654B9DD6D9347E9D3001040F2014@es-ex1-il.essencesecurity.com>
In-Reply-To: 
 <9C103394ED80654B9DD6D9347E9D3001040F2014@es-ex1-il.essencesecurity.com>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="iso-8859-15"
Content-Transfer-Encoding: 7bit
Message-Id: <201108141057.05589.adrian.klaver@gmail.com>

On Sunday, August 14, 2011 4:13:30 am Janiv Ratson wrote:
> Hi,
> 
> 
> 
> I have the following query:
> 
> 
> 
> select ticket as ticket, time as created, author as reporter,
> cast(extract(epoch from (date 'now' - integer '30')) as bigint)
> 
> from ticket_change tc
> 
> where field = 'status'
> 
> and newvalue = 'reopened'
> 
> and time > extract(epoch from (date 'now' - integer '30'))
> 
> order by time
> 
> 
> 
> I'm trying it get all records that their 'time' is within the past 30
> days.
> 
> However, the time is bigint: 1287323899000000
> 
> While the extract(epoch from (date 'now' - integer '30')) is 1310677200

Bigint versus integer refers to the max values that the field can contain.  For a 
given value of integer the storage should be the same for each up to the limit 
of the integer field. Would seem that whatever is putting values into time is 
inflating the values if they are actually referring to contemporary time values.

> 
> 
> 
> As you understand, I always get all records ...
> 
> 
> 
> How do I solve it?
> 
> 
> 
> Thanks,
> 
> Janiv.
> 


-- 
Adrian Klaver
adrian.klaver@gmail.com