Re: bigint and unix time

Hi and thanks,
If my 'time' column is being saved as bigint: 1287323899000000.
How do I write a query to check if the 'time' field is greater than now - 30 (past 30 days)?

Thanks,
Janiv,.

-----Original Message-----
From: Adrian Klaver [mailto:adrian(dot)klaver(at)gmail(dot)com]
Sent: Sunday, August 14, 2011 20:57
To: pgsql-sql(at)postgresql(dot)org
Cc: Janiv Ratson
Subject: Re: [SQL] bigint and unix time

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(dot)klaver(at)gmail(dot)com