Description:
I Suggest the following Enyhancements to MySQL.
The Create Table ... Column ...
ON UPDATE and DEFAULT
constructs need to be enhanced to allow other time functions,
than just the
CURRENT_TIMESTAMP
function.
Consider the following two programming problems
that would be solved better with this enhancement to MySQL.
Problem 1)
I want to have Databases in different countries,
and thus in different time zones.
I want to be able to select records from a table in DB2,
based on a time greater than records from a table in DB1.
Since the time zone may differ, what I really want to do is:
CREATE TABLE timeclock (
id Integer UNSIGNED NOT NULL DEFAULT 0,
ts TimeStamp
ON UPDATE UNIX_TIMESTAMP
DEFAULT UNIX_TIMESTAMP,
PRIMARY KEY (id)
) TYPE=MyISAM;
Unfortunately, I have to rather use CURRENT_TIMESTAMP
and keep another DB table of time zone differences,
in order to make sure the code works right.
Sure, I could have the calling code allways set the timestamp.
However, it is a far better programming practice
to have the DB always set the correct default value,
rather than depending on all the calling code in all
of the diverse places to never forget to enter the correct "default value".
For Data Corruption prevention, it would be far better
to have the DB code enforce the rules that:
1) on insert, use a default of Now in Universal time.
2) ON update, use a default of Now in Universal time.
Its great that ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP
is present, and that is good enough within one time zone.
But, allowing UNIX_TIMESTAMP would help those with diverse time zones.
Problem 2)
I want to create a Table with a timestamp row that indicates
the Time the row was last checked for a given condition.
Thus, the value must be TIME ZERO on insert or update.
ie, if the row changes, then it must be checked again.
What I want to be able to say is:
CREATE TABLE timeclock (
id Integer UNSIGNED NOT NULL DEFAULT 0,
ts TimeStamp ON UPDATE 0 DEFAULT 0,
PRIMARY KEY (id)
) TYPE=MyISAM;
The problem is that it seems that for the ON UPDATE construct,
MySQL only allows one argument, namely: CURRENT_TIME.
So, again the code must set the "default value" to zero time
on insert and update, which is not a safe programming practice.
Again, for data corruption prevention,
it is far better for DB code to handle defaults,
rather than depending on all the programmers
writing all the lines of code in diverse modules,
to never forget to set the "default" value.
The ON UPDATE construct to MySQL was a great idea.
Extending it would make it even more helpful.
How to repeat:
Try entering in the SQL statements given.
The language currently does not allow these extensions
to the ON UPDATE and DEFAULT constructs
for a TimeStamp field.
Suggested fix:
Allow ON UPDATE and DEFAULT
for a TimeStamp field
to not only allow CURRENT_TIME
but also allow both:
UNIX_TIMESTAMP
and
0 (ie, '0001-01-01')
Description: I Suggest the following Enyhancements to MySQL. The Create Table ... Column ... ON UPDATE and DEFAULT constructs need to be enhanced to allow other time functions, than just the CURRENT_TIMESTAMP function. Consider the following two programming problems that would be solved better with this enhancement to MySQL. Problem 1) I want to have Databases in different countries, and thus in different time zones. I want to be able to select records from a table in DB2, based on a time greater than records from a table in DB1. Since the time zone may differ, what I really want to do is: CREATE TABLE timeclock ( id Integer UNSIGNED NOT NULL DEFAULT 0, ts TimeStamp ON UPDATE UNIX_TIMESTAMP DEFAULT UNIX_TIMESTAMP, PRIMARY KEY (id) ) TYPE=MyISAM; Unfortunately, I have to rather use CURRENT_TIMESTAMP and keep another DB table of time zone differences, in order to make sure the code works right. Sure, I could have the calling code allways set the timestamp. However, it is a far better programming practice to have the DB always set the correct default value, rather than depending on all the calling code in all of the diverse places to never forget to enter the correct "default value". For Data Corruption prevention, it would be far better to have the DB code enforce the rules that: 1) on insert, use a default of Now in Universal time. 2) ON update, use a default of Now in Universal time. Its great that ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP is present, and that is good enough within one time zone. But, allowing UNIX_TIMESTAMP would help those with diverse time zones. Problem 2) I want to create a Table with a timestamp row that indicates the Time the row was last checked for a given condition. Thus, the value must be TIME ZERO on insert or update. ie, if the row changes, then it must be checked again. What I want to be able to say is: CREATE TABLE timeclock ( id Integer UNSIGNED NOT NULL DEFAULT 0, ts TimeStamp ON UPDATE 0 DEFAULT 0, PRIMARY KEY (id) ) TYPE=MyISAM; The problem is that it seems that for the ON UPDATE construct, MySQL only allows one argument, namely: CURRENT_TIME. So, again the code must set the "default value" to zero time on insert and update, which is not a safe programming practice. Again, for data corruption prevention, it is far better for DB code to handle defaults, rather than depending on all the programmers writing all the lines of code in diverse modules, to never forget to set the "default" value. The ON UPDATE construct to MySQL was a great idea. Extending it would make it even more helpful. How to repeat: Try entering in the SQL statements given. The language currently does not allow these extensions to the ON UPDATE and DEFAULT constructs for a TimeStamp field. Suggested fix: Allow ON UPDATE and DEFAULT for a TimeStamp field to not only allow CURRENT_TIME but also allow both: UNIX_TIMESTAMP and 0 (ie, '0001-01-01')