R help - Jul 2021 - RMariaDB returns a query without microseconds

Hi

I can query a table from a mysql database with?RMariaDB. One of the table's
column indicates "trade_time" and contains values such as
"09:55:02.113000". When I query this table I can not get fractional
seconds. I get a value such as "09:55:02". Also I get a variable class
such as?"hms" and "difftime" for this column. Not character
or POSIX* format. I tried both "datetime" and "varchar(25)"
as column type of?"trade_time" in mysql. How can I solve this problem?

Best regards
Baki ?nal

Am Mon, 26 Jul 2021 16:07:24 +0000 (UTC)
schrieb Baki UNAL via R-help <r-help at r-project.org>:
> Hi
> 
> I can query a table from a mysql database with?RMariaDB. One of the
> table's column indicates "trade_time" and contains values
such as
> "09:55:02.113000". When I query this table I can not get
fractional
> seconds. I get a value such as "09:55:02". Also I get a variable
> class such as?"hms" and "difftime" for this column. Not
character or
> POSIX* format. I tried both "datetime" and
"varchar(25)" as column
> type of?"trade_time" in mysql. How can I solve this problem?
Did you tell mariadb to include microsecond? I you just do
> create table dt (d TIME);
> insert into dt values("09:55:02.113000");
> select * from dt;
+----------+
| d        |
+----------+
| 09:55:02 |
+----------+
1 row in set (0.000 sec)

the fractional part is gone. But if you instead say
> create table dt (d DATETIME(6));
you get
> select * from dt;
+----------------------------+
| d                          |
+----------------------------+
| 2020-07-23 09:55:02.113000 |
+----------------------------+
1 row in set (0.001 sec)

And I also see this in R:
> dbGetQuery(con,"select * from dt")
                           d
1 2020-07-23 09:55:02.113000