From unnikrishnan.menon@chennai.transys.net Tue Jan 15 03:57:34 2002 Received: from chennai.transys (chennai.transys.net [12.33.140.105]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0F3vXV33482 for ; Mon, 14 Jan 2002 22:57:34 -0500 (EST) (envelope-from unnikrishnan.menon@chennai.transys.net) Received: from UNNIKRISHNAN ([192.168.10.233]) by chennai.transys with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2650.21) id CGH4Y5B8; Tue, 15 Jan 2002 09:40:12 +0530 Message-ID: <005e01c19d79$16219db0$e90aa8c0@UNNIKRISHNAN> From: "Unnikrishnan Menon" To: Subject: Date Time calculation help Date: Mon, 14 Jan 2002 20:28:39 +0530 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0090_01C19D3A.0CDD0580" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Archive-Number: 200201/158 X-Sequence-Number: 6244 This is a multi-part message in MIME format. ------=_NextPart_000_0090_01C19D3A.0CDD0580 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I have a particular query in Oracle : select mod((sysdate - to_date('15-JAN-02','dd-mon-yy')),1) from dual; The above query gives 0.752.. as the difference between the two days which = when multiplied by 24 gives around 3.1.. hrs.=20 I tried the same query in postgresql but the value returned is 0. The query was: select mod((current_date - '2002-15-01')),1); I require the difference in time to the last second to be shown as time lef= t. Could anyone suggest as to how to overcome this particular problem. Thanx in advance, Unni ------=_NextPart_000_0090_01C19D3A.0CDD0580 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi,
 
I have a particular query in Oracle :
 
select mod((sysdate -=20 to_date('15-JAN-02','dd-mon-yy')),1) from dual;
The above query gives 0.752.. as the diffe= rence=20 between the two days which when multiplied by 24 gives around 3.1.. hrs.=20
 
I tried the same query in postgresql but t= he value=20 returned is 0.
The query was:
select mod((cu= rrent_date -=20 '2002-15-01')),1);
 
I require the difference in time to the la= st second=20 to be shown as time left. Could anyone suggest as to how to overcome this= =20 particular problem.
 
Thanx in advance,
 
Unni
------=_NextPart_000_0090_01C19D3A.0CDD0580-- From boylesa@roselink.co.uk Tue Jan 15 12:40:13 2002 Received: from mta02-svc.ntlworld.com (mta02-svc.ntlworld.com [62.253.162.42]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0FCeDV49516 for ; Tue, 15 Jan 2002 07:40:13 -0500 (EST) (envelope-from boylesa@roselink.co.uk) Received: from dualtower ([213.105.88.197]) by mta02-svc.ntlworld.com (InterMail vM.4.01.03.27 201-229-121-127-20010626) with SMTP id <20020115124015.BKBM8848.mta02-svc.ntlworld.com@dualtower>; Tue, 15 Jan 2002 12:40:15 +0000 Message-ID: <00eb01c19dc1$b6bea180$c55869d5@dualtower> From: "Steve Boyle \(Roselink\)" To: "Unnikrishnan Menon" , References: <005e01c19d79$16219db0$e90aa8c0@UNNIKRISHNAN> Subject: Re: Date Time calculation help Date: Tue, 15 Jan 2002 12:39:46 -0000 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00E8_01C19DC1.B69D36D0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Archive-Number: 200201/164 X-Sequence-Number: 6250 This is a multi-part message in MIME format. ------=_NextPart_000_00E8_01C19DC1.B69D36D0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Unnikrishanan, The following query will return the number of whole days between the two da= tes (a working version of postgres query you posted). select date_part('day','now'::datetime - '2001-01-15'::datetime); Other available datetime functions can be found at: http://www.il.postgresql.org/users-lounge/docs/7.1/postgres/functions-datet= ime.html hih steve boyle ----- Original Message -----=20 From: Unnikrishnan Menon=20 To: pgsql-sql@postgresql.org=20 Sent: Monday, January 14, 2002 2:58 PM Subject: [SQL] Date Time calculation help Hi, I have a particular query in Oracle : select mod((sysdate - to_date('15-JAN-02','dd-mon-yy')),1) from dual; The above query gives 0.752.. as the difference between the two days whic= h when multiplied by 24 gives around 3.1.. hrs.=20 I tried the same query in postgresql but the value returned is 0. The query was: select mod((current_date - '2002-15-01')),1); I require the difference in time to the last second to be shown as time l= eft. Could anyone suggest as to how to overcome this particular problem. Thanx in advance, Unni ------=_NextPart_000_00E8_01C19DC1.B69D36D0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Unnikrishanan,
 
The following query will return the number= of whole=20 days between the two dates (a working version of postgres query you=20 posted).
 
select date_part('day','now'::datetime -= =20 '2001-01-15'::datetime);
Other available datetime functions can be = found=20 at:
http://www.il.postgresql.org/users-lounge/docs/7.1/postgr= es/functions-datetime.html
 
hih
 
steve boyle
----- Original Message -----
Fro= m:=20 Unnikrishnan Menon
To: pgsql-sql@postgresql.org
Sent: Monday, January 14, 2002 2:5= 8=20 PM
Subject: [SQL] Date Time calculati= on=20 help

Hi,
 
I have a particular query in Oracle=20 :
 
select mod((sysdate -=20 to_date('15-JAN-02','dd-mon-yy')),1) from dual;
The above query gives 0.752.. as the dif= ference=20 between the two days which when multiplied by 24 gives around 3.1.. hrs.= =20
 
I tried the same query in postgresql but= the=20 value returned is 0.
The query was:
select mod((= current_date=20 - '2002-15-01')),1);
 
