MySQL uploading data where one column in specific date format
up vote
1
down vote
favorite
Good day everyone,
I am trying to upload a csv file that has 4 columns into a MySQL database. Columns are:
user_id, user_name, user_screenname, user_created
The user_created column has date values in the following format:
Tue Nov 14 22:33:19 GMT 2017
Fri Mar 08 16:10:13 UTC 2013
I am trying to upload all the data into a table that has defined datatypes: user_id being a BIGINT(20) and all the other columns VARCHAR(45) including the user_created column as well.
I want to try and execute the following query to upload the data and format the values in the user_created column so that it's stored properly in the database, but my question is how do I emphasize the timezone abbreviations in the STR_TO_DATE() statement as there is no specific specifier description as per the documentation I found. Any help will be much appreciated!
LOAD DATA LOCAL INFILE 'C:/Users/Data/users_data.csv'
INTO TABLE new_schema.user
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY 'rn'
IGNORE 1 LINES
(user_id, user_name, user_screenname, @user_created_at)
SET user_created_at = STR_TO_DATE(@user_created_at, '%W %b %e %H:%i:%s GMT/UTC?? %Y);
I tried uploading using all the time/date related datatypes in mysql but as you would imagine all give warnings that the format is incorrect to be stored as any of the given datatypes.
mysql sql datetime
edited Nov 18 at 20:05
O. Jones
58.6k971106
asked Nov 18 at 19:26
asleniovas
133
add a comment |
up vote
1
down vote
favorite
Good day everyone,
I am trying to upload a csv file that has 4 columns into a MySQL database. Columns are:
user_id, user_name, user_screenname, user_created
The user_created column has date values in the following format:
Tue Nov 14 22:33:19 GMT 2017
Fri Mar 08 16:10:13 UTC 2013
I am trying to upload all the data into a table that has defined datatypes: user_id being a BIGINT(20) and all the other columns VARCHAR(45) including the user_created column as well.
I want to try and execute the following query to upload the data and format the values in the user_created column so that it's stored properly in the database, but my question is how do I emphasize the timezone abbreviations in the STR_TO_DATE() statement as there is no specific specifier description as per the documentation I found. Any help will be much appreciated!
LOAD DATA LOCAL INFILE 'C:/Users/Data/users_data.csv'
INTO TABLE new_schema.user
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY 'rn'
IGNORE 1 LINES
(user_id, user_name, user_screenname, @user_created_at)
SET user_created_at = STR_TO_DATE(@user_created_at, '%W %b %e %H:%i:%s GMT/UTC?? %Y);
I tried uploading using all the time/date related datatypes in mysql but as you would imagine all give warnings that the format is incorrect to be stored as any of the given datatypes.
mysql sql datetime
edited Nov 18 at 20:05
O. Jones
58.6k971106
asked Nov 18 at 19:26
asleniovas
133
Can you please tell the format of date in csv. Just you need ato_date(your_date,'your_format')meaning whichever format you may have you can write it in the to_date function (your_format) in this case. Just make sure the format is the exact copy of your csv date format.
– Himanshu Ahuja
Nov 18 at 20:02
The date format in the CSV is Tue Nov 14 22:33:19 GMT 2017. In my query I am specifying it asSTR_TO_DATE(@user_created_at, '%W %b %e %H:%i:%s ??GMT/UTC?? %Y')but I don't know how to specify the GMT/UTC bit as the data in the csv has multiple timezone abbreviations such as BST etc..
– asleniovas
Nov 18 at 20:17
useto_date(trunc(your_date),'%W %b %e')check if this works I just tried to remove time and extra part in datetime n then converted but idk about nested functions work or not in this case. Can check it out
– Himanshu Ahuja
Nov 18 at 20:30
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
Good day everyone,
I am trying to upload a csv file that has 4 columns into a MySQL database. Columns are:
user_id, user_name, user_screenname, user_created
The user_created column has date values in the following format:
Tue Nov 14 22:33:19 GMT 2017
Fri Mar 08 16:10:13 UTC 2013
I am trying to upload all the data into a table that has defined datatypes: user_id being a BIGINT(20) and all the other columns VARCHAR(45) including the user_created column as well.
I want to try and execute the following query to upload the data and format the values in the user_created column so that it's stored properly in the database, but my question is how do I emphasize the timezone abbreviations in the STR_TO_DATE() statement as there is no specific specifier description as per the documentation I found. Any help will be much appreciated!
LOAD DATA LOCAL INFILE 'C:/Users/Data/users_data.csv'
INTO TABLE new_schema.user
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY 'rn'
IGNORE 1 LINES
(user_id, user_name, user_screenname, @user_created_at)
SET user_created_at = STR_TO_DATE(@user_created_at, '%W %b %e %H:%i:%s GMT/UTC?? %Y);
I tried uploading using all the time/date related datatypes in mysql but as you would imagine all give warnings that the format is incorrect to be stored as any of the given datatypes.
mysql sql datetime
edited Nov 18 at 20:05
O. Jones
58.6k971106
asked Nov 18 at 19:26
asleniovas
133
Good day everyone,
I am trying to upload a csv file that has 4 columns into a MySQL database. Columns are:
user_id, user_name, user_screenname, user_created
The user_created column has date values in the following format:
Tue Nov 14 22:33:19 GMT 2017
Fri Mar 08 16:10:13 UTC 2013
I am trying to upload all the data into a table that has defined datatypes: user_id being a BIGINT(20) and all the other columns VARCHAR(45) including the user_created column as well.
I want to try and execute the following query to upload the data and format the values in the user_created column so that it's stored properly in the database, but my question is how do I emphasize the timezone abbreviations in the STR_TO_DATE() statement as there is no specific specifier description as per the documentation I found. Any help will be much appreciated!
LOAD DATA LOCAL INFILE 'C:/Users/Data/users_data.csv'
INTO TABLE new_schema.user
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY 'rn'
IGNORE 1 LINES
(user_id, user_name, user_screenname, @user_created_at)
SET user_created_at = STR_TO_DATE(@user_created_at, '%W %b %e %H:%i:%s GMT/UTC?? %Y);
I tried uploading using all the time/date related datatypes in mysql but as you would imagine all give warnings that the format is incorrect to be stored as any of the given datatypes.
mysql sql datetime
mysql sql datetime
edited Nov 18 at 20:05
O. Jones
58.6k971106
asked Nov 18 at 19:26
asleniovas
133
edited Nov 18 at 20:05
O. Jones
58.6k971106
asked Nov 18 at 19:26
asleniovas
133
edited Nov 18 at 20:05
O. Jones
58.6k971106
edited Nov 18 at 20:05
O. Jones
58.6k971106
edited Nov 18 at 20:05
O. Jones
58.6k971106
58.6k971106
asked Nov 18 at 19:26
asleniovas
133
asked Nov 18 at 19:26
asleniovas
133
asked Nov 18 at 19:26
asleniovas
133
133
Can you please tell the format of date in csv. Just you need ato_date(your_date,'your_format')meaning whichever format you may have you can write it in the to_date function (your_format) in this case. Just make sure the format is the exact copy of your csv date format.
– Himanshu Ahuja
Nov 18 at 20:02
The date format in the CSV is Tue Nov 14 22:33:19 GMT 2017. In my query I am specifying it asSTR_TO_DATE(@user_created_at, '%W %b %e %H:%i:%s ??GMT/UTC?? %Y')but I don't know how to specify the GMT/UTC bit as the data in the csv has multiple timezone abbreviations such as BST etc..
– asleniovas
Nov 18 at 20:17
useto_date(trunc(your_date),'%W %b %e')check if this works I just tried to remove time and extra part in datetime n then converted but idk about nested functions work or not in this case. Can check it out
– Himanshu Ahuja
Nov 18 at 20:30
add a comment |
Can you please tell the format of date in csv. Just you need ato_date(your_date,'your_format')meaning whichever format you may have you can write it in the to_date function (your_format) in this case. Just make sure the format is the exact copy of your csv date format.
– Himanshu Ahuja
Nov 18 at 20:02
The date format in the CSV is Tue Nov 14 22:33:19 GMT 2017. In my query I am specifying it asSTR_TO_DATE(@user_created_at, '%W %b %e %H:%i:%s ??GMT/UTC?? %Y')but I don't know how to specify the GMT/UTC bit as the data in the csv has multiple timezone abbreviations such as BST etc..
– asleniovas
Nov 18 at 20:17
useto_date(trunc(your_date),'%W %b %e')check if this works I just tried to remove time and extra part in datetime n then converted but idk about nested functions work or not in this case. Can check it out
– Himanshu Ahuja
Nov 18 at 20:30
Can you please tell the format of date in csv. Just you need a
to_date(your_date,'your_format') meaning whichever format you may have you can write it in the to_date function (your_format) in this case. Just make sure the format is the exact copy of your csv date format.– Himanshu Ahuja
Nov 18 at 20:02
Can you please tell the format of date in csv. Just you need a
to_date(your_date,'your_format') meaning whichever format you may have you can write it in the to_date function (your_format) in this case. Just make sure the format is the exact copy of your csv date format.– Himanshu Ahuja
Nov 18 at 20:02
The date format in the CSV is Tue Nov 14 22:33:19 GMT 2017. In my query I am specifying it as
STR_TO_DATE(@user_created_at, '%W %b %e %H:%i:%s ??GMT/UTC?? %Y') but I don't know how to specify the GMT/UTC bit as the data in the csv has multiple timezone abbreviations such as BST etc..– asleniovas
Nov 18 at 20:17
The date format in the CSV is Tue Nov 14 22:33:19 GMT 2017. In my query I am specifying it as
STR_TO_DATE(@user_created_at, '%W %b %e %H:%i:%s ??GMT/UTC?? %Y') but I don't know how to specify the GMT/UTC bit as the data in the csv has multiple timezone abbreviations such as BST etc..– asleniovas
Nov 18 at 20:17
use
to_date(trunc(your_date),'%W %b %e') check if this works I just tried to remove time and extra part in datetime n then converted but idk about nested functions work or not in this case. Can check it out– Himanshu Ahuja
Nov 18 at 20:30
use
to_date(trunc(your_date),'%W %b %e') check if this works I just tried to remove time and extra part in datetime n then converted but idk about nested functions work or not in this case. Can check it out– Himanshu Ahuja
Nov 18 at 20:30
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53364640%2fmysql-uploading-data-where-one-column-in-specific-date-format%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Can you please tell the format of date in csv. Just you need a
to_date(your_date,'your_format')meaning whichever format you may have you can write it in the to_date function (your_format) in this case. Just make sure the format is the exact copy of your csv date format.– Himanshu Ahuja
Nov 18 at 20:02
The date format in the CSV is Tue Nov 14 22:33:19 GMT 2017. In my query I am specifying it as
STR_TO_DATE(@user_created_at, '%W %b %e %H:%i:%s ??GMT/UTC?? %Y')but I don't know how to specify the GMT/UTC bit as the data in the csv has multiple timezone abbreviations such as BST etc..– asleniovas
Nov 18 at 20:17
use
to_date(trunc(your_date),'%W %b %e')check if this works I just tried to remove time and extra part in datetime n then converted but idk about nested functions work or not in this case. Can check it out– Himanshu Ahuja
Nov 18 at 20:30