Before version 5.6.4, MySQL datetime and timestamp columns could not store fractional seconds. Here is a UDF to retrieve millisecond time from http://bugs.mysql.com/bug.php?id=8523
/* Copyright (c) 2006 Wadimoff */
#include <my_global.h>
#include <my_sys.h>
#include <mysql.h>
#include <stdio.h>
#include <sys/time.h>
#include <time.h>
#include <unistd.h>
/*
now_msec()
returns current date and time with millisecond granularity
as a YYYY-MM-DD HH:MM:SS.mmm string.
compile:
gcc -shared -o now_msec.so now_msec.cc -I /usr/local/include/mysql
cp now_msec.so /usr/lib
Copy to anywhere in LD path
Run:
CREATE FUNCTION now_msec RETURNS STRING SONAME "now_msec.so";
Test:
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: http://www.mysql.ru/docs/man/UDF_return_values.html */
*length = 23;
return( msec_time_string );
}
|
|