[MCOL-795] using cpimport on datetime of '2016-09-01 9:39' gives error Created: 2017-07-02  Updated: 2017-07-12  Resolved: 2017-07-12

Status: Closed
Project: MariaDB ColumnStore
Component/s: cpimport
Affects Version/s: 1.0.8, 1.0.9
Fix Version/s: Icebox

Type: Bug Priority: Minor
Reporter: james mclaurin (Inactive) Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Environment:

CentOS


Issue Links:
Duplicate
is duplicated by MCOL-502 Add a command line parameter for cpim... Closed

 Description   

Importing data using cpimport using the following sample data produces errors on datetime field. ColumnStore documentation states '9:5:0' is equilvalent
Sample data:
1288069,254,2016-07-24 00:05,Grant Avenue at Columbus Avenue,73,2016-07-24 00:09,Davis at Jackson,42,497,Subscriber,94111
1288070,575,2016-07-24 00:07,Commercial at Montgomery,45,2016-07-24 00:17,Embarcadero at Bryant,54,288,Subscriber,94105
1288071,435,2016-07-24 00:08,Paseo de San Antonio,7,2016-07-24 00:15,Japantown,9,223,Subscriber,95112
1288072,7766,2016-07-24 1:30,Arena Green - SAP Center,14,2016-07-24 3:39,Arena Green - SAP Center,14,82,Customer,95123
1288073,7754,2016-07-24 1:30,Arena Green - SAP Center,14,2016-07-24 3:40,Arena Green - SAP Center,14,167,Customer,95123
1288074,222,2016-07-24 1:33,Steuart at Market,74,2016-07-24 1:37,Embarcadero at Bryant,54,197,Subscriber,94107
1288075,411,2016-07-24 3:01,Market at Sansome,77,2016-07-24 3:07,2nd at Townsend,61,317,Subscriber,94105
1288076,316,2016-07-24 3:19,Clay at Battery,41,2016-07-24 3:24,Grant Avenue at Columbus Avenue,73,519,Subscriber,94133
1288077,31756,2016-07-24 3:20,Howard at 2nd,63,2016-07-24 12:09,San Francisco Caltrain (Townsend at 4th),70,632,Customer,94569
1288078,4439,2016-07-24 4:20,Civic Center BART (7th at Market),72,2016-07-24 05:34,Embarcadero at Vallejo,48,560,Customer,95706
1288079,349,2016-07-24 5:57,5th at Howard,57,2016-07-24 6:03,Howard at 2nd,63,471,Subscriber,94107
1288080,20140,2016-07-24 6:34,Mountain View City Hall,27,2016-07-24 12:10,Mountain View Caltrain Station,28,128,Customer,94043
1288081,378,2016-07-24 7:47,Grant Avenue at Columbus Avenue,73,2016-07-24 7:53,Steuart at Market,74,463,Subscriber,94133
1288082,1369,2016-07-24 8:07,Davis at Jackson,42,2016-07-24 8:30,Davis at Jackson,42,364,Subscriber,94111
1288083,401,2016-07-24 8:13,2nd at South Park,64,2016-07-24 8:19,Townsend at 7th,65,14,Subscriber,94105
1288084,423,2016-07-24 8:14,Steuart at Market,74,2016-07-24 8:21,2nd at Townsend,61,652,Subscriber,94109
1288085,209,2016-07-24 :33,Embarcadero at Bryant,54,2016-07-24 8:37,Embarcadero at Folsom,51,585,Subscriber,94105
1288094,730,2016-07-24 08:52,Embarcadero at Bryant,54,2016-07-24 9:04,Embarcadero at Sansome,60,275,Subscriber,94105
1288103,21814,2016-07-24 9:08,Market at 4th,76,2016-07-24 15:12,Market at 4th,76,15,Customer,80130
1288107,129,2016-07-24 9:11,Broadway St at Battery St,82,2016-07-24 9:13,Davis at Jackson,42,482,Subscriber,94111
1288110,473,2016-07-24 9:17,Davis at Jackson,42,2016-07-24 9:25,2nd at Folsom,62,482,Subscriber,94111
1288111,2855,2016-07-24 9:21,Powell Street BART,39,2016-07-24 10:08,Yerba Buena Center of the Arts (3rd @ Howard),68,426,Customer,94569
1288112,1793,2016-07-24 9:28,San Francisco Caltrain (Townsend at 4th),70,2016-07-24 9:58,San Francisco Caltrain (Townsend at 4th),70,475,Customer,89423
1288113,1779,2016-07-24 9:29,San Francisco Caltrain (Townsend at 4th),70,2016-07-24 9:58,San Francisco Caltrain 2 (330 Townsend),69,675,Customer,89423
1288114,650,2016-07-24 9:33,Davis at Jackson,42,2016-07-24 9:43,2nd at Townsend,61,364,Subscriber,94102
1288115,650,2016-07-24 9:36,Market at 10th,67,2016-07-24 9:47,San Francisco Caltrain 2 (330 Townsend),69,389,Subscriber,94028
1288118,691,2016-07-24 9:39,San Francisco Caltrain 2 (330 Townsend),69,2016-07-24 9:51,Harry Bridges Plaza (Ferry Building),50,662,Subscriber,95035
1288119,349,2016-07-24 9:41,San Francisco Caltrain 2 (330 Townsend),69,2016-07-24 9:47,5th at Howard,57,277,Subscriber,94107
1288120,334,2016-07-24 9:42,2nd at Townsend,61,2016-07-24 9:48,Yerba Buena Center of the Arts (3rd @ Howard),68,317,Subscriber,94107
1288121,363,2016-07-24 9:45,2nd at Townsend,61,2016-07-24 9:51,Market at Sansome,77,429,Subscriber,94107
1288122,197,2016-07-24 9:59,Temporary Transbay Terminal (Howard at Beale),55,2016-07-24 10:02,Howard at 2nd,63,349,Subscriber,94105

 Comments   
Comment by Andrew Hutchings (Inactive) [ 2017-07-02 ]

Can you please point us to the section of documentation you are referring to?

As it is "2016-07-24 9:39" would only work if there was an extra leading space before the first '9' (and that is more a backwards compatibility hack as that time is ambiguous without a leading zero). "2016-07-24 :33" definitely won't work, it will convert to "2016-07-24 00:00:00" as the code will try and convert ":33" to an hour and fail.

Comment by james mclaurin (Inactive) [ 2017-07-02 ]

Andrew,

Here's the page: https://mariadb.com/kb/en/mariadb/date-and-time-literals/

Look in the "Time Literals" section.

James —

Comment by Andrew Hutchings (Inactive) [ 2017-07-03 ]

Hi James,

That page only applies to MariaDB syntax, not ColumnStore's cpimport which uses a separate parser for date/time data. We are working on a way to let other date/time formats work as mentioned in your other bug report.

For this bug report I recommend we modify the following document to mention the time format and then also mark as a duplicate of MCOL-502:

https://mariadb.com/kb/en/mariadb/columnstore-bulk-data-loading/

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