MySQL uploading data where one column in specific date format
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 '18 at 20:05
O. Jones
59.8k974106
asked Nov 18 '18 at 19:26
asleniovasasleniovas
195
add a comment |
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 '18 at 20:05
O. Jones
59.8k974106
asked Nov 18 '18 at 19:26
asleniovasasleniovas
195
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 '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 '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 '18 at 20:30
add a comment |
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 '18 at 20:05
O. Jones
59.8k974106
asked Nov 18 '18 at 19:26
asleniovasasleniovas
195
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 '18 at 20:05
O. Jones
59.8k974106
asked Nov 18 '18 at 19:26
asleniovasasleniovas
195
edited Nov 18 '18 at 20:05
O. Jones
59.8k974106
asked Nov 18 '18 at 19:26
asleniovasasleniovas
195
edited Nov 18 '18 at 20:05
O. Jones
59.8k974106
edited Nov 18 '18 at 20:05
O. Jones
59.8k974106
edited Nov 18 '18 at 20:05
O. Jones
59.8k974106
59.8k974106
asked Nov 18 '18 at 19:26
asleniovasasleniovas
195
asked Nov 18 '18 at 19:26
asleniovasasleniovas
195
asked Nov 18 '18 at 19:26
asleniovasasleniovas
195
195
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 '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 '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 '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 '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 '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 '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 '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 '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 '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 '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 '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 '18 at 20:30
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
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
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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 '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 '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 '18 at 20:30