From jinujosein@yahoo.com Fri Nov 7 13:34:38 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id B42C4D1B4E5 for ; Fri, 7 Nov 2003 13:34:38 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 13626-02 for ; Fri, 7 Nov 2003 09:34:10 -0400 (AST) Received: from web14909.mail.yahoo.com (web14909.mail.yahoo.com [216.136.225.61]) by svr1.postgresql.org (Postfix) with SMTP id 644FED1B507 for ; Fri, 7 Nov 2003 09:34:06 -0400 (AST) Message-ID: <20031107133409.25844.qmail@web14909.mail.yahoo.com> Received: from [202.9.168.133] by web14909.mail.yahoo.com via HTTP; Fri, 07 Nov 2003 05:34:09 PST Date: Fri, 7 Nov 2003 05:34:09 -0800 (PST) From: "George A.J" Subject: DateDiff in PostgreSQL To: pgsql-sql@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1357030754-1068212049=:25074" X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.8 tagged_above=0.0 required=5.0 tests=HTML_10_20, IN_REP_TO X-Spam-Level: X-Archive-Number: 200311/38 X-Sequence-Number: 15853 --0-1357030754-1068212049=:25074 Content-Type: text/plain; charset=us-ascii Hi, i am converting a MSSQL Server database to PostgreSQL. Using PostgreSQL version 7.3.2. Is there any function like the DateDiff() in MSSQL Server. ie, a function that returns difference of two dates(timestamp) in days or months or year.. The - operator for timestamp retuns the intervel in days only. Please help jinujose --------------------------------- Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard --0-1357030754-1068212049=:25074 Content-Type: text/html; charset=us-ascii

Hi,

i am converting a MSSQL Server database to PostgreSQL.

Using PostgreSQL version 7.3.2.

Is there any function like the DateDiff() in MSSQL Server.

ie, a function that returns difference of two dates(timestamp) in days or months or year..

The - operator for timestamp retuns the intervel in days only.

Please help

