Get interval in months

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

On Sat, Nov 8, 2008 at 4:12 AM, Gerhard Heift <
ml-postgresql-20081012-3518@gheift.de> wrote:

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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFJFVgNa8fhU24j2fkRAq0ZAJ925CHwchm/kZCwTTDcZF/QVXAlewCeMYGL
h3y0pxtiOiDV7pExYiEcSZ0=
=84G4
-----END PGP SIGNATURE-----

--
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�

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'');

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 **********

ERROR: syntax error at or near "select"
SQL state: 42601
Context: SQL statement in PL/PgSQL function "pr_gettimeinterval" near line 7

How can i to put my variable "v_timebreak" into function ? so i can send dynamic value for v_timebreak.

please help, thank you.

Alam Surya

----- Original Message -----
From: "Andreas Kretschmer" <akretschmer@spamfence.net>
To: <pgsql-general@postgresql.org>
Cc: "Gerhard Heift" <ml-postgresql-20081012-3518@gheift.de>
Sent: Saturday, November 08, 2008 19:59
Subject: Re: [GENERAL] Get interval in months

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)) 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

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 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