GBase 8a 实现oracle里months_between功能函数

根据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)=date

SQL形式

推荐方案,虽然看上去有点多,但无性能损失。

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

}