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