[MCOL-3287] Nanoseconds in MariaDB ColumnStore Created: 2019-04-25  Updated: 2023-07-02  Resolved: 2023-07-02

Status: Closed
Project: MariaDB ColumnStore
Component/s: N/A
Affects Version/s: 1.2.3
Fix Version/s: Icebox

Type: New Feature Priority: Major
Reporter: Todd Stoffel (Inactive) Assignee: Todd Stoffel (Inactive)
Resolution: Won't Do Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-18843 Nanoseconds in MariaDB Open
Epic Link: ColumnStore Compatibility Improvements

 Description   

Nanoseconds in MariaDB
----------------------------------------
Currently we have Microseconds in MariaDB

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

 
 
MariaDB [test]> SELECT CAST('2009-12-31 23:59:59.9876543210' as DATETIME(6));
 
+-------------------------------------------------------+
 
| CAST('2009-12-31 23:59:59.9876543210' as DATETIME(6)) |
 
+-------------------------------------------------------+
 
| 2009-12-31 23:59:59.987654                            |
 
+-------------------------------------------------------+
 
1 row in set, 1 warning (0.000 sec)
 
 
 
MariaDB [test]> SELECT CAST('2009-12-31 23:59:59.9876543210' as DATETIME(10));
 
ERROR 1426 (42000): Too big precision 10 specified for '2009-12-31 23:59:59.9876543210'. Maximum is 6
 
 
 
MariaDB [test]> SELECT CAST('2009-12-31 23:59:59.9876543210' as DATETIME(9));
 
ERROR 1426 (42000): Too big precision 9 specified for '2009-12-31 23:59:59.9876543210'. Maximum is 6

Nanoseconds in MariaDB

It ranges from 0 to 9, meaning that we can use the TIMESTAMP data type to store up to nanosecond. Currently MariaDB support fractional seconds precision sets to microsecond.

 
 
MariaDB [test]> SELECT TIME'10:10:10.1234567890';
 
+---------------------------+
 
| TIME'10:10:10.1234567890' |
 
+---------------------------+
 
| 10:10:10.123456           |
 
+---------------------------+
 
1 row in set, 1 warning (0.001 sec)

For example :-

I have two timestamps i.e. start= 15-03-11 15:10:10 and finish=15-03-11 15:10:10 and i need to subtract them ("finish-start" ) in order to find the duration.

Because their difference is in nanosecond level, I need to have them into 15:10:10.000000001 and 15:10:10.000000015 respectively.

TIMESTAMP [(<nanosecond precision)]



 Comments   
Comment by Todd Stoffel (Inactive) [ 2023-07-02 ]

The "create date" on this ticket is pre-convergence with MariaDB server. If the issue still exists in a modern version of the engine/plugin please submit a new ticket.

Generated at Thu Feb 08 02:41:37 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.