pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedbigint and unix time
8+ messages / 3 participants
[nested] [flat]
* bigint and unix time
@ 2011-08-14 11:13 Janiv Ratson <janivra@essence-grp.com>
0 siblings, 1 reply; 8+ messages in thread
From: Janiv Ratson @ 2011-08-14 11:13 UTC (permalink / raw)
To: pgsql-sql
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
As you understand, I always get all records ...
How do I solve it?
Thanks,
Janiv.
Best regards,
Janiv Ratson
Software Architect & Team Leader
Essence Home & Family LTD.. (Part of Essence Group)
Tel: +972-73-2447779 | Fax: +972-9-9564182 | Mobile: +972-507768822
Email: janivra@essence-grp.com <mailto:janivra@essence-grp.com> |
Site: www.essence-grp.com
This E-mail and any attachments transmitted with it (if any) ("E-mail")
is private and confidential, contains proprietary information belonging
to the Essence Group and is intended only for the intended recipient at
the email address to which it has been addressed. Any review,
retransmission, dissemination, copying or other use of, or taking of any
action in reliance upon this E-mail, by persons or entities other than
the intended recipient is prohibited. Please notify the sender
immediately by e-mail if you have received this E-mail by mistake and
delete this E-mail from your system. Any opinions expressed in this
E-mail are those of the sender and do not necessarily represent those of
Essence Group unless otherwise stated expressly and the sender is
authorised to do so. E-mail transmission cannot be guaranteed to be
secured or error-free as information could be intercepted, corrupted,
lost, destroyed, arrive late or incomplete, or contain viruses. Essence
Group therefore does not accept liability for any errors or omissions in
the contents of this E-mail arise as a result of e-mail transmission or
for any damage caused by any virus transmitted by this E-mail.
P save a tree...please don't print this e-mail unless you really need
to.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: bigint and unix time
@ 2011-08-14 17:57 Adrian Klaver <adrian.klaver@gmail.com>
parent: Janiv Ratson <janivra@essence-grp.com>
0 siblings, 1 reply; 8+ messages in thread
From: Adrian Klaver @ 2011-08-14 17:57 UTC (permalink / raw)
To: pgsql-sql; +Cc: Janiv Ratson <janivra@essence-grp.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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: bigint and unix time
@ 2011-08-15 06:23 Janiv Ratson <yanivra@essence-grp.com>
parent: Adrian Klaver <adrian.klaver@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Janiv Ratson @ 2011-08-15 06:23 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@gmail.com>; pgsql-sql
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.klaver@gmail.com]
Sent: Sunday, August 14, 2011 20:57
To: pgsql-sql@postgresql.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.klaver@gmail.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: bigint and unix time
@ 2011-08-15 14:14 Adrian Klaver <adrian.klaver@gmail.com>
parent: Janiv Ratson <yanivra@essence-grp.com>
0 siblings, 3 replies; 8+ messages in thread
From: Adrian Klaver @ 2011-08-15 14:14 UTC (permalink / raw)
To: Janiv Ratson <yanivra@essence-grp.com>; +Cc: pgsql-sql
On Sunday, August 14, 2011 11:23:08 pm Janiv Ratson wrote:
> 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)?
So what you want is not what values are greater than some point 30 days ago
which is what your previous query asked and answered, but the values between a
point 30 days ago and today. The easiest way is to use BETWEEN:
test(5432)aklaver=>\d big_int_test
Table "public.big_int_test"
Column | Type | Modifiers
--------+---------+-----------
bint | bigint |
rint | integer |
test(5432)aklaver=>SELECT * from big_int_test ;
bint | rint
------------------+------------
1287323899000000 | 1310799600
test(5432)aklaver=>SELECT
bint
FROM
big_int_test
WHERE
bint
BETWEEN
extract(epoch from (date 'now' - integer '30'))
AND
extract(epoch from (date 'now'));
bint
------
(0 rows)
That being said, if your time values are the order of magnitude shown they will
not meet the criteria above. Is the time value supposed to be seconds?
>
> Thanks,
> Janiv,.
--
Adrian Klaver
adrian.klaver@gmail.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: bigint and unix time
@ 2011-08-16 09:12 Janiv Ratson <yanivra@essence-grp.com>
parent: Adrian Klaver <adrian.klaver@gmail.com>
2 siblings, 1 reply; 8+ messages in thread
From: Janiv Ratson @ 2011-08-16 09:12 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@gmail.com>; +Cc: pgsql-sql
Hi Adrain and thank you,
Trac 0.12 uses microseconds as time value.
What do you suggest?
Thanks,
Janiv.
-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@gmail.com]
Sent: Monday, August 15, 2011 17:14
To: Janiv Ratson
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] bigint and unix time
On Sunday, August 14, 2011 11:23:08 pm Janiv Ratson wrote:
> 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)?
So what you want is not what values are greater than some point 30 days ago
which is what your previous query asked and answered, but the values between a
point 30 days ago and today. The easiest way is to use BETWEEN:
test(5432)aklaver=>\d big_int_test
Table "public.big_int_test"
Column | Type | Modifiers
--------+---------+-----------
bint | bigint |
rint | integer |
test(5432)aklaver=>SELECT * from big_int_test ;
bint | rint
------------------+------------
1287323899000000 | 1310799600
test(5432)aklaver=>SELECT
bint
FROM
big_int_test
WHERE
bint
BETWEEN
extract(epoch from (date 'now' - integer '30'))
AND
extract(epoch from (date 'now'));
bint
------
(0 rows)
That being said, if your time values are the order of magnitude shown they will
not meet the criteria above. Is the time value supposed to be seconds?
>
> Thanks,
> Janiv,.
--
Adrian Klaver
adrian.klaver@gmail.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: bigint and unix time
@ 2011-08-16 09:15 Janiv Ratson <yanivra@essence-grp.com>
parent: Adrian Klaver <adrian.klaver@gmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: Janiv Ratson @ 2011-08-16 09:15 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@gmail.com>; +Cc: pgsql-sql
Hi and thanks again.
I use the following query but it returns me 0 rows (it should return more):
SELECT ticket, "time", author, field, oldvalue, newvalue
FROM ticket_change
where "time"
BETWEEN
extract(epoch from (date 'now' - integer '30')) AND
extract(epoch from (date 'now'));
Thanks a lot again,
Janiv.
-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@gmail.com]
Sent: Monday, August 15, 2011 17:14
To: Janiv Ratson
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] bigint and unix time
On Sunday, August 14, 2011 11:23:08 pm Janiv Ratson wrote:
> 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)?
So what you want is not what values are greater than some point 30 days ago
which is what your previous query asked and answered, but the values between a
point 30 days ago and today. The easiest way is to use BETWEEN:
test(5432)aklaver=>\d big_int_test
Table "public.big_int_test"
Column | Type | Modifiers
--------+---------+-----------
bint | bigint |
rint | integer |
test(5432)aklaver=>SELECT * from big_int_test ;
bint | rint
------------------+------------
1287323899000000 | 1310799600
test(5432)aklaver=>SELECT
bint
FROM
big_int_test
WHERE
bint
BETWEEN
extract(epoch from (date 'now' - integer '30'))
AND
extract(epoch from (date 'now'));
bint
------
(0 rows)
That being said, if your time values are the order of magnitude shown they will
not meet the criteria above. Is the time value supposed to be seconds?
>
> Thanks,
> Janiv,.
--
Adrian Klaver
adrian.klaver@gmail.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: bigint and unix time
@ 2011-08-16 09:19 Janiv Ratson <yanivra@essence-grp.com>
parent: Adrian Klaver <adrian.klaver@gmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: Janiv Ratson @ 2011-08-16 09:19 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@gmail.com>; +Cc: pgsql-sql
Additional information:
If I use the following query:
select "time", extract(epoch from (date 'now' - integer '30')), extract(epoch from (date 'now'))
from ticket_change
The first row looks like this:
Bigint, double precision, double precision
1286090615000000;1310850000;1313442000
Thanks,
Janiv,
-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@gmail.com]
Sent: Monday, August 15, 2011 17:14
To: Janiv Ratson
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] bigint and unix time
On Sunday, August 14, 2011 11:23:08 pm Janiv Ratson wrote:
> 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)?
So what you want is not what values are greater than some point 30 days ago
which is what your previous query asked and answered, but the values between a
point 30 days ago and today. The easiest way is to use BETWEEN:
test(5432)aklaver=>\d big_int_test
Table "public.big_int_test"
Column | Type | Modifiers
--------+---------+-----------
bint | bigint |
rint | integer |
test(5432)aklaver=>SELECT * from big_int_test ;
bint | rint
------------------+------------
1287323899000000 | 1310799600
test(5432)aklaver=>SELECT
bint
FROM
big_int_test
WHERE
bint
BETWEEN
extract(epoch from (date 'now' - integer '30'))
AND
extract(epoch from (date 'now'));
bint
------
(0 rows)
That being said, if your time values are the order of magnitude shown they will
not meet the criteria above. Is the time value supposed to be seconds?
>
> Thanks,
> Janiv,.
--
Adrian Klaver
adrian.klaver@gmail.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: bigint and unix time
@ 2011-08-16 13:43 Adrian Klaver <adrian.klaver@gmail.com>
parent: Janiv Ratson <yanivra@essence-grp.com>
0 siblings, 0 replies; 8+ messages in thread
From: Adrian Klaver @ 2011-08-16 13:43 UTC (permalink / raw)
To: Janiv Ratson <yanivra@essence-grp.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2011-08-16 13:43 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-08-14 11:13 bigint and unix time Janiv Ratson <janivra@essence-grp.com>
2011-08-14 17:57 ` Adrian Klaver <adrian.klaver@gmail.com>
2011-08-15 06:23 ` Janiv Ratson <yanivra@essence-grp.com>
2011-08-15 14:14 ` Adrian Klaver <adrian.klaver@gmail.com>
2011-08-16 09:12 ` Janiv Ratson <yanivra@essence-grp.com>
2011-08-16 13:43 ` Adrian Klaver <adrian.klaver@gmail.com>
2011-08-16 09:15 ` Janiv Ratson <yanivra@essence-grp.com>
2011-08-16 09:19 ` Janiv Ratson <yanivra@essence-grp.com>
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