mysql time_format function qustion

Multi tool use
I insert and update mysql time_format function to express string value in time,
Why do some numbers get
"truncated incorrect time value"
errors?
For example, '55' is not a problem with query, but when '188' is entered, the above error message appears.
The type is VARCHAR (50).
my query :
INSERT INTO TABLE_HOME (DURATION)
VALUES (TIME_FORMAT (# {DURATION, jdbcType = VARCHAR}, '% H:% i:% s'))
UPDATE TABLE_HOME SET DURATION = TIME_FORMAT (# {DURATION, jdbcType = VARCHAR}, '% H:% i:% s')
mysql time-format
add a comment |
I insert and update mysql time_format function to express string value in time,
Why do some numbers get
"truncated incorrect time value"
errors?
For example, '55' is not a problem with query, but when '188' is entered, the above error message appears.
The type is VARCHAR (50).
my query :
INSERT INTO TABLE_HOME (DURATION)
VALUES (TIME_FORMAT (# {DURATION, jdbcType = VARCHAR}, '% H:% i:% s'))
UPDATE TABLE_HOME SET DURATION = TIME_FORMAT (# {DURATION, jdbcType = VARCHAR}, '% H:% i:% s')
mysql time-format
As the name suggests TIME_FORMAT() expects a time. In that context, what do55
or188
mean?
– Álvaro González
Jan 3 at 9:28
add a comment |
I insert and update mysql time_format function to express string value in time,
Why do some numbers get
"truncated incorrect time value"
errors?
For example, '55' is not a problem with query, but when '188' is entered, the above error message appears.
The type is VARCHAR (50).
my query :
INSERT INTO TABLE_HOME (DURATION)
VALUES (TIME_FORMAT (# {DURATION, jdbcType = VARCHAR}, '% H:% i:% s'))
UPDATE TABLE_HOME SET DURATION = TIME_FORMAT (# {DURATION, jdbcType = VARCHAR}, '% H:% i:% s')
mysql time-format
I insert and update mysql time_format function to express string value in time,
Why do some numbers get
"truncated incorrect time value"
errors?
For example, '55' is not a problem with query, but when '188' is entered, the above error message appears.
The type is VARCHAR (50).
my query :
INSERT INTO TABLE_HOME (DURATION)
VALUES (TIME_FORMAT (# {DURATION, jdbcType = VARCHAR}, '% H:% i:% s'))
UPDATE TABLE_HOME SET DURATION = TIME_FORMAT (# {DURATION, jdbcType = VARCHAR}, '% H:% i:% s')
mysql time-format
mysql time-format
edited Jan 3 at 9:18
Vijunav Vastivch
3,3621723
3,3621723
asked Jan 3 at 9:06


MinaKimMinaKim
145
145
As the name suggests TIME_FORMAT() expects a time. In that context, what do55
or188
mean?
– Álvaro González
Jan 3 at 9:28
add a comment |
As the name suggests TIME_FORMAT() expects a time. In that context, what do55
or188
mean?
– Álvaro González
Jan 3 at 9:28
As the name suggests TIME_FORMAT() expects a time. In that context, what do
55
or 188
mean?– Álvaro González
Jan 3 at 9:28
As the name suggests TIME_FORMAT() expects a time. In that context, what do
55
or 188
mean?– Álvaro González
Jan 3 at 9:28
add a comment |
2 Answers
2
active
oldest
votes
TIME_FORMAT() expects a time. If you feed it with anything else, you first get a cast. In this case:
mysql> SELECT CAST(55 AS TIME), CAST(188 AS TIME);
+------------------+-------------------+
| CAST(55 AS TIME) | CAST(188 AS TIME) |
+------------------+-------------------+
| 00:00:55 | NULL |
+------------------+-------------------+
1 row in set, 1 warning (0.00 sec)
The rules are:
MySQL recognizes TIME values in these formats:
As a string in 'D HH:MM:SS' format. You can also use one of the following “relaxed” syntaxes: 'HH:MM:SS', 'HH:MM', 'D HH:MM', 'D HH',
or 'SS'. Here D represents days and can have a value from 0 to 34.
As a string with no delimiters in 'HHMMSS' format, provided that it makes sense as a time. For example, '101112' is understood as
'10:11:12', but '109712' is illegal (it has a nonsensical minute part)
and becomes '00:00:00'.
As a number in HHMMSS format, provided that it makes sense as a time. For example, 101112 is understood as '10:11:12'. The following
alternative formats are also understood: SS, MMSS, or HHMMSS.
In this case, #3 applies:
55
is rendered asSS
so it's valid.
188
is not a supported format so it producesNULL
.
Date and time handling is already hard enough. I suggest to:
Be explicit to avoid ambiguity (something like
23:30:45
is crystal clear, 188 is open to interpretations).Not use
VARCHAR
columns to store dates and times.
add a comment |
Time_Format not all integers are valid..
188
is not a valid time anymore
Even where you placed it:
"%H= Hour => 188?
%i=Minute => 188?
%s"=Second => 188?
Do you think 188
is a valid time of an Hour, Minute, Second
?
add a comment |
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%2f54019155%2fmysql-time-format-function-qustion%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
TIME_FORMAT() expects a time. If you feed it with anything else, you first get a cast. In this case:
mysql> SELECT CAST(55 AS TIME), CAST(188 AS TIME);
+------------------+-------------------+
| CAST(55 AS TIME) | CAST(188 AS TIME) |
+------------------+-------------------+
| 00:00:55 | NULL |
+------------------+-------------------+
1 row in set, 1 warning (0.00 sec)
The rules are:
MySQL recognizes TIME values in these formats:
As a string in 'D HH:MM:SS' format. You can also use one of the following “relaxed” syntaxes: 'HH:MM:SS', 'HH:MM', 'D HH:MM', 'D HH',
or 'SS'. Here D represents days and can have a value from 0 to 34.
As a string with no delimiters in 'HHMMSS' format, provided that it makes sense as a time. For example, '101112' is understood as
'10:11:12', but '109712' is illegal (it has a nonsensical minute part)
and becomes '00:00:00'.
As a number in HHMMSS format, provided that it makes sense as a time. For example, 101112 is understood as '10:11:12'. The following
alternative formats are also understood: SS, MMSS, or HHMMSS.
In this case, #3 applies:
55
is rendered asSS
so it's valid.
188
is not a supported format so it producesNULL
.
Date and time handling is already hard enough. I suggest to:
Be explicit to avoid ambiguity (something like
23:30:45
is crystal clear, 188 is open to interpretations).Not use
VARCHAR
columns to store dates and times.
add a comment |
TIME_FORMAT() expects a time. If you feed it with anything else, you first get a cast. In this case:
mysql> SELECT CAST(55 AS TIME), CAST(188 AS TIME);
+------------------+-------------------+
| CAST(55 AS TIME) | CAST(188 AS TIME) |
+------------------+-------------------+
| 00:00:55 | NULL |
+------------------+-------------------+
1 row in set, 1 warning (0.00 sec)
The rules are:
MySQL recognizes TIME values in these formats:
As a string in 'D HH:MM:SS' format. You can also use one of the following “relaxed” syntaxes: 'HH:MM:SS', 'HH:MM', 'D HH:MM', 'D HH',
or 'SS'. Here D represents days and can have a value from 0 to 34.
As a string with no delimiters in 'HHMMSS' format, provided that it makes sense as a time. For example, '101112' is understood as
'10:11:12', but '109712' is illegal (it has a nonsensical minute part)
and becomes '00:00:00'.
As a number in HHMMSS format, provided that it makes sense as a time. For example, 101112 is understood as '10:11:12'. The following
alternative formats are also understood: SS, MMSS, or HHMMSS.
In this case, #3 applies:
55
is rendered asSS
so it's valid.
188
is not a supported format so it producesNULL
.
Date and time handling is already hard enough. I suggest to:
Be explicit to avoid ambiguity (something like
23:30:45
is crystal clear, 188 is open to interpretations).Not use
VARCHAR
columns to store dates and times.
add a comment |
TIME_FORMAT() expects a time. If you feed it with anything else, you first get a cast. In this case:
mysql> SELECT CAST(55 AS TIME), CAST(188 AS TIME);
+------------------+-------------------+
| CAST(55 AS TIME) | CAST(188 AS TIME) |
+------------------+-------------------+
| 00:00:55 | NULL |
+------------------+-------------------+
1 row in set, 1 warning (0.00 sec)
The rules are:
MySQL recognizes TIME values in these formats:
As a string in 'D HH:MM:SS' format. You can also use one of the following “relaxed” syntaxes: 'HH:MM:SS', 'HH:MM', 'D HH:MM', 'D HH',
or 'SS'. Here D represents days and can have a value from 0 to 34.
As a string with no delimiters in 'HHMMSS' format, provided that it makes sense as a time. For example, '101112' is understood as
'10:11:12', but '109712' is illegal (it has a nonsensical minute part)
and becomes '00:00:00'.
As a number in HHMMSS format, provided that it makes sense as a time. For example, 101112 is understood as '10:11:12'. The following
alternative formats are also understood: SS, MMSS, or HHMMSS.
In this case, #3 applies:
55
is rendered asSS
so it's valid.
188
is not a supported format so it producesNULL
.
Date and time handling is already hard enough. I suggest to:
Be explicit to avoid ambiguity (something like
23:30:45
is crystal clear, 188 is open to interpretations).Not use
VARCHAR
columns to store dates and times.
TIME_FORMAT() expects a time. If you feed it with anything else, you first get a cast. In this case:
mysql> SELECT CAST(55 AS TIME), CAST(188 AS TIME);
+------------------+-------------------+
| CAST(55 AS TIME) | CAST(188 AS TIME) |
+------------------+-------------------+
| 00:00:55 | NULL |
+------------------+-------------------+
1 row in set, 1 warning (0.00 sec)
The rules are:
MySQL recognizes TIME values in these formats:
As a string in 'D HH:MM:SS' format. You can also use one of the following “relaxed” syntaxes: 'HH:MM:SS', 'HH:MM', 'D HH:MM', 'D HH',
or 'SS'. Here D represents days and can have a value from 0 to 34.
As a string with no delimiters in 'HHMMSS' format, provided that it makes sense as a time. For example, '101112' is understood as
'10:11:12', but '109712' is illegal (it has a nonsensical minute part)
and becomes '00:00:00'.
As a number in HHMMSS format, provided that it makes sense as a time. For example, 101112 is understood as '10:11:12'. The following
alternative formats are also understood: SS, MMSS, or HHMMSS.
In this case, #3 applies:
55
is rendered asSS
so it's valid.
188
is not a supported format so it producesNULL
.
Date and time handling is already hard enough. I suggest to:
Be explicit to avoid ambiguity (something like
23:30:45
is crystal clear, 188 is open to interpretations).Not use
VARCHAR
columns to store dates and times.
edited Jan 3 at 9:51
answered Jan 3 at 9:35
Álvaro GonzálezÁlvaro González
107k30189279
107k30189279
add a comment |
add a comment |
Time_Format not all integers are valid..
188
is not a valid time anymore
Even where you placed it:
"%H= Hour => 188?
%i=Minute => 188?
%s"=Second => 188?
Do you think 188
is a valid time of an Hour, Minute, Second
?
add a comment |
Time_Format not all integers are valid..
188
is not a valid time anymore
Even where you placed it:
"%H= Hour => 188?
%i=Minute => 188?
%s"=Second => 188?
Do you think 188
is a valid time of an Hour, Minute, Second
?
add a comment |
Time_Format not all integers are valid..
188
is not a valid time anymore
Even where you placed it:
"%H= Hour => 188?
%i=Minute => 188?
%s"=Second => 188?
Do you think 188
is a valid time of an Hour, Minute, Second
?
Time_Format not all integers are valid..
188
is not a valid time anymore
Even where you placed it:
"%H= Hour => 188?
%i=Minute => 188?
%s"=Second => 188?
Do you think 188
is a valid time of an Hour, Minute, Second
?
answered Jan 3 at 9:23
Vijunav VastivchVijunav Vastivch
3,3621723
3,3621723
add a comment |
add a comment |
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%2f54019155%2fmysql-time-format-function-qustion%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
7Xuwnc3tOak XuHJgD8mDpwETRxu ybVfY,pEuNGKwD2NvsGPWCz6gNeSfsvC9efLjWmUM8 VgHb qEQt4FN6dyV
As the name suggests TIME_FORMAT() expects a time. In that context, what do
55
or188
mean?– Álvaro González
Jan 3 at 9:28