← Back to team overview

maria-discuss team mailing list archive

  1. maria-discuss team
  2. Mailing list archive
  3. Message #02942

Re: select using date_sub produces unwanted milliseconds in results

  Thread Previous • Date Previous • • Thread Next 
Thanks Justin, but I'm not sure I'm following this.
Is there some way that I can see the differences in what's being returned from each phase of the operation within the client itself? Each of these statements: 

  select substr(datetime,1,13) from test limit 1;
  select substr('2015-09-18 09:01:02',1,13) from test limit 1;
  select concat('2015-09-18 09',':00:00') from test limit 1;
select concat(substr('2015-09-18 09:01:02',1,13),':00:00') from test limit 1; 
  select concat(substr(datetime,1,13),':00:00') from test limit 1;

Returns:

  Type:       VAR_STRING
  Collation:  utf8_general_ci (33)

The two statements that start with the substr have:

  Length:     39
  Max_length: 13
  Decimals:   31

while the three that start with the concat have:

  Length:     57
  Max_length: 19
  Decimals:   31

Then of course adding the date_sub:
select date_sub(concat('2015-09-18 09',':00:00'),interval 1 second) from test limit 1; select date_sub(concat(substr('2015-09-18 09:01:02',1,13),':00:00'),interval 1 second) from test limit 1; select date_sub(concat(substr(datetime,1,13),':00:00'),interval 1 second) from test limit 1 

Produces this for the first two selects:

  Type:       STRING
  Collation:  binary (63)
  Length:     19
  Max_length: 19
  Decimals:   0
  Flags:      BINARY

and this for the third:

  Type:       STRING
  Collation:  binary (63)
  Length:     26
  Max_length: 26
  Decimals:   6
  Flags:      BINARY
I guess what I'm saying is that I'm not seeing the difference that date_sub sees, yet clearly it's handling them differently. 

On 9/19/2015 6:12 PM, Justin Swanhart wrote:

My guess with no code inspection is that substr returns a string where s date literal string is a date literal (parser handles this).  As such the string conversion to date probably produces a time with microsecond resolution, so you see 0 microseconds.  This is likely because concat returns the data type of the first argument, which is a date literal, but substr returns string, which must be cast internally.

--Justin

Sent from my iPhone


On Sep 19, 2015, at 3:53 PM, Dan Ragle <daniel@xxxxxxxxxxxxxx> wrote:

We're converting from MySQL 5.1 to MariaDB 5.5.44 and I'm hoping someone can help clarify for me what's going on in the following scenario. In brief, we're getting milliseconds displayed in a particular date_sub calculation (and possibly other selects with date arithmetic, trying to figure out how to track them down is part of the goal of this post) where we aren't expecting them.

MariaDB [testdb]> show variables like '%version%';
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| innodb_version          | 5.5.43-MariaDB-37.2 |
| protocol_version        | 10                  |
| slave_type_conversions  |                     |
| version                 | 5.5.44-MariaDB-log  |
| version_comment         | MariaDB Server      |
| version_compile_machine | x86_64              |
| version_compile_os      | Linux               |
+-------------------------+---------------------+
7 rows in set (0.00 sec)

MariaDB [testdb]> show create table test \G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `datetime` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

MariaDB [testdb]> select datetime from test;
Field   1:  `datetime`
Catalog:    `def`
Database:   `velocitynorep`
Table:      `test`
Org_table:  `test`
Type:       DATETIME
Collation:  binary (63)
Length:     19
Max_length: 19
Decimals:   0
Flags:      NOT_NULL BINARY NO_DEFAULT_VALUE

+---------------------+
| datetime            |
+---------------------+
| 2015-09-18 09:23:21 |
+---------------------+
1 row in set (0.00 sec)

