[Maria-discuss] select using date_sub produces unwanted milliseconds in results

[Dan Ragle]

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) |

Re: [Maria-discuss] select using date_sub produces unwanted milliseconds in results

[Justin Swanhart]

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  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 

Re: [Maria-discuss] writing plugin with .net language (C#)

[Sergei Golubchik]

Hi, Roberto!

On Sep 18, Roberto Spadim wrote:
> guys , i'm still with doubts... but not more about c#, now about c++
> i was thinking about rewrite the code with c++
> 
> my doubt is could i use C++ 11 ? or i will have problem with mariadb
> makefiles etc etc?

Sure, you  can use any language you want, C++11 is perfectly fine too.
cmake doesn't care.

Regards,
Sergei

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