I require the difference in time to the = last=20 second to be shown as time left. Could anyone suggest as to how to overco= me=20 this particular problem.
 
Thanx in advance,
 
Unni
------=_NextPart_000_00E8_01C19DC1.B69D36D0-- From unnikrishnan.menon@chennai.transys.net Tue Jan 22 03:27:03 2002 Received: from chennai.transys (chennai.transys.net [12.33.140.105]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0M3R3l20774 for ; Mon, 21 Jan 2002 22:27:03 -0500 (EST) (envelope-from unnikrishnan.menon@chennai.transys.net) Received: from UNNIKRISHNAN ([192.168.10.233]) by chennai.transys with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2650.21) id CGH4Z4KW; Tue, 22 Jan 2002 09:09:50 +0530 Message-ID: <002601c1a2f5$06ad4330$e90aa8c0@UNNIKRISHNAN> From: "Unnikrishnan Menon" To: "Josh Berkus" , References: Subject: Re: Date Time calculation help Date: Tue, 22 Jan 2002 08:59:35 +0530 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Archive-Number: 200201/252 X-Sequence-Number: 6336 Thanx Josh, This helped solve my problem. Unni ----- Original Message ----- From: "Josh Berkus" To: "Unnikrishnan Menon" Sent: Tuesday, January 22, 2002 2:02 AM Subject: Re: [SQL] Date Time calculation help > Unni, > > > I tried the same query in postgresql but the value returned is 0. > > The query was: > > select mod((current_date - '2002-15-01')),1); > > The problem above is that you are using the DATE data type, which does > not admit hours. As a result, mod(1)=0 is quite correct. > > You want to use the TIMESTAMP data type, and process your outcome as an > interval: > select (current_timestamp - ('2002-15-01'::Timestamp)) > which should give you some days and some hours. > > See: http://techdocs.postgresql.org/techdocs/faqdatesintervals.php > for more info about dates and times. > > Of course, I could give you more help if I was clear on what exactly > you want to achieve. Do you want the date, or just hours? Care to > elucidate? > > -Josh Berkus > > ______AGLIO DATABASE SOLUTIONS___________________________ > Josh Berkus > Complete information technology josh@agliodbs.com > and data management solutions (415) 565-7293 > for law firms, small businesses fax 621-2533 > and non-profit organizations. San Francisco