Description:
Background: In the polytechnic university (5000 students) we have been using MySQL for educational classes. However we have run into serious problems with the date data types, because MySQL uses different concepts from the SQL standard.
The most problematic is the reserved word TIMESTAMP which SQL 92 standard
specifically assigns for DATE + TIME [+ TIME ZONE] information. In MySQL
this is interpreted quite differently.
This is a serious prblem, because it makes it impossible to create case studies of cross platform applications that use Standard SQL. In other databases that we have the TIMESTAMP is interpreted in standard way.
Overall the weakest point of MySQL from business programs's point of view are the handling and storing of the times and dates.
How to repeat:
TIMESTAMP is not standards compatible
Suggested fix:
Please consider evaluating the SQL 92 and taking into the use the data types defined in there [1]:
5.3 <literal>
...
<datetime literal> ::=
<date literal>
| <time literal>
| <timestamp literal>
<date literal> ::=
DATE <date string>
<time literal> ::=
TIME <time string>
<timestamp literal> ::=
TIMESTAMP <timestamp string>
<time zone interval> ::=
<sign> <hours value> <colon> <minutes value>
<date value> ::=
<years value> <minus sign> <months value> <minus sign> <days value>
<time value> ::=
<hours value> <colon> <minutes value> <colon> <seconds value>
6.1 <data type>
...
<datetime type> ::=
DATE
| TIME [ <left paren> <time precision> <right paren> ]
[ WITH TIME ZONE ]
| TIMESTAMP [ <left paren> <timestamp precision> <right paren> ]
[ WITH TIME ZONE ]
<time precision> ::= <time fractional seconds precision>
<timestamp precision> ::= <time fractional seconds precision>
<time fractional seconds precision> ::= <unsigned integer>
<interval type> ::= INTERVAL <interval qualifier>
Other thinsgs that might want to consider improving against the standards
compliance have been gathered into page [2].
[1] The SQL 92 is downloadable at:
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
[2]
Comparison of different SQL implementations
http://troels.arvin.dk/db/rdbms/
Description: Background: In the polytechnic university (5000 students) we have been using MySQL for educational classes. However we have run into serious problems with the date data types, because MySQL uses different concepts from the SQL standard. The most problematic is the reserved word TIMESTAMP which SQL 92 standard specifically assigns for DATE + TIME [+ TIME ZONE] information. In MySQL this is interpreted quite differently. This is a serious prblem, because it makes it impossible to create case studies of cross platform applications that use Standard SQL. In other databases that we have the TIMESTAMP is interpreted in standard way. Overall the weakest point of MySQL from business programs's point of view are the handling and storing of the times and dates. How to repeat: TIMESTAMP is not standards compatible Suggested fix: Please consider evaluating the SQL 92 and taking into the use the data types defined in there [1]: 5.3 <literal> ... <datetime literal> ::= <date literal> | <time literal> | <timestamp literal> <date literal> ::= DATE <date string> <time literal> ::= TIME <time string> <timestamp literal> ::= TIMESTAMP <timestamp string> <time zone interval> ::= <sign> <hours value> <colon> <minutes value> <date value> ::= <years value> <minus sign> <months value> <minus sign> <days value> <time value> ::= <hours value> <colon> <minutes value> <colon> <seconds value> 6.1 <data type> ... <datetime type> ::= DATE | TIME [ <left paren> <time precision> <right paren> ] [ WITH TIME ZONE ] | TIMESTAMP [ <left paren> <timestamp precision> <right paren> ] [ WITH TIME ZONE ] <time precision> ::= <time fractional seconds precision> <timestamp precision> ::= <time fractional seconds precision> <time fractional seconds precision> ::= <unsigned integer> <interval type> ::= INTERVAL <interval qualifier> Other thinsgs that might want to consider improving against the standards compliance have been gathered into page [2]. [1] The SQL 92 is downloadable at: http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt [2] Comparison of different SQL implementations http://troels.arvin.dk/db/rdbms/