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?

score 11 · Answer 1 · answered May 27 '15 at 15:30

11

In MariaDB you can use

SELECT NOW(4);

To get milisecs in your timestamp. See here.

answered May 27 '15 at 15:30

Benvorth

7,416
8
49
70

score 6 · Answer 2 · edited Jun 20 '20 at 09:12

6

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.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jan 23 '12 at 22:37

Cheery

16,063
42
57

Thanks for your reply, but like I mentioned, although the NOW() function supports it, it does not fill the fractional part, and that's exactly what I would like, the current system time, with microseconds precision. When using NOW()+!, I get '20120123234744.000000' (up to seconds precision). – Marking Jan 23 '12 at 22:41
1

@Marking MySQL is not able to get the time with microseconds, get them in your application. – Cheery Jan 23 '12 at 22:45
What about making an UDF to make this function. As far as I understand this feature of Mysql this should allow me to use maximum precision possible right? – Marking Jan 24 '12 at 11:19

score 4 · Accepted Answer · answered Jan 24 '12 at 12:28

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?

I had some problems getting this to work at the command line in Linux so here's what ended up working for me: g++ -c -fpic libhrt.cpp && g++ -shared -lc -o libhrt.so libhrt.o && cp libhrt.so /usr/lib/mysql/plugin/ — Tim Tisdall, Oct 17 '12 at 13:45
You can use since MySQL 5.6 `NOW()` - see [documentation](https://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_now). MySQL 5.6 was released 1 week later as your answered your own question. But anyway, good work buddy +1. — Peter VARGA, Jan 21 '19 at 17:16

score 3 · Answer 4 · answered Oct 18 '17 at 19:40

3

To expand on amra. After you set the fsp on the column you can use the built in functions from MySQL.

NOW(6) or CURTIME(6) etc. It's supported in MySQL 5.6

answered Oct 18 '17 at 19:40

Melvinchi

51
2

score 2 · Answer 5 · answered Mar 13 '12 at 09:55

MySQL will support microseconds, see MySQL 5.6.4 changelog:

Fractional Seconds Handling

Incompatible Change: 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. For example:

CREATE TABLE t1 (t TIME(3), dt DATETIME(6)); The fsp value, if given, must be in the range 0 to 6. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0. (This differs from the standard SQL default of 6, for compatibility with previous MySQL versions.)

The following items summarize the implications of this change. See also Section 10.3.5, “Fractional Seconds in Time Values”.

MySQL NOW() function with high precision

5 Answers5

Linked