根据Oracle的规定，months_bwtween返回2个日期相差的月数，本文介绍在GBase 8a数据库集群里如何实现该功能函数。
目录导航
分析
根据Oracle规定，如果日期1晚于日期2，则返回正数，否则返回负数。
如果2个日期的天部分相同，或者都是所在月的最后一天，那么返回的是一个整数(小数部分为0）。
否则以31天为基数，计算小数部分。
实现方法
通过year和month函数可以分别拿到日期的年和月，其中每年是21个月，所以整数部分计算为
(year(date1)-year(date2))*12
+
(month(date1)-month(date2))小数部分，需要先判断是否为所在月的最后一天，可以通过如下方式
last_day(date)=dateSQL形式
推荐方案，虽然看上去有点多，但无性能损失。
select
(year('2020-01-30')-year('2020-02-29'))*12 /* 年差*12 */
+
(month('2020-01-30')-month('2020-02-29')) /* 月差 */
+
if(last_day('2020-01-30')='2020-01-30' and last_day('2020-02-29')='2020-02-29' /* 日差，如果都是所在月最后一天，则返回0 */
,0
,(day('2020-01-30')-day('2020-02-29'))/31
)
mm
from dual; 执行结果
gbase> select (year('2020-01-30')-year('2020-02-29'))*12 + (month('2020-01-30')-month('2020-02-29')) + if(last_day('2020-01-30')='2020-01-30' and last_day('2020-02-29')='2020-02-29' ,0 ,(day('2020-01-30')-day('2020-02-gbase> select
-> (year('2020-01-30')-year('2020-02-29'))*12 /* 年差*12 */
-> +
-> (month('2020-01-30')-month('2020-02-29')) /* 月差 */
-> +
-> if(last_day('2020-01-30')='2020-01-30' and last_day('2020-02-29')='2020-02-29' /* 日差，如果都是所在月最后一天，则返回0 */
-> ,0
-> ,(day('2020-01-30')-day('2020-02-29'))/31
-> )
-> mm
-> from dual;
+---------+
| mm |
+---------+
| -0.9677 |
+---------+
1 row in set (Elapsed: 00:00:00.01)
自定义函数UDF格式
如果少量使用，且不是性能关键。
drop function if exists months_between;
delimiter //
create function months_between(date1 date,date2 date) returns decimal(64,9)
begin
declare dd decimal(64,9);
select
(year(date1)-year(date2))*12 /* 年差*12 */
+
(month(date1)-month(date2)) /* 月差 */
+
if(last_day(date1)=date1 and last_day(date2)=date2 /* 日差，如果都是所在月最后一天，则返回0 */
,0
,(day(date1)-day(date2))/31
)
into dd;
return dd;
end //
delimiter ;执行效果
采用了和Oracle相同的写法。
gbase> select months_between(
-> to_date('2020-01-28','yyyy-mm-dd'),
-> to_date('2020-02-29','yyyy-mm-dd')
-> ) a
-> from dual;
+--------------+
| a |
+--------------+
| -1.032258064 |
+--------------+
1 row in set (Elapsed: 00:00:00.01)
C编写的UDF方案
该方案采用C来实现，性能有保障，但需要额外的编译和部署，建议已经使用了CUDF或者大量使用且性能要求高的情况使用。
C的UDF编译和部署方法，请参考GBase的产品手册。再次提醒，不完善的C代码，有可能导致内存泄漏，宕机等问题。
如下只是样例，如果在正式项目使用，请做大数据量的长时间测试。
#include <gbase_global.h>
#include <gbase_sys.h>
#include <m_string.h>
#include <gbase.h>
#include <string.h>
#include <time.h>
#include <gbase.h>
int is_end(struct tm timestru)
{
unsigned char x[13]={0,31,28,31,30,31,30,31,31,30,31,30,31};
int y = timestru.tm_year;
int m = timestru.tm_mon;
int d = timestru.tm_mday;
if(y%4==0 && y%100!=0 || y%400==0)
x[2]=29;
if( m == 0)
return 0;
else
if(x[m] == d)
return 1;
else
return 0;
}
void strtodatetime(char *timeptr,struct tm *timestru)
{
char yy[4]={'\0'},mm[2]={'\0'},day[2]={'\0'};
memset(yy,0,4);
while(*timeptr ==' ' || *timeptr=='\t')
timeptr++;
strncpy(yy,timeptr,4);
timeptr+=5;
strncpy(mm,timeptr,2);
timeptr+=3;
strncpy(day,timeptr,2);
timestru->tm_year=atoi(yy);
timestru->tm_mon= atoi(mm);
timestru->tm_mday= atoi(day);
}
my_bool months_between_init(UDF_INIT *initid,UDF_ARGS *args,char *message)
{
if(args->arg_count != 2)
{
strcpy(message,"months_between() must be three arguments");
return 1;
}
initid->extension=NULL;
initid->maybe_null=1;
return 0;
}
double months_between(UDF_INIT *initid,UDF_ARGS *args,char *result,unsigned long *length,char *is_null,char *error)
{
//FILE *fp;
//fp = fopen("/home/gbase/udf/oo","w+");
char v_first[20]={'\0'};
char v_second[20]={'\0'} ;
struct tm first_tm;
struct tm second_tm;
int v_first_end=0;
int v_second_end =0;
double vm_result=0;
strcpy(v_first,args->args[0]);
strcpy(v_second,args->args[1]);
//fprintf(fp," return 1 %s, %s \n",v_first, v_second);
//fflush(fp);
strtodatetime(v_first,&first_tm);
strtodatetime(v_second,&second_tm);
//fprintf(fp," return 2 %d, %d,%d \n",first_tm.tm_year, first_tm.tm_mon,first_tm.tm_mday);
//fprintf(fp," return 3 %d, %d,%d \n",second_tm.tm_year, second_tm.tm_mon,second_tm.tm_mday);
//fflush(fp);
v_first_end= is_end(first_tm);
v_second_end= is_end(second_tm);
//fprintf(fp," return 2 %d, %d \n",v_first_end,v_second_end);
//fflush(fp);
if(v_first_end && v_second_end)
vm_result= (first_tm.tm_year-second_tm.tm_year)*12 +(first_tm.tm_mon-second_tm.tm_mon);
else
vm_result= (first_tm.tm_year-second_tm.tm_year)*12 +(first_tm.tm_mon-second_tm.tm_mon)+(first_tm.tm_mday-second_tm.tm_mday)/31.0 ;
//fclose(fp);
return vm_result;
}
void months_between_deinit(UDF_INIT *initid)
{
}