DBeaver: display datetime with milliseconds

Question

This is MySQL 5.7 column:

`date` datetime(6) NOT NULL

I can see milliseconds using console:

mysql> select * from teq_log_1;
+----+----------------------------+-------+---------+---------+
| id | date                       | level | message | details |
+----+----------------------------+-------+---------+---------+
|  4 | 2019-08-01 12:23:34.123457 |     2 | test    | NULL    |
+----+----------------------------+-------+---------+---------+

but I cannot see milliseconds in DBeaver client:

There is format option for the date column (Right Click on date column / View/Format / Data Formats ...):

There are Date, Time, Timestamp & Number types (w/o Datetime). I set yyyy-MM-dd HH:mm:ss.sss & yyyy-MM-dd HH:mm:ss.mmm patterns but without result.

How can I display milliseconds/microseconds for datetime(6) in DBeaver (v. 6.1.3)?

Answer 1

Turning on Use native date/time format did it for me.

Answer 2

Using uppercase SSS as in yyyy-MM-dd HH:mm:ss.SSS shows milliseconds - you were using lowercase sss

Answer 3

Using yyyy-MM-dd HH:mm:ss:S:Z in Timestamp under (for mac :: prefernces -> Editors -> Data Editor -> Data Formats ) helped me.

More on dBeaver documentation can be found here :: https://dbeaver.com/docs/wiki/Managing-Data-Formats/

Answer 4

Probably, this is a cache related issue. I recreated my tables and reconnect DBeaver. Now I see milliseconds in DBeaver client:

... and I see microseconds in mysql console (but the last 3 digits are "0" - unixtime was inserted, w/o microseconds):

mysql> select * from teq_log;
+----+----------------------------+-------+-
| id | date                       | level | 
+----+----------------------------+-------+-
|  5 | 2019-08-01 11:10:27.247000 |     1 | 
|  6 | 2019-08-01 11:10:27.259000 |     1 | 
|  7 | 2019-08-01 11:10:27.636000 |     1 | 
|  8 | 2019-08-01 11:10:27.713000 |     1 | 
|  9 | 2019-08-01 11:10:27.758000 |     1 | 
| 10 | 2019-08-01 11:10:31.768000 |     1 | 
| 11 | 2019-08-01 11:10:32.300000 |     1 | 
+----+----------------------------+-------+-

Answer 5

Using "Datetime" instead of "Date" as data type worked for me.

My java time's variable was--> Date now = new Date(System.currentTimeMillis());

I saved the "now" variable in DBeaver as "Datetime" type and this is the result--> 2022-11-11 15:15:17.000