MySQL NOW() function with high precision

Question

I am working on an C++ application that is storing its data through a remote MySQL database.

One of the features is to keep up to date with the remote data as much as possible. To achieve this, I use the NOW() function to get the last update time, and when updating records, set a last_changed field to NOW().

This works fine right now, apart form the issue that it has a maximum precision of up to one second. Causing dozens of duplicate entries, which wastes bandwidth, and having to manually remove them.

To limit most of this bloat, I would like a precision greater than this, preferably that up to microseconds (like unix gettimeofday()). However, I can not find any information about this on the documentation. What I can find is that the NOW() function is using a variable type, capable of storing up to microsecond precision (source). But when using it, all precision after the second are zeros.

Is there a way to force the Mysql server to use a higher precision timer (and taking potential performance for granted)? Or another way to achieve this?

Answer 1

In MariaDB you can use

SELECT NOW(4);

To get milisecs in your timestamp. See here.

Answer 2

http://dev.mysql.com/doc/refman/5.1/en/datetime.html

A DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) precision. Although this fractional part is recognized, it is discarded from values stored into DATETIME or TIMESTAMP columns.

Use varchar or text columns to keep the fraction.

ps: MySQL is not able to get the time with microseconds by itself

pps: except of future versions )) http://dev.mysql.com/doc/refman/5.6/en/news-5-6-4.html

MySQL now permits fractional seconds for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) precision. To define a column that includes a fractional seconds part, use the syntax type_name(fsp), where type_name is TIME, DATETIME, or TIMESTAMP, and fsp is the fractional seconds precision.

Some expressions produce results that differ from previous results. Examples: The timestamp system variable returns a value that includes a microseconds fractional part rather than an integer value.

Answer 3

So, looking into MySQL user defined functions after a tip, I was able to get this to work.

In Code::Blocks I wrote this simple library file

#include <stdlib.h>
#include <stdio.h>
#include <string.h>
typedef long long longlong;    
#include <mysql.h>
#include <ctype.h>    
#include <sys/time.h>
#include <unistd.h>
static pthread_mutex_t LOCK_hostname;
extern "C" longlong PreciseNow(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error);
extern "C" my_bool PreciseNow_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
extern "C" void PreciseNow_deinit(UDF_INIT *initid);
my_bool PreciseNow_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{
 return 0; //optional
}
void PreciseNow_deinit(UDF_INIT *initid)
{ //optional
}

longlong PreciseNow(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error)
{
   struct timeval start;
   long mtime, seconds, useconds;
   gettimeofday(&start, NULL);
   return start.tv_sec * 1000000 + start.tv_usec;
}

The only thing it does is return the current system time. I placed the lib file in '/usr/lib/mysql/plugin/'. Secondly, in the database, I excecuted the following query:

CREATE FUNCTION PreciseNow RETURNS INTEGER SONAME 'libhrt.so'

Finally I converted my timestamp columns to bigint (8 byte integers). Maybe this was not necessary but seeing the C function returns the same object type, why not...

Now when running the SQL query "SELECT PreciseNow()" I get an microsecond precise version of the build-in "NOW()"! As I have very limited knowledge about MySQL, I assume this is a perfectly legal and valid solution to my problem?