MariaDB [testdb]> select date_sub(datetime,interval 1 second) from test;
Field   1:  `date_sub(datetime,interval 1 second)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DATETIME
Collation:  binary (63)
Length:     19
Max_length: 19
Decimals:   0
Flags:      BINARY


+--------------------------------------+
| date_sub(datetime,interval 1 second) |
+--------------------------------------+
| 2015-09-18 09:23:20                  |
+--------------------------------------+
1 row in set (0.00 sec)

MariaDB [testdb]> select substr(datetime,1,13) from test;
Field   1:  `substr(datetime,1,13)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     39
Max_length: 13
Decimals:   31
Flags:      NOT_NULL

+-----------------------+
| substr(datetime,1,13) |
+-----------------------+
| 2015-09-18 09         |
+-----------------------+
1 row in set (0.00 sec)

MariaDB [testdb]> select concat(substr(datetime,1,13),':00:00')
    -> from test;
Field   1:  `concat(substr(datetime,1,13),':00:00')`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     57
Max_length: 19
Decimals:   31
Flags:      NOT_NULL

+----------------------------------------+
| concat(substr(datetime,1,13),':00:00') |
+----------------------------------------+
| 2015-09-18 09:00:00                    |
+----------------------------------------+
1 row in set (0.00 sec)

MariaDB [testdb]> select concat('2015-09-18 09',':00:00');
Field   1:  `concat('2015-09-18 09',':00:00')`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     57
Max_length: 19
Decimals:   31
Flags:      NOT_NULL


+----------------------------------+
| concat('2015-09-18 09',':00:00') |
+----------------------------------+
| 2015-09-18 09:00:00              |
+----------------------------------+
1 row in set (0.00 sec)

MariaDB [testdb]> select date_sub(concat('2015-09-18 09',':00:00'),
    -> interval 1 second);
Field   1:  `date_sub(concat('2015-09-18 09',':00:00'),
interval 1 second)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       STRING
Collation:  binary (63)
Length:     19
Max_length: 19
Decimals:   0
Flags:      BINARY


+---------------------------------------------------------------+
| date_sub(concat('2015-09-18 09',':00:00'),
interval 1 second) |
+---------------------------------------------------------------+
| 2015-09-18 08:59:59                                           |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [testdb]> select
    -> date_sub(concat(substr(datetime,1,13),':00:00'),
    -> interval 1 second) from test;
Field   1:  `date_sub(concat(substr(datetime,1,13),':00:00'),
interval 1 second)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       STRING
Collation:  binary (63)
Length:     26
Max_length: 26
Decimals:   6
Flags:      BINARY

+---------------------------------------------------------------------+
| date_sub(concat(substr(datetime,1,13),':00:00'),
interval 1 second) |
+---------------------------------------------------------------------+
| 2015-09-18 08:59:59.000000                                          |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

The last one is the one that has me stumped.

1.) Why does it include milliseconds in the output? According to this page:

    https://mariadb.com/kb/en/mariadb/microseconds-in-mariadb/

where no precision is specified the result should not display milliseconds, which is what happens in all the other examples above except this one.

2.) The statement on a 5.1 MySQL server (5.1.73-log, also 64 bit Linux) produces this:

mysql> select date_sub(concat(substr(datetime,1,13),':00:00'),
    -> interval 1 second) from test;
Field   1:  `date_sub(concat(substr(datetime,1,13),':00:00'),
interval 1 second)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       STRING
Collation:  binary (63)
Length:     29
Max_length: 19
Decimals:   31
Flags:      BINARY

+---------------------------------------------------------------------+
| date_sub(concat(substr(datetime,1,13),':00:00'),
interval 1 second) |
+---------------------------------------------------------------------+
| 2015-09-18 08:59:59                                                 |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

Is there some global variable I can set to revert to the 5.1 behavior? I realize I can CAST the result to a datetime to make sure I don't get the fractional seconds, but as I mentioned we're converting our system over to 5.5 and there's boatloads of code that I would have to scan through to try and find these differences.

3.) If there is no way to revert to the 5.1 behavior, can someone help me to understand the exact reason why the milliseconds get displayed on the final select in MariaDB 5.5? I'll need to look for any similar types of results and other than the exact reduction above I'm not sure what statements I would need to identify and test.

Thanks for any assistance!

Dan Ragle

_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

References

  Thread Previous • Date Previous • • Thread Next