In the yet to be released MySQL 5.6.6 DMR, there has been a change to the restriction of just one TIMESTAMP column with the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP syntax. It is now possible for any TIMESTAMP to have either column defintion.
More information at http://dev.mysql.com/doc/refman/5.6/en/news-5-6-5.html
I’ve been thinking a lot about invalid and zero temporal values and how to detect them with MySQL date and time functions because mk-table-checksum has to handle “everything” correctly and efficiently. The requirements are complex because we have to take into account what MySQL allows to be stored verses what it allows to be used in certain operations and functions, how it sorts a mix of real and invalid temporal values for MIN() and MAX(), how to detect a temporal value as equivalent to zero, and how different MySQL versions might affect any of the aforementioned.
At base, the four guiding requirements are:
My tests cases for invalid temporal values are:
00:00:6000:60:00999-00-00999-01-010000-00-002009-00-002009-13-00999-00-00 00:00:00999-01-01 00:00:000000-00-00 00:00:001000-00-00 00:00:002009-00-00 00:00:002009-13-00 00:00:002009-05-26 00:00:602009-05-26 00:60:002009-05-26 24:00:00And my test cases for first real temporal values are:
00:00:0000:00:011000-01-012009-01-011000-01-01 00:00:002009-01-01 00:00:00And there is only one real zero-equivalent temporal value: 00:00:00.
So the first requirement is to find a MySQL function that returns NULL for all those invalid values, and that function is TO_DAYS with one exception:
mysql> SELECT TO_DAYS('999-01-01 00:00:00');
+-------------------------------+
| TO_DAYS('999-01-01 00:00:00') |
+-------------------------------+
| 364878 |
+-------------------------------+
That date is only valid if years before 1000 are handled but the MySQL manual says that,
TO_DAYS() is not intended for use with values that precede the advent of the Gregorian calendar (1582)
so we’re already way past the limit of its intended use and, moreover, the supported lower limit of a date or datetime is 1000-01-01, so says the manual. It’s reasonable to not bother with pre-year 1000 dates so I’ll overlook this.
Excepting pre-year 1000 dates, TO_DAYS() returns NULL for all the invalid values. By contrast, UNIX_TIMESTAMP() returns zero for all the invalid values and TIME_TO_SEC() returns a mix of NULL, zero, and values. So the apparent winner for requirement #1 is TO_DAYS(), but…
Requirement #2 complicates the issue because the time 00:00:00 is valid and zero-equivalent but TO_DAYS() returns NULL for it. We need a hack that handles all the cases, and here it is:
SELECT IF(TIME_FORMAT(?,'%H:%i:%s')=?, TIME_TO_SEC(?), TO_DAYS(?))
That says, basically: if the value is a time then evaluate it with TIME_TO_SEC(), else evaluate it with TO_DAYS(). It works so well in fact that it satisfies all four requirements. 00:00:00 evaluates to zero, all the invalid values evaluate to NULL, and all the valid values evaluate to various non-null values. I have to use TIME_FORMAT() instead of just TIME() because TIME() wasn’t introduced until MySQL v4.1 (fourth requirement).
The hack works because of this (substituting TIME() for TIME_FORMAT()):
mysql> SELECT TIME('00:00:00');
+------------------+
| TIME('00:00:00') |
+------------------+
| 00:00:00 |
+------------------+
mysql> SELECT TIME('00-00-00');
+------------------+
| TIME('00-00-00') |
+------------------+
| 00:00:00 |
+------------------+
mysql> SELECT TIME('2010-05-26');
+--------------------+
| TIME('2010-05-26') |
+--------------------+
| 00:20:10 |
+--------------------+
mysql> SELECT TIME('2010-05-26 10:10:10');
+-----------------------------+
| TIME('2010-05-26 10:10:10') |
+-----------------------------+
| 10:10:10 |
+-----------------------------+
As you can see, TIME() (or TIME_FORMAT()) returns the exact same value if the given value is a time, otherwise it interprets the value–which is a date or datetime–as a time causing it to return a different value than the given value. Thus we discern time values from date and datetime values and evaluate them separately with TIME_TO_SEC().
I tested on MySQL v4.0, 4.1, 5.0 and 5.1 and all pass. The only difference is 4.0 verses the others for the pre-year 1000 dates, but I’m ignoring these anyway.
Of course all the preceding could have been accomplished in code by looking at the column type and choosing the correct MySQL function to evaluate the value and check if it’s zero-equivalent, but I was curious to see if it could be done using only MySQL since, after all, it is MySQL that permits these silly, invalid temporals values.
If you know a simpler, more elegant solution that meets the four requirements and passes all the tests, please share!
This article shows how to create a MySQL-plugin that can be used to create a function which can in turn be used in stored procedures. The function will produce an integer value representing the time (to the nearest usec).
I’m working on an article for conflict detection/resolution when using MySQL Cluster asynchronous replication which requires an integer column to store a timestamp for comparison purposes. In fact, it doesn’t actually need the timestamp to represent an absolute or even a relative point in time – all it cares about is that the if the function is called twice on 2 different hosts that the 2nd call will always result in a larger number than the 1st. Obviously, in a production environment the times on the 2 hosts would need to be kept in sync.
#include <mysql.h>
#include <sys/time.h>
my_bool inttime_init(UDF_INIT *initid,UDF_ARGS *args, char *message) {
return 0;
}
void inttime_deinit(UDF_INIT *initid) {};
unsigned long int inttime(UDF_INIT *initid, UDF_ARGS *args, char *result,
unsigned long *length, char *is_null, char *error)
{
struct timeval tv;
gettimeofday(&tv,(void *)0);
return ((double)tv.tv_usec)+tv.tv_sec*1000000;
}
[billy@ws1 timestamp]$ gcc -I/usr/local/mysql/include/ -fPIC -shared -o inttime.so inttime.c [billy@ws1 timestamp]$ cp inttime.so /usr/local/mysql/lib/plugin/
mysql> create function inttime RETURNS REAL SONAME 'inttime.so';
mysql> select inttime();select inttime(); +------------------+ | inttime() | +------------------+ | 1250080524270706 | +------------------+ 1 row in set (0.00 sec) +------------------+ | inttime() | +------------------+ | 1250080524270833 | +------------------+ 1 row in set (0.00 sec)
Note that the results are different and the second result is always larger than the first – function is fit for purpose 
mysql> CREATE TRIGGER updateTAB1 BEFORE UPDATE ON TAB1 FOR EACH ROW SET NEW.ts = inttime();
I used the c code found at http://lists.mysql.com/internals/33702 as the starting point.