Attempts to emulate the postgres age() function. Returns an approximate number of years, months, days. hours, minutes, and seconds between two timestamps

==============================

// udf_age.cc
// valid_date - written by Yonah Russ
//
// Entered into the Public Domain (AWAITING RELEASE)
// 
// Usage:
//   age(datetime1, datetime2)
//
// Requires two datetime arguements:
//  
// Return value:
//   If everything works, this should return a string containing
//   the number of years, months, and days from datetime2 to datetime1
//
// Reminder:
//   Before you can use any UDF MySQL must be told about it: 
//     CREATE FUNCTION pg_age RETURNS STRING SONAME "udf_pg_age.so";
//
//   If you decide to discontinue use:
//     DROP FUNCTION pg_age;
//

#ifdef STANDARD
#include <stdio.h>
#else
#include <my_global.h>
#include <my_sys.h>
#endif
#include <string.h>
#include <time.h>
#include <math.h>
#include <mysql.h>
#include <m_ctype.h>

extern "C" {
   // functions for chkdate
my_bool pg_age_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
char *pg_age(UDF_INIT *initid, UDF_ARGS *args, char *result,
      unsigned long *length, char *is_null, char *error);
}

#define MAXLEN 21

my_bool pg_age_init(UDF_INIT *initid, UDF_ARGS *args, char *message) {
  if(args->arg_count < 2) {
    strcpy(message,"valid_date: two required args");
    return 1;
  }
  initid->max_length = MAXLEN;             // 0000-00-00 00:00:00
  initid->maybe_null = 1;
  /* if (args->arg_type[0] != STRING_RESULT ||
      args->arg_type[1] != STRING_RESULT)
    {
      strcpy(message,"age() requires two datetime strings");
      return 1;
    }
  */

  return 0;
}

char *pg_age(UDF_INIT *initid, UDF_ARGS *args, char *result,
unsigned long *length, char *is_null, char *error) {
  struct tm a_time, b_time;
  time_t a_time_t, b_time_t;
  unsigned int timediff;
  int leap = 0, max_days, x, y;
  char tmp_a[30], tmp_b[30], *save_args = NULL;
  /*
  if(args->args[0] == NULL || (args->arg_count > 1 && args->args[1] == NULL)) {
    *is_null = 1;
    return(result);
  }
  */
  memset(&a_time, 0, sizeof(struct tm));      
  memset(&b_time, 0, sizeof(struct tm));      

  if(strchr(args->args[0], '-') == NULL) {   // its all numbers
    memset(tmp_a, 0, sizeof(tmp_a));
    save_args = args->args[0];
    if(args->lengths[0] == 6 || args->lengths[0] == 7) {  // 2 digit year
      sprintf(tmp_a, "%c%c-%c%c-%c%c", args->args[0][0],
              args->args[0][1], args->args[0][2], args->args[0][3],
              args->args[0][4], args->args[0][5]);
    }
    else {                                             // 4 digit year
      for(x = y = 0; x < args->lengths[0] && x < 14; x++) {
        tmp_a[y++] = args->args[0][x];
        if(y == 4 || y == 7)
           tmp_a[y++] = '-';
        else if(y == 10)
           tmp_a[y++] = ' ';
        else if(y == 13 || y == 16)
           tmp_a[y++] = ':'; 
      }       
    }
    args->args[0] = tmp_a;
    args->lengths[0] = strlen(args->args[0]);
  }

                                             // check 4 digit then 2
  if(strptime(args->args[0], " %Y-%m-%d %T", &a_time) != NULL ||
     strptime(args->args[0], " %y-%m-%d %T", &a_time) != NULL ||
     (a_time.tm_year && a_time.tm_mon)) {
    if(!a_time.tm_year) {
      *is_null = 1;
      return(result);
    }
    a_time_t = mktime(&a_time);
  }
    /**********************************************************
               move 2nd datetime into tm struct
    ***********************************************************/

  if(strchr(args->args[1], '-') == NULL) {   // its all numbers
    memset(tmp_b, 0, sizeof(tmp_b));
    save_args = args->args[1];
    if(args->lengths[1] == 6 || args->lengths[1] == 7) {  // 2 digit year
      sprintf(tmp_b, "%c%c-%c%c-%c%c", args->args[1][0],
              args->args[1][1], args->args[1][2], args->args[1][3],
              args->args[1][4], args->args[1][5]);
    }
    else {                                             // 4 digit year
      for(x = y = 0; x < args->lengths[1] && x < 14; x++) {
        tmp_b[y++] = args->args[1][x];
        if(y == 4 || y == 7)
           tmp_b[y++] = '-';
        else if(y == 10)
           tmp_b[y++] = ' ';
        else if(y == 13 || y == 16)
           tmp_b[y++] = ':'; 
      }       
    }
    args->args[1] = tmp_b;
    args->lengths[1] = strlen(args->args[1]);
  }

                                             // check 4 digit then 2
  if(strptime(args->args[1], " %Y-%m-%d %T", &b_time) != NULL ||
     strptime(args->args[1], " %y-%m-%d %T", &b_time) != NULL ||
     (b_time.tm_year && b_time.tm_mon)) {
    if(!b_time.tm_year) {
      *is_null = 1;
      return(result);
    }
    b_time_t = mktime(&b_time);
  }
  memset(tmp_a, 0, sizeof(tmp_a));
  timediff=difftime(a_time_t,b_time_t);
  a_time.tm_sec=timediff%60;
  a_time.tm_min=(unsigned int)floor(timediff/60)%60;
  a_time.tm_hour=(unsigned int)floor(floor(timediff/60)/60)%24;
  a_time.tm_mday=(unsigned int)floor(floor(floor(timediff/60)/60)/24)%30;
  a_time.tm_mon=(unsigned int)floor(floor(floor(floor(timediff/60)/60)/24)/30)%12;
  a_time.tm_year=(unsigned int)floor(floor(floor(floor(floor(timediff/60)/60)/24)/30)/12);
  sprintf(tmp_a,"%d years %d mons %d days %02d:%02d:%02d", a_time.tm_year, a_time.tm_mon, a_time.tm_mday,
   a_time.tm_hour, a_time.tm_min, a_time.tm_sec);
  result=tmp_a;
  *length=strlen(result);
  return(result);
}

====================================================

http://empyrean.lib.ndsu.nodak.edu/~nem/mysql/udf/