Mysql function to generate millisecond precision timestamp as a BIGINT(13)

Question

I am aware that mysql does not support storing timestamp columns with millisecond precision.

My question: is there a mysql function I could write that will output the current time as a BIGINT(13) to millisecond precision.

For example, as now() outputs a timestamp:

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2010-10-27 11:24:23 |
+---------------------+

I would like to write a mysql function, say ts() that outputs a bigint(13) e.g.

mysql> select ts();
+---------------------+
| ts()               |
+---------------------+
| 1288172185517      |
+---------------------+

My reasons for wanting this is to be able to populate the default value of a column with the value of the function ts()

e..g

`MY_TIMESTAMP_COLUMN` BIGINT(13) DEFAULT ts(),

Answer 1

The article link to which you have posted refers to an implementation of such a function:

http://bugs.mysql.com/bug.php?id=8523

If you replace a call to sprinf with tv.tv_sec * 1000000 + tv.tv_usec, the function will return the local time as a number of microseconds since the epoch.