[Maria-developers] Microseconds support in MariaDB 5.3.
I noticed: http://askmonty.org/blog/the-2-year-old-mariadb/ "The support for microseconds in TIMESTAMP, DATETIME, and TIME". I fully agree the 'full seconds support only' has been a sever limitation in MySQL. But I have two concerns with ths: 1) 'noisyness'. On not very fast systems the last digits of a TIME in microseconds should not be considered too much important. It depends on what else the system is doing at the time. I think for lots of users milliseconds and not microseconds would be better/more relevant. It is not always a lot of digits can be proven true. When we went to school we were all taught not to present results in natural sciences more accurate than the accuracy of data sources and measurements. Providing too many digits in a resuslt actually caused a lower score in my highschool! 2) Compability. There is a problem with UPDATES. A client/application written for MySQL may assume 'full seconds support only' and may generate the current MySQL format byte format in the WHERE clause (typically if the client reserves a 14 byte buffer for TIMERSTAMPS etc). In SQL: UPDATE table SET column = 'something' WHERE time_column = '01:01:01': .. this update will fail if the currently stored value is '01:01:01:010001'; and the rigth query in the context would be. UPDATE table SET column = 'something' WHERE time_column = '01:01:01:010001'; Ideally I think there should be a configuration parameter and a global variable to SET time_accuracy = full_second|millisecond|microsecond. Did somebody have considerations about same? I definitely think that lots of applications using a TIME|TIMESTAMP|DATETIME column in the WHERE-clause of an UPDATE statement will need some rewrite to be upgradable to MariaDB 5.3 -- Peter
Hello, By default, the existing precision is used. Only if a column is defined as TIME|TIMESTAMP(N) with N being either 3 or 6 , then millisecond or microsecond precision will be used. Philip Stoev ----- Original Message ----- From: "Peter Laursen" <peter_laursen@webyog.com> To: <maria-discuss@lists.launchpad.net>; <maria-developers@lists.launchpad.net> Sent: Wednesday, September 21, 2011 5:33 PM Subject: [Maria-developers] Microseconds support in MariaDB 5.3.
I noticed: http://askmonty.org/blog/the-2-year-old-mariadb/ "The support for microseconds in TIMESTAMP, DATETIME, and TIME".
I fully agree the 'full seconds support only' has been a sever limitation in MySQL. But I have two concerns with ths:
1) 'noisyness'. On not very fast systems the last digits of a TIME in microseconds should not be considered too much important. It depends on what else the system is doing at the time. I think for lots of users milliseconds and not microseconds would be better/more relevant. It is not always a lot of digits can be proven true. When we went to school we were all taught not to present results in natural sciences more accurate than the accuracy of data sources and measurements. Providing too many digits in a resuslt actually caused a lower score in my highschool!
2) Compability. There is a problem with UPDATES. A client/application written for MySQL may assume 'full seconds support only' and may generate the current MySQL format byte format in the WHERE clause (typically if the client reserves a 14 byte buffer for TIMERSTAMPS etc). In SQL:
UPDATE table SET column = 'something' WHERE time_column = '01:01:01': .. this update will fail if the currently stored value is '01:01:01:010001'; and the rigth query in the context would be. UPDATE table SET column = 'something' WHERE time_column = '01:01:01:010001';
Ideally I think there should be a configuration parameter and a global variable to SET time_accuracy = full_second|millisecond|microsecond.
Did somebody have considerations about same? I definitely think that lots of applications using a TIME|TIMESTAMP|DATETIME column in the WHERE-clause of an UPDATE statement will need some rewrite to be upgradable to MariaDB 5.3
-- Peter
--------------------------------------------------------------------------------
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
That is fine then! Only a little confusing thing is that as far as I know TIMESTAMP(14) is supported in MySQL 5.1 (and abandoned in MySQL 5.5) - so probably still in MariaDB as well. So maybe N should rather have been 17 or 20 (the byte length)? -- Peter On Wed, Sep 21, 2011 at 16:39, Philip Stoev <pstoev@askmonty.org> wrote:
Hello,
By default, the existing precision is used. Only if a column is defined as TIME|TIMESTAMP(N) with N being either 3 or 6 , then millisecond or microsecond precision will be used.
Philip Stoev
----- Original Message ----- From: "Peter Laursen" < peter_laursen@webyog.com>
To: <maria-discuss@lists.launchpad.net>; < maria-developers@lists.launchpad.net> Sent: Wednesday, September 21, 2011 5:33 PM Subject: [Maria-developers] Microseconds support in MariaDB 5.3.
I noticed: http://askmonty.org/blog/the-2-year-old-mariadb/ "The support
for microseconds in TIMESTAMP, DATETIME, and TIME".
I fully agree the 'full seconds support only' has been a sever limitation in MySQL. But I have two concerns with ths:
1) 'noisyness'. On not very fast systems the last digits of a TIME in microseconds should not be considered too much important. It depends on what else the system is doing at the time. I think for lots of users milliseconds and not microseconds would be better/more relevant. It is not always a lot of digits can be proven true. When we went to school we were all taught not to present results in natural sciences more accurate than the accuracy of data sources and measurements. Providing too many digits in a resuslt actually caused a lower score in my highschool!
2) Compability. There is a problem with UPDATES. A client/application written for MySQL may assume 'full seconds support only' and may generate the current MySQL format byte format in the WHERE clause (typically if the client reserves a 14 byte buffer for TIMERSTAMPS etc). In SQL:
UPDATE table SET column = 'something' WHERE time_column = '01:01:01': .. this update will fail if the currently stored value is '01:01:01:010001'; and the rigth query in the context would be. UPDATE table SET column = 'something' WHERE time_column = '01:01:01:010001';
Ideally I think there should be a configuration parameter and a global variable to SET time_accuracy = full_second|millisecond|microsecond.
Did somebody have considerations about same? I definitely think that lots of applications using a TIME|TIMESTAMP|DATETIME column in the WHERE-clause of an UPDATE statement will need some rewrite to be upgradable to MariaDB 5.3
-- Peter
--------------------------------------------------------------------------------
_______________________________________________
Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
That is fine then! Only a little confusing thing is that as far as I know TIMESTAMP(14) is supported in MySQL 5.1 (and abandoned in MySQL 5.5) - so probably still in MariaDB as well. So maybe N should rather have been 17 or 20 (the byte length)?
TIMESTAMP(14) has been removed in MariaDB as well.
@Philip .. I have one more question. Can libmysql as distributed by Oracle handle those extended precision DATE(TIME)STAMPs - or will only the one distributed with MariaDB do properly?? -- Peter On Wed, Sep 21, 2011 at 16:51, Philip Stoev <philip@stoev.org> wrote:
That is fine then! Only a little confusing thing is that as far as I know
TIMESTAMP(14) is supported in MySQL 5.1 (and abandoned in MySQL 5.5) - so probably still in MariaDB as well. So maybe N should rather have been 17 or 20 (the byte length)?
TIMESTAMP(14) has been removed in MariaDB as well.
Hi, Peter! On Sep 21, Peter Laursen wrote:
@Philip .. I have one more question. Can libmysql as distributed by Oracle handle those extended precision DATE(TIME)STAMPs - or will only the one distributed with MariaDB do properly??
It should work just fine. We have not changed the API. Thanks for asking (this, and especially the previous question!) - it means we need to document the new feature better. We will do it asap! Regards, Sergei
participants (4)
-
Peter Laursen -
Philip Stoev -
Philip Stoev -
Sergei Golubchik