jinujose

Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard --0-1357030754-1068212049=:25074-- From ymalik@heineken.cs.stevens-tech.edu Fri Nov 7 13:40:54 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 889F0D1B4E1 for ; Fri, 7 Nov 2003 13:40:54 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 13906-03 for ; Fri, 7 Nov 2003 09:40:25 -0400 (AST) Received: from heineken.cs.stevens-tech.edu (heineken.cs.stevens-tech.edu [155.246.89.69]) by svr1.postgresql.org (Postfix) with ESMTP id 19F17D1B4E4 for ; Fri, 7 Nov 2003 09:40:22 -0400 (AST) Received: from ymalik (helo=localhost) by heineken.cs.stevens-tech.edu with local-esmtp (Exim 3.35 #1 (Debian)) id 1AI6r6-0005NK-00; Fri, 07 Nov 2003 08:40:52 -0500 Date: Fri, 7 Nov 2003 08:40:52 -0500 (EST) From: Yasir Malik To: "George A.J" Cc: pgsql-sql@postgresql.org Subject: Re: DateDiff in PostgreSQL In-Reply-To: <20031107133409.25844.qmail@web14909.mail.yahoo.com> Message-ID: References: <20031107133409.25844.qmail@web14909.mail.yahoo.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/69 X-Sequence-Number: 15884 Hi, You can use the age() function to find the difference between dates, and use the extract() function to get the years, months, days, etc. Yasir On Fri, 7 Nov 2003, George A.J wrote: > Date: Fri, 7 Nov 2003 05:34:09 -0800 (PST) > From: George A.J > To: pgsql-sql@postgresql.org > Subject: [SQL] DateDiff in PostgreSQL > > > Hi, > > i am converting a MSSQL Server database to PostgreSQL. > > Using PostgreSQL version 7.3.2. > > Is there any function like the DateDiff() in MSSQL Server. > > ie, a function that returns difference of two dates(timestamp) in days or months or year.. > > The - operator for timestamp retuns the intervel in days only. > > Please help > > jinujose > > > --------------------------------- > Do you Yahoo!? > Protect your identity with Yahoo! Mail AddressGuard From ymalik@cs.stevens-tech.edu Fri Nov 7 13:45:44 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 320D1D1B4E1 for ; Fri, 7 Nov 2003 13:45:44 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 12905-06 for ; Fri, 7 Nov 2003 09:45:16 -0400 (AST) Received: from guinness.cs.stevens-tech.edu (guinness.cs.stevens-tech.edu [155.246.89.8]) by svr1.postgresql.org (Postfix) with ESMTP id 4E2ABD1B507 for ; Fri, 7 Nov 2003 09:45:13 -0400 (AST) Received: by guinness.cs.stevens-tech.edu (Postfix, from userid 4405) id 8C8A514187F; Fri, 7 Nov 2003 08:45:16 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by guinness.cs.stevens-tech.edu (Postfix) with ESMTP id 7E54FC2454; Fri, 7 Nov 2003 08:45:16 -0500 (EST) Date: Fri, 7 Nov 2003 08:45:16 -0500 From: Yasir Malik To: jinujosein@yahoo.com Cc: pgsql-sql@postgresql.org Subject: Re: DateDiff in PostgreSQL Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.2 tagged_above=0.0 required=5.0 tests=EMAIL_ATTRIBUTION, QUOTED_EMAIL_TEXT, RCVD_IN_RFCI, USER_AGENT_PINE X-Spam-Level: X-Archive-Number: 200311/39 X-Sequence-Number: 15854 Hi, You can use the age() function to find the difference between dates, and use the extract() function to get the years, months, days, etc. Yasir On Fri, 7 Nov 2003, George A.J wrote: > Date: Fri, 7 Nov 2003 05:34:09 -0800 (PST) > From: George A.J > To: pgsql-sql@postgresql.org > Subject: [SQL] DateDiff in PostgreSQL > > > Hi, > > i am converting a MSSQL Server database to PostgreSQL. > > Using PostgreSQL version 7.3.2. > > Is there any function like the DateDiff() in MSSQL Server. > > ie, a function that returns difference of two dates(timestamp) in days or months or year.. > > The - operator for timestamp retuns the intervel in days only. > > Please help > > jinujose > > > --------------------------------- > Do you Yahoo!? > Protect your identity with Yahoo! Mail AddressGuard From jasiek@klaster.net Fri Nov 7 13:50:44 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id D42EDD1B540 for ; Fri, 7 Nov 2003 13:50:44 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 15519-01 for ; Fri, 7 Nov 2003 09:50:17 -0400 (AST) Received: from serwer.skawsoft.com.pl (rtr.skawina.eu.org [80.48.213.10]) by svr1.postgresql.org (Postfix) with ESMTP id 8FF07D1B54B for ; Fri, 7 Nov 2003 09:50:12 -0400 (AST) Received: from klaster.net (core-1.citynet.pl [80.48.135.69]) by serwer.skawsoft.com.pl (Postfix) with ESMTP id AA5342B3CB; Fri, 7 Nov 2003 14:49:13 +0100 (CET) Message-ID: <3FABA26D.5030900@klaster.net> Date: Fri, 07 Nov 2003 14:47:25 +0100 From: Tomasz Myrta User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; pl-PL; rv:1.5) Gecko/20030916 X-Accept-Language: pl, en-us, en MIME-Version: 1.0 To: "George A.J" Cc: pgsql-sql@postgresql.org Subject: Re: DateDiff in PostgreSQL References: <20031107133409.25844.qmail@web14909.mail.yahoo.com> In-Reply-To: <20031107133409.25844.qmail@web14909.mail.yahoo.com> Content-Type: text/plain; charset=ISO-8859-2; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/40 X-Sequence-Number: 15855 Dnia 2003-11-07 14:34, Użytkownik George A.J napisał: > Hi, > > i am converting a MSSQL Server database to PostgreSQL. > > Using PostgreSQL version 7.3.2. > > Is there any function like the DateDiff() in MSSQL Server. > > ie, a function that returns difference of two dates(timestamp) in days > or months or year.. > > The - operator for timestamp retuns the intervel in days only. 1. You can convert timestamps to dates and then substract them. The result will be days::integer. 2. Interval result is not a problem - you can extract epoch from it. The result is seconds::integer - do whatever you want with it. Regards, Tomasz Myrta From jinujosein@yahoo.com Fri Nov 7 13:54:08 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 8DF72D1B4E1 for ; Fri, 7 Nov 2003 13:54:08 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 15519-02 for ; Fri, 7 Nov 2003 09:53:40 -0400 (AST) Received: from web14909.mail.yahoo.com (web14909.mail.yahoo.com [216.136.225.61]) by svr1.postgresql.org (Postfix) with SMTP id 4BD37D1B507 for ; Fri, 7 Nov 2003 09:53:36 -0400 (AST) Message-ID: <20031107135335.29716.qmail@web14909.mail.yahoo.com> Received: from [202.9.168.133] by web14909.mail.yahoo.com via HTTP; Fri, 07 Nov 2003 05:53:35 PST Date: Fri, 7 Nov 2003 05:53:35 -0800 (PST) From: "George A.J" Subject: Re: DateDiff in PostgreSQL To: Yasir Malik Cc: pgsql-sql@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1155965565-1068213215=:28965" X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.3 tagged_above=0.0 required=5.0 tests=EMAIL_ATTRIBUTION, HTML_10_20, IN_REP_TO X-Spam-Level: X-Archive-Number: 200311/41 X-Sequence-Number: 15856 --0-1155965565-1068213215=:28965 Content-Type: text/plain; charset=us-ascii Yasir Malik wrote: >You can use the age() function to find the difference between dates, and >use the extract() function to get the years, months, days, etc. >Yasir this will not work because age('25/12/1975','30/01/1986') wil return '10 year 1 month 5 days' extract only returns years -10, moths-1, days-5. this is not i want.Actual month difference between the dates are 121 months. jinu jose --------------------------------- Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard --0-1155965565-1068213215=:28965 Content-Type: text/html; charset=us-ascii


Yasir Malik <ymalik@heineken.cs.stevens-tech.edu> wrote:

>You can use the age() function to find the difference between dates, and
>use the extract() function to get the years, months, days, etc.
>Yasir

this will not work because age('25/12/1975','30/01/1986')
wil return '10 year 1 month 5 days'

extract only returns years -10, moths-1, days-5.

this is not i want.Actual month  difference between the dates are 121 months.

jinu jose

 

Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard --0-1155965565-1068213215=:28965-- From pg@rbt.ca Fri Nov 7 13:56:19 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 17A62D1B53E for ; Fri, 7 Nov 2003 13:56:19 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 13241-09 for ; Fri, 7 Nov 2003 09:55:51 -0400 (AST) Received: from tomts5-srv.bellnexxia.net (tomts5.bellnexxia.net [209.226.175.25]) by svr1.postgresql.org (Postfix) with ESMTP id 963DAD1B508 for ; Fri, 7 Nov 2003 09:55:42 -0400 (AST) Received: from [10.0.2.6] ([216.208.117.7]) by tomts5-srv.bellnexxia.net (InterMail vM.5.01.06.05 201-253-122-130-105-20030824) with ESMTP id <20031107135545.DAVU21460.tomts5-srv.bellnexxia.net@[10.0.2.6]>; Fri, 7 Nov 2003 08:55:45 -0500 Subject: Re: DateDiff in PostgreSQL From: Rod Taylor To: "George A.J" Cc: pgsql-sql@postgresql.org In-Reply-To: <20031107133409.25844.qmail@web14909.mail.yahoo.com> References: <20031107133409.25844.qmail@web14909.mail.yahoo.com> Content-Type: text/plain Message-Id: <1068213347.42603.2.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.5 Date: Fri, 07 Nov 2003 08:55:48 -0500 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/42 X-Sequence-Number: 15857 > ie, a function that returns difference of two dates(timestamp) in days > or months or year.. > > The - operator for timestamp retuns the intervel in days only. rbt=# select extract('days' from current_timestamp - '2003-01-01'::timestamp); date_part ----------- 310 (1 row)