from the Artful MySQL Tips List

Before version 5.6.4, MySQL datetime and timestamp columns could not store fractional seconds. Here is a UDF to retrieve millisecond time from
/* Copyright (c) 2006 Wadimoff */

  returns current date and time with millisecond granularity
  as a YYYY-MM-DD HH:MM:SS.mmm string.
    gcc -shared -o -I /usr/local/include/mysql       
    cp /usr/lib                                                  
  Copy to anywhere in LD path             
    CREATE FUNCTION now_msec RETURNS STRING SONAME "";            
     SELECT NOW_MSEC();                                                       

extern "C" {
  my_bool now_msec_init( UDF_INIT *initid, UDF_ARGS *args, char *message );
  char *now_msec(
    UDF_INIT *initid,
    UDF_ARGS *args,
    char *result,
    unsigned long *length, char *is_null, char *error);

my_bool now_msec_init(UDF_INIT *initid, UDF_ARGS *args, char *message) {
  return 0;

char *now_msec( UDF_INIT *initid, UDF_ARGS *args, char *result,
                unsigned long *length, char *is_null, char *error ) {
  struct timeval tv;
  struct tm* ptm;
  char time_string[20]; /* e.g. "2006-04-27 17:10:52" */
  long milliseconds;
  char *msec_time_string = result;
  time_t t;
  gettimeofday (&tv, NULL);
  t = (time_t)tv.tv_sec;
  ptm = localtime (&t);
  strftime( time_string, sizeof ( time_string), "%Y-%m-%d %H:%M:%S", ptm );
  milliseconds = tv.tv_usec/1000;
  sprintf( msec_time_string, "%s.%03ld\n", time_string, milliseconds );
  /* Hint: */
  *length = 23;
  return( msec_time_string );

Last updated 13 Apr 2020

Return to the Artful MySQL Tips page