From: Erik Aronesty <erik@zoneedit.com>
To: pgsql-sql@postgresql.org
Subject: datediff script
Date: Wed, 21 Apr 2004 10:13:25 -0400
Message-ID: <04af01c427aa$ddb75640$cd855140@h2ttp> (raw)
References: <GOEGKICPFOPNLIEIHGFJGEPBCKAA.postgresql@thegatelys.com>
<000601c4279b$04c12f00$6400a8c0@Dell4500>
This is a not-quite complete implementation of the SY/MS sql datediff. The
months_between function can be extrapolated from it as well. I looked for
it on forums, etc. and all I found were people complaining about the lack of
an example. Please post fixes/changes or a link to a better one... if you
know of it.
CREATE OR REPLACE FUNCTION public.datediff(varchar, timestamp, timestamp)
RETURNS int4 AS
'
DECLARE
arg_mode alias for $1;
arg_d2 alias for $2;
arg_d1 alias for $3;
BEGIN
if arg_mode = \'dd\' or arg_mode = \'d\' or arg_mode = \'y\' or arg_mode =
\'dy\' or arg_mode = \'w\' then
return cast(arg_d1 as date) - cast(arg_d2 as date);
elsif arg_mode = \'ww\' then
return ceil( ( cast(arg_d1 as date) - cast(arg_d2 as date) ) / 7.0);
elsif arg_mode = \'mm\' OR arg_mode = \'m\' then
return 12 * (date_part(\'year\',arg_d1) - date_part(\'year\',arg_d2))
+ date_part(\'month\',arg_d1) - date_part(\'month\',arg_d2)
+ case when date_part(\'day\',arg_d1) >
date_part(\'day\',arg_d2)
then 0
when date_part(\'day\',arg_d1) =
date_part(\'day\',arg_d2) and cast(arg_d1 as time) >= cast(arg_d2 as time)
then 0
else -1
end;
elsif arg_mode = \'yy\' OR arg_mode = \'y\' OR arg_mode = \'yyyy\' then
return (cast(arg_d1 as date) - cast(arg_d2 as date)) / 365;
end if;
END;
'
LANGUAGE 'plpgsql' VOLATILE;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: erik@zoneedit.com
Subject: Re: datediff script
In-Reply-To: <04af01c427aa$ddb75640$cd855140@h2ttp>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox