[R] RMariaDB returns a query without fractional seconds from MYSQL database

[Baki UNAL]

Hi

I created a table with following sql code in mysql:

CREATE TABLE pp_guniciislem201906.pp_201906 (
    date DATE,
    transaction_code VARCHAR(15),
    joint_agree_no VARCHAR(20),
    match_trade_item VARCHAR(15),
    buy_sell VARCHAR(15),
    num_transactions INT,
    paid_price FLOAT,
    trade_value FLOAT,
    order_no VARCHAR(20),
    trade_time TIME(6),
    trade_report INT,
    session VARCHAR(20),
    trade_status INT,
    settlement_date VARCHAR(5),
    assets_liabilities VARCHAR(1),
    trade_id INT,
    deal_id INT,
    update_time DATETIME,
    give_up_flag VARCHAR(1),
    update_no INT,
    update_time2 DATETIME    
);

I defined "trade_time" column with "TIME(6)". Because I have only time such as "09:55:02.113000" but not date.
Then I imported a csv file data to table with following sql code:

LOAD DATA INFILE 'G:\m201906.csv'
INTO TABLE pp_guniciislem201906.pp_201906
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n'
IGNORE 2 ROWS;

After that, when I query "SELECT * FROM pp_guniciislem201906.pp_201906;" from mysql "trade_time" column displays with fractional seconds. (I attached screenshot of mysql screen)

Then when I query from r with following code I could not get fractional seconds. I attached Rstudio screenshot image. As seen from this image in "trade_time" column fractional seconds are gone.


library(RMariaDB)

my_date <- '2019-06-24'

my_transaction_code <- 'AKBNK.E'

pp_201906 <- dbConnect(RMariaDB::MariaDB(), user='root', password='qazwsxedc', dbname='pp_guniciislem201906', host='localhost')

myquery <- paste("SELECT * FROM pp_201906 WHERE date='",my_date,"' AND transaction_code='",my_transaction_code,"'", sep="")

rs = dbSendQuery(pp_201906, myquery)

dbRows<-dbFetch(rs)


How can I solve this problem.
Best Regards