Обсуждение: Get interval in months

Hello,

I want to get an interval in months from two dates:

SELECT '2008-02-01'::timestamp - '2008-01-01'::timestamp AS '1 month'

Here I want '1 month' and not '31 days' as answer.

How can I do this?

Regards,
  Gerhard

Gerhard,

Check out:

http://www.postgresql.org/docs/8.3/interactive/functions-datetime.html

In particular, look at 'age()' or 'justify_days()', but  I think age() is the one you want.


SELECT age('2008-02-01'::timestamp, '2008-01-01'::timestamp) AS "1 month"

Produces:  "1 mon"

SELECT justify_days('2008-02-01'::timestamp - '2008-01-01'::timestamp) AS "1 month"

Produces "1 mon 1 day"

One thing to be aware of is the different behaviors in these functions. age() uses the actual number of days in the month when representing the time, where justify_days() always assumes 30 days. In your example, January has 31 days, not 30, which is why the difference of '1 day' in the output. It's a little more obvious in this example, where both queries give a number of days:

SELECT age('2008-02-05'::timestamp, '2008-01-01'::timestamp) AS "1 month"

Produces: "1 mon 4 days"

SELECT justify_days('2008-02-05'::timestamp - '2008-01-01'::timestamp) AS "1 month"

Produces: "1 mon 5 days"

Had I used June into July, these would have agreed at '1 mon 4 days'.

Dave










--
David Spadea
President
Spadea Enterprises, Inc
http://www.spadea.net

Gerhard Heift <ml-postgresql-20081012-3518@gheift.de> schrieb:

> Hello,
>
> I want to get an interval in months from two dates:
>
> SELECT '2008-02-01'::timestamp - '2008-01-01'::timestamp AS '1 month'
>
> Here I want '1 month' and not '31 days' as answer.

How long is a month? 28 days? 31 days? But okay, i will try:

test=*# SELECT justify_days('2008-02-01'::timestamp - '2008-01-01'::timestamp) ;
 justify_days
--------------
 1 mon 1 day
(1 row)

Is this okay for you? PostgreSQL assume 30 days per month.



Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

> Gerhard Heift <ml-postgresql-20081012-3518@gheift.de> schrieb:
>
>> Hello,
>>
>> I want to get an interval in months from two dates:
>>
>> SELECT '2008-02-01'::timestamp - '2008-01-01'::timestamp AS '1 month'
>>
>> Here I want '1 month' and not '31 days' as answer.
>
> How long is a month? 28 days? 31 days? But okay, i will try:
>
> test=*# SELECT justify_days('2008-02-01'::timestamp - '2008-01-01'::timestamp) ;
> justify_days
> --------------
> 1 mon 1 day
> (1 row)
>
> Is this okay for you? PostgreSQL assume 30 days per month.
>
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect.                              (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly."   (unknown)
> Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

am  Mon, dem 10.11.2008, um 12:06:04 +0700 mailte dbalinglung folgendes:
> Dear Expert,

First, please create a new thread for a new question.


>
> I have a function to getting time interval bellow :
>
> create or replace function scmaster.pr_gettimeinterval(time without time zone,
> time without time zone, numeric(5,2)) returns char(10) As '
> declare v_timein    alias for $1;
>         v_timeout   alias for $2;
>         v_timebreak alias for $3;
>         v_output    char(10);
> begin
>   raise notice ''-- BOF --'';
>   v_output := select ((v_timeout - v_timein) - interval ''v_timebreak
> minutes'');
>
>   raise notice ''-- EOF --'';
> return v_output;
> end;'
> language plpgsql;
>
>
> and when i compilled from pgAdmin, i got some error message bellow :
>
> ERROR:  syntax error at or near "select"
> LINE 1: SELECT  select (( $1  -  $2 ) - interval 'v_timebreak minute...
>                 ^
> QUERY:  SELECT  select (( $1  -  $2 ) - interval 'v_timebreak minutes')
> CONTEXT:  SQL statement in PL/PgSQL function "pr_gettimeinterval" near line 7
>
> ********** Error **********


Rewrite the line

v_output := select ((v_timeout - v_timein) ...

to:

select into v_output ((v_timeout - v_timein) ...


(not tested)


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

>From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
>
> First, please create a new thread for a new question.
>
>

Sorry, ok i create new thread

>
> Rewrite the line
>
> v_output := select ((v_timeout - v_timein) ...
>
> to:
>
> select into v_output ((v_timeout - v_timein) ...
>
>
> (not tested)
>

please tested your answer so you can find out the result from your own
sugestion, but thank you for your attention.


Thanks,


Alam Surya

am  Mon, dem 10.11.2008, um 13:13:05 +0700 mailte dbalinglung folgendes:
> >From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
> >
> >First, please create a new thread for a new question.
> >
> >
>
> Sorry, ok i create new thread

his is still the old thread, see the References-Headers.

> >select into v_output ((v_timeout - v_timein) ...
> >
> >
> >(not tested)
> >
>
> please tested your answer so you can find out the result from your own
> sugestion, but thank you for your attention.

Done, works for me.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

On Mon, Nov 10, 2008 at 12:06:04PM +0700, dbalinglung wrote:
> Dear Expert,
>
> I have a function to getting time interval bellow :
>
> create or replace function scmaster.pr_gettimeinterval(time without time zone, time without time zone, numeric(5,2))
returnschar(10) As ' 
> declare v_timein    alias for $1;
>         v_timeout   alias for $2;
>         v_timebreak alias for $3;
>         v_output    char(10);
> begin
>   raise notice ''-- BOF --'';
>   v_output := select ((v_timeout - v_timein) - interval ''v_timebreak minutes'');

You've got the brackets wrong here, you need brackets around the whole
SELECT statement a bit like subselects.  Also, the INTERVAL literal
is wrong.  At the moment, you're telling PG to interpret the string
'v_timebreak minutes' as an interval which will fail.  You can either
concatenate the numeric value of the "v_timebreak" column with the
string ' minutes' to get a valid string that can be interpreted as an
INTERVAL; or a better option would be to create a fixed interval and
then multiply it by your numeric value.

>
>   raise notice ''-- EOF --'';
> return v_output;
> end;'
> language plpgsql;
>
>
> and when i compilled from pgAdmin, i got some error message

I'd probably write it like this:

  CREATE OR REPLACE FUNCTION cmaster.pr_gettimeinterval(
      _timein TIME, _timeout TIME, _timebreak NUMERIC)
    RETURNS TEXT LANGUAGE plpgsql AS
  $$
    DECLARE
      _output TEXT;
    BEGIN
      _output := (SELECT _timeout - _timein - INTERVAL '1 minute' * _timebreak);
      RETURN _output;
    END
  $$;

The operator precedence is such that this will work without brackets,
but you can put them in if you want.  The "_output" variable is
also unneeded, you can just RETURN the SELECT statement in one line
(i.e. RETURN (SELECT 1) works), but I left it in because I thought you
may want to do other things with it.


  Sam

DONE........

thank you very much.


Best Regards,


Alam Surya


----- Original Message -----
From: "Sam Mason" <sam@samason.me.uk>
To: <pgsql-general@postgresql.org>
Sent: Monday, November 10, 2008 18:50
Subject: Re: [GENERAL] Get interval in months


> On Mon, Nov 10, 2008 at 12:06:04PM +0700, dbalinglung wrote:
>> Dear Expert,
>>
>> I have a function to getting time interval bellow :
>>
>> create or replace function scmaster.pr_gettimeinterval(time without time
>> zone, time without time zone, numeric(5,2)) returns char(10) As '
>> declare v_timein    alias for $1;
>>         v_timeout   alias for $2;
>>         v_timebreak alias for $3;
>>         v_output    char(10);
>> begin
>>   raise notice ''-- BOF --'';
>>   v_output := select ((v_timeout - v_timein) - interval ''v_timebreak
>> minutes'');
>
> You've got the brackets wrong here, you need brackets around the whole
> SELECT statement a bit like subselects.  Also, the INTERVAL literal
> is wrong.  At the moment, you're telling PG to interpret the string
> 'v_timebreak minutes' as an interval which will fail.  You can either
> concatenate the numeric value of the "v_timebreak" column with the
> string ' minutes' to get a valid string that can be interpreted as an
> INTERVAL; or a better option would be to create a fixed interval and
> then multiply it by your numeric value.
>
>>
>>   raise notice ''-- EOF --'';
>> return v_output;
>> end;'
>> language plpgsql;
>>
>>
>> and when i compilled from pgAdmin, i got some error message
>
> I'd probably write it like this:
>
>  CREATE OR REPLACE FUNCTION cmaster.pr_gettimeinterval(
>      _timein TIME, _timeout TIME, _timebreak NUMERIC)
>    RETURNS TEXT LANGUAGE plpgsql AS
>  $$
>    DECLARE
>      _output TEXT;
>    BEGIN
>      _output := (SELECT _timeout - _timein - INTERVAL '1 minute' *
> _timebreak);
>      RETURN _output;
>    END
>  $$;
>
> The operator precedence is such that this will work without brackets,
> but you can put them in if you want.  The "_output" variable is
> also unneeded, you can just RETURN the SELECT statement in one line
> (i.e. RETURN (SELECT 1) works), but I left it in because I thought you
> may want to do other things with it.
>
>
>  Sam
>

On Sat, Nov 08, 2008 at 07:44:45AM -0500, David Spadea wrote:
> Gerhard,
>
> Check out:
>
> http://www.postgresql.org/docs/8.3/interactive/functions-datetime.html
>
> In particular, look at 'age()' or 'justify_days()', but  I think age() is the
> one you want.
>
>
> SELECT age('2008-02-01'::timestamp, '2008-01-01'::timestamp) AS "1 month"
>
> Produces:  "1 mon"

This was the function I searched. Thank you.

Gerhard