Обсуждение: bigint and unix time
<div class="Section1"><p class="MsoNormal">Hi,<p class="MsoNormal"> <p class="MsoNormal">I have the following query:<p
class="MsoNormal"> <pclass="MsoNormal">select ticket as ticket, time as created, author as reporter, cast(extract(epoch
from(date 'now' - integer '30')) as bigint)<p class="MsoNormal">from ticket_change tc<p class="MsoNormal">where field =
'status'<p class="MsoNormal">and newvalue = 'reopened'<p class="MsoNormal">and time > extract(epoch from (date 'now'
-integer '30'))<p class="MsoNormal">order by time<p class="MsoNormal"> <p class="MsoNormal">I’m trying it get all
recordsthat their ‘time’ is within the past 30 days.<p class="MsoNormal">However, the time is bigint: 1287323899000000
<pclass="MsoNormal">While the extract(epoch from (date 'now' - integer '30')) is 1310677200<p class="MsoNormal"> <p
class="MsoNormal">Asyou understand, I always get all records …<p class="MsoNormal"> <p class="MsoNormal">How do I solve
it?<pclass="MsoNormal"> <p class="MsoNormal">Thanks,<p class="MsoNormal">Janiv.<p class="MsoNormal"> <p
class="MsoNormal"><b><spanstyle="font-size:8.0pt;font-family:"Verdana","sans-serif";
color:gray">Best regards,</span></b><span style="font-size:12.0pt;font-family:
"Times New Roman","serif""></span><p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Times New
Roman","serif""> </span><pclass="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";
color:navy">Janiv Ratson</span></b><span style="font-size:12.0pt;font-family:
"Times New Roman","serif""></span><p class="MsoNormal"><b><span
style="font-size:8.0pt;font-family:"Verdana","sans-serif";
color:gray">Software Architect & Team Leader</span></b><span style="font-size:12.0pt;font-family:"Times New
Roman","serif""></span><pclass="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><b><span
style="font-size:8.0pt;font-family:"Verdana","sans-serif";color:gray">EssenceHome & Family
LTD.. (Part of Essence Group)<br/> Tel: </span></b><span style="font-size:12.0pt;font-family:"Times New
Roman","serif""></span><b><span style="font-size:8.0pt;font-family:"Tahoma","sans-serif";
color:gray">+972-73-2447779 </span></b><span style="font-size:12.0pt;
font-family:"Times New Roman","serif""> </span><b><span style="font-size:8.0pt;
font-family:"Tahoma","sans-serif";color:#FF6600">|</span></b><span style="font-size:12.0pt;font-family:"Times New
Roman","serif""></span><b><span
style="font-size:8.0pt;font-family:"Verdana","sans-serif";color:gray">Fax: </span></b><span
style="font-size:12.0pt;font-family:"TimesNew Roman","serif""> </span><b><span
style="font-size:8.0pt;font-family:"Tahoma","sans-serif";color:gray">+972-9-9564182 </span></b><span
style="font-size:12.0pt;font-family:"TimesNew Roman","serif""> </span><b><span
style="font-size:8.0pt;font-family:"Tahoma","sans-serif";color:#FF6600">|</span></b><span
style="font-size:12.0pt;font-family:"TimesNew Roman","serif""> </span><b><span
style="font-size:8.0pt;font-family:"Verdana","sans-serif";color:gray">Mobile: </span></b><span
style="font-size:12.0pt;font-family:"TimesNew Roman","serif""> </span><b><span
style="font-size:8.0pt;font-family:"Tahoma","sans-serif";color:gray">+972-507768822 <br/></span></b><b><span
style="font-size:8.0pt;font-family:"Tahoma","sans-serif";
color:#7F7F7F">Email: </span></b><span style="font-size:12.0pt;font-family:
"Times New Roman","serif""> </span><b><span style="font-size:8.0pt;font-family:
"Verdana","sans-serif";color:navy"><a href="mailto:janivra@essence-grp.com"><span
style="color:blue">janivra@essence-grp.com</span></a> </span></b><spanstyle="font-size:12.0pt;font-family:"Times New
Roman","serif""></span><b><span
style="font-size:8.0pt;font-family:"Tahoma","sans-serif";color:#FF6600">|</span></b><span
style="font-size:12.0pt;font-family:"TimesNew Roman","serif""> </span><b><span
style="font-size:8.0pt;font-family:"Tahoma","sans-serif";color:#7F7F7F">Site: </span></b><span
style="font-size:12.0pt;font-family:"TimesNew Roman","serif""> </span><b><span
style="font-size:8.0pt;font-family:"Verdana","sans-serif";color:navy"><ahref="www.essence-grp.com"><span
style="color:blue">www.essence-grp.com</span></a><br/><br /><br /></span></b><span
style="font-size:10.0pt;color:black">ThisE-mail and any attachments transmitted with it (if any) ("<b>E-mail</b>") is
privateand confidential, contains proprietary information belonging to the Essence Group and is intended only for the
intendedrecipient at the email address to which it has been addressed. Any review, retransmission, dissemination,
copyingor other use of, or taking of any action in reliance upon this E-mail, by persons or entities other than the
intendedrecipient is prohibited. Please notify the sender immediately by e-mail if you have received this E-mail by
mistakeand delete this E-mail from your system. Any opinions expressed in this E-mail are those of the sender and do
notnecessarily 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
anyerrors or omissions in the contents of this E-mail arise as a result of e-mail transmission or for any damage caused
byany virus transmitted by this E-mail. <br /></span><span lang="EN-GB" style="font-size:18.0pt;font-family:Webdings;
color:green">P</span><span lang="EN-GB" style="font-size:12.0pt;font-family:"Times New Roman","serif""> </span><span
style="font-size:9.0pt;font-family:"ComicSans MS";color:green">save a tree...please don't print this e-mail<i> unless
youreally need to.</i></span><span style="font-size:12.0pt;font-family:"Times New Roman","serif""> </span><p
class="MsoNormal"><spanstyle="font-size:12.0pt;font-family:"Times New Roman","serif""> </span></div>
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
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')) ANDextract(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
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
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
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')) ANDextract(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
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')) ANDextract(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')) ANDextract(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
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')) ANDextract(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