[SQL] bigint and unix time

[Janiv Ratson]

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: 128732389900 

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:  jani...@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. 

 

Re: [SQL] pgadmin debugger

[David Harel]

On Sat, 2011-08-13 at 12:56 -0700, Adrian Klaver wrote:
> On Saturday, August 13, 2011 12:39:44 pm David Harel wrote:
> > Greetings,
> > 
> > I use Ubuntu 10.04. I have postgresql version 8.4.8 installed also I
> > have pgadmin version 1.10.2. I can't find debugger_plugin.so which is
> > needed to debug pgplsql using pgadmin:
> > http://www.postgresonline.com/journal/archives/214-Using-PgAdmin-PLPgSQL-De
> > bugger.html
> > 
> > Any idea?
> 
> http://www.enterprisedb.com/docs/en/8.4/pgadmin/debugger.html

Trying to compile (no configure script found) the thing I get:
pldebugger # make
Makefile:42: ../../src/Makefile.global: No such file or directory
Makefile:43: /contrib/contrib-global.mk: No such file or directory
make: *** No rule to make target `/contrib/contrib-global.mk'.  Stop.


-- 
Thanks.

David Harel,

==

Home office +972 77 7657645
Cellular:   +972 54 4534502
eMail:  harel...@gmail.com
Snail Mail: Amuka
D.N Merom Hagalil
13802
Israel



-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] bigint and unix time

[Adrian Klaver]

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: 128732389900
> 
> 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.kla...@gmail.com

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] pgadmin debugger

[Adrian Klaver]

On Sunday, August 14, 2011 10:15:43 am David Harel wrote:
> On Sat, 2011-08-13 at 12:56 -0700, Adrian Klaver wrote:
> > On Saturday, August 13, 2011 12:39:44 pm David Harel wrote:
> > > Greetings,
> > > 
> > > I use Ubuntu 10.04. I have postgresql version 8.4.8 installed also I
> > > have pgadmin version 1.10.2. I can't find debugger_plugin.so which is
> > > needed to debug pgplsql using pgadmin:
> > > http://www.postgresonline.com/journal/archives/214-Using-PgAdmin-PLPgSQ
> > > L-De bugger.html
> > > 
> > > Any idea?
> > 
> > http://www.enterprisedb.com/docs/en/8.4/pgadmin/debugger.html
> 
> Trying to compile (no configure script found) the thing I get:
> pldebugger # make
> Makefile:42: ../../src/Makefile.global: No such file or directory
> Makefile:43: /contrib/contrib-global.mk: No such file or directory
> make: *** No rule to make target `/contrib/contrib-global.mk'.  Stop.

Looking at README.pldebugger  indicates you need the development code for 
Postgres. Is your Postgres installation from the Ubuntu packages or did you 
compile it yourself?

-- 
Adrian Klaver
adrian.kla...@gmail.com

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] pgadmin debugger

[David Harel]

On Sun, 2011-08-14 at 11:07 -0700, Adrian Klaver wrote:
> On Sunday, August 14, 2011 10:15:43 am David Harel wrote:
> > On Sat, 2011-08-13 at 12:56 -0700, Adrian Klaver wrote:
> > > On Saturday, August 13, 2011 12:39:44 pm David Harel wrote:
> > > > Greetings,
> > > > 
> > > > I use Ubuntu 10.04. I have postgresql version 8.4.8 installed also I
> > > > have pgadmin version 1.10.2. I can't find debugger_plugin.so which is
> > > > needed to debug pgplsql using pgadmin:
> > > > http://www.postgresonline.com/journal/archives/214-Using-PgAdmin-PLPgSQ
> > > > L-De bugger.html
> > > > 
> > > > Any idea?
> > > 
> > > http://www.enterprisedb.com/docs/en/8.4/pgadmin/debugger.html
> > 
> > Trying to compile (no configure script found) the thing I get:
> > pldebugger # make
> > Makefile:42: ../../src/Makefile.global: No such file or directory
> > Makefile:43: /contrib/contrib-global.mk: No such file or directory
> > make: *** No rule to make target `/contrib/contrib-global.mk'.  Stop.
> 
> Looking at README.pldebugger  indicates you need the development code for 
> Postgres. Is your Postgres installation from the Ubuntu packages or did you 
> compile it yourself?
> 
I installed form Ubuntu package. I am currently trying to install
postgresql-server-dev-8.4.8-0ubuntu0.10.04 package.
Will that suffice?

-- 
Thanks.

David Harel,

==

Home office +972 77 7657645
Cellular:   +972 54 4534502
eMail:  harel...@gmail.com
Snail Mail: Amuka
D.N Merom Hagalil
13802
Israel



-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql