Message-ID: <51DE4E51.3050702@archidevsys.co.nz>
Date: Thu, 11 Jul 2013 18:18:57 +1200
From: Gavin Flower <GavinFlower@archidevsys.co.nz>
Organization: ArchiDevSys
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:17.0) Gecko/20130620 Thunderbird/17.0.7
MIME-Version: 1.0
To: Huan Ruan <huan.ruan.it@gmail.com>
CC: pgsql-sql@postgresql.org
Subject: Re: DateDiff() function
References: 
 <CAD1stZuwG5fx4YJ+dyFRuxEaCmYo+nUWJvwuP-=FUTEA463PdQ@mail.gmail.com>
In-Reply-To: 
 <CAD1stZuwG5fx4YJ+dyFRuxEaCmYo+nUWJvwuP-=FUTEA463PdQ@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------090608050801030705080508"
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

This is a multi-part message in MIME format.
--------------090608050801030705080508
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 11/07/13 17:17, Huan Ruan wrote:
> Hi Guys
>
> We are migrating to Postgres. In the current system, we use datediff() 
> function to get the difference between two dates, e.g. datediff 
> (month, cast('2013-01-01' as timestamp), cast('2013-02-02' 
> as timestamp) returns 1.
>
> I understand that Postgres has Interval data type so I can achieve the 
> same with Extract(month from Age(date1, date2)). However, I try to 
> make it so that the existing SQL can run on both databases without 
> changes. One possible way is to add a datediff function to Postgres, 
> but the problem is that month/day/year etc is a keyword not a string 
> like 'month'. I noticed that Postgres seems to convert Extract(month 
> from current_timestamp) to date_part('month', current_timestamp), you 
> can also do Extract('month' from current_timestamp). So it seems 
> internally, Postgres can do the mapping from month to 'month'. I was 
> wondering if there is a way for me to do the same for the datediff() 
> function? Any other ideas?
>
> Thanks
> Huan
Purely out of curiosity, could you tell us what database software you 
are moving from, as well as a rough idea of the size of database, type 
and volume of database queries?

It would also be of interest to know what postgres features in 
particular were the biggest motivations for change, and any aspects that 
gave you cause for concern - obviously overall, it must have come across 
as being better .

I strongly suspect that answering these questions will have no direct 
bearing on how people will answer your query! :-)


Cheers,
Gavin

--------------090608050801030705080508
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    <div class="moz-cite-prefix">On 11/07/13 17:17, Huan Ruan wrote:<br>
    </div>
    <blockquote
cite="mid:CAD1stZuwG5fx4YJ+dyFRuxEaCmYo+nUWJvwuP-=FUTEA463PdQ@mail.gmail.com"
      type="cite">
      <div dir="ltr">Hi Guys
        <div><br>
        </div>
        <div style="">We are migrating to Postgres. In the current
          system, we use datediff() function to get the difference
          between two dates, e.g. datediff (month, cast('2013-01-01' as
          timestamp), cast('2013-02-02' as&nbsp;timestamp) returns 1.</div>
        <div style=""><br>
        </div>
        <div style="">I understand that Postgres has Interval data type
          so I can achieve the same with Extract(month from Age(date1,
          date2)). However, I try to make it so that the existing SQL
          can run on both databases without changes. One possible way is
          to add a datediff function to Postgres, but the problem is
          that month/day/year etc is a keyword not a string like
          'month'. I noticed that Postgres seems to convert
          Extract(month from current_timestamp) to date_part('month',
          current_timestamp), you can also do Extract('month' from
          current_timestamp). So it seems internally, Postgres can do
          the mapping from month to 'month'. I was wondering if there is
          a way for me to do the same for the datediff() function? Any
          other ideas?</div>
        <div style=""><br>
        </div>
        <div style="">Thanks</div>
        <div style="">Huan</div>
      </div>
    </blockquote>
    <font size="-1">Purely <font size="-1">out o<font size="-1">f
          curiosity, could you tell us what database software you a<font
            size="-1">re moving from</font></font></font></font>, as
    well as a rough idea of the size of database, type and volume of
    database queries?<br>
    <br>
    It would also be of interest to know what postgres features in
    particular were the biggest motivations for change, and any aspects
    that gave you cause for concern - obviously overall, it must have
    come across as being better .<br>
    <br>
    I strongly suspect that answering these questions will have no
    direct bearing on how people will answer your query! :-)<br>
    <br>
    <br>
    Cheers,<br>
    Gavin<br>
  </body>
</html>

--------------090608050801030705080508--