From: Adrian Klaver <adrian.klaver@gmail.com>
To: pgsql-sql@postgresql.org
Cc: Janiv Ratson <janivra@essence-grp.com>
Subject: Re: bigint and unix time
Date: Sun, 14 Aug 2011 10:57:05 -0700
Message-ID: <201108141057.05589.adrian.klaver@gmail.com> (raw)
In-Reply-To: <9C103394ED80654B9DD6D9347E9D3001040F2014@es-ex1-il.essencesecurity.com>
References: <9C103394ED80654B9DD6D9347E9D3001040F2014@es-ex1-il.essencesecurity.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: adrian.klaver@gmail.com, janivra@essence-grp.com
Subject: Re: bigint and unix time
In-Reply-To: <201108141057.05589.adrian.klaver@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox