SQL Error 2207 on TO_TIMESTAMP() using datetime format
In Postgres, I'm trying to do a date/time based query in my WHERE predicate.
When I try to select with this date/time format SQL error says the value needs to be an integer. I'm not sure why it does not think my minute of 17 is not an integer or why it only see it as a 1 and not a 17?
SELECT *
FROM history
WHERE create_time > TO_TIMESTAMP('2018-10-08T23:17:44.728','yyyy-MM-dd''T''HH:mm:ss.SSS');
ERROR: invalid value ":1" for "HH"
DETAIL: Value must be an integer.
SQL state: 22007
sql postgresql datetime-format
asked Nov 14 '18 at 18:19
JasonJason
70811023
add a comment |
In Postgres, I'm trying to do a date/time based query in my WHERE predicate.
When I try to select with this date/time format SQL error says the value needs to be an integer. I'm not sure why it does not think my minute of 17 is not an integer or why it only see it as a 1 and not a 17?
SELECT *
FROM history
WHERE create_time > TO_TIMESTAMP('2018-10-08T23:17:44.728','yyyy-MM-dd''T''HH:mm:ss.SSS');
ERROR: invalid value ":1" for "HH"
DETAIL: Value must be an integer.
SQL state: 22007
sql postgresql datetime-format
asked Nov 14 '18 at 18:19
JasonJason
70811023
add a comment |
In Postgres, I'm trying to do a date/time based query in my WHERE predicate.
When I try to select with this date/time format SQL error says the value needs to be an integer. I'm not sure why it does not think my minute of 17 is not an integer or why it only see it as a 1 and not a 17?
SELECT *
FROM history
WHERE create_time > TO_TIMESTAMP('2018-10-08T23:17:44.728','yyyy-MM-dd''T''HH:mm:ss.SSS');
ERROR: invalid value ":1" for "HH"
DETAIL: Value must be an integer.
SQL state: 22007
sql postgresql datetime-format
asked Nov 14 '18 at 18:19
JasonJason
70811023
In Postgres, I'm trying to do a date/time based query in my WHERE predicate.
When I try to select with this date/time format SQL error says the value needs to be an integer. I'm not sure why it does not think my minute of 17 is not an integer or why it only see it as a 1 and not a 17?
SELECT *
FROM history
WHERE create_time > TO_TIMESTAMP('2018-10-08T23:17:44.728','yyyy-MM-dd''T''HH:mm:ss.SSS');
ERROR: invalid value ":1" for "HH"
DETAIL: Value must be an integer.
SQL state: 22007
sql postgresql datetime-format
sql postgresql datetime-format
asked Nov 14 '18 at 18:19
JasonJason
70811023
asked Nov 14 '18 at 18:19
JasonJason
70811023
asked Nov 14 '18 at 18:19
JasonJason
70811023
asked Nov 14 '18 at 18:19
JasonJason
70811023
asked Nov 14 '18 at 18:19
JasonJason
70811023
70811023
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
You're trying to consume a date value that contains a T, and it looks like you're trying to declare to TO_TIMESTAMP that the T is a literal value to be ignored. Problem is you're doing this by putting 'T' (apostrophe-T-apostrophe, escaped) which is bumping the parser on by 3 characters and it is then encountering ':1' from 23:17 when it is expecting HH:
--your date, and underneath it, the format you gave
2018-10-08T23:17:44.728
yyyy-MM-dd'T'HH:mm:ss.SSS
Can you see how the HH aligns (vertically) with :1? Postgres is complaining that it was expecting an integer that it could parse to 23, but it encountered the string :1 which isn't an integer.
This question:
Postgres- have to_timestamp() ignore/not read a specific character in middle of date/time string
Implies you can put a space in the format where the T is, or just cast the string you have to a Timestamp - postgres can apparently parse that string as a Timestamp without you having to literally lay the format out for it explicitly
Try:
SELECT *
FROM history
WHERE create_time > TIMESTAMP '2018-10-08T23:17:44.728'
SELECT *
FROM history
WHERE create_time > cast('2018-10-08T23:17:44.728' as timestamp)
SELECT *
FROM history
WHERE create_time > TO_TIMESTAMP('2018-10-08T23:17:44.728','yyyy-MM-dd HH:mm:ss.SSS');
You might even find this works:
SELECT *
FROM history
WHERE create_time > TO_TIMESTAMP('2018-10-08T23:17:44.728', 'yyyy MM dd HH mm ss SSS')
The numbers align with the format fields and space is used for everything else you want to ignore (hyphens, colons, dots etc)
answered Nov 14 '18 at 18:48
Caius JardCaius Jard
11.9k21239
The first one with TIMESTAMP '2018-10-08T23:17:44.728' worked.
– Jason
Nov 14 '18 at 22:06
add a comment |
The problem is due to using ''T'' which's before HH, and DB signals that, you might use
TO_TIMESTAMP('2018-10-08 23:17:44.728','yyyy-mm-dd HH24:MI:SS.MS')
instead.
answered Nov 14 '18 at 18:34
Barbaros ÖzhanBarbaros Özhan
13.5k71633
I don't think it's a typo, I think it is an erroneous expectation that that is how the T in the date value should be consumed
– Caius Jard
Nov 14 '18 at 18:44
@CaiusJard ok, thanks.
– Barbaros Özhan
Nov 14 '18 at 18:52
This worked too, but required me to remove the 'T' from my date and came slightly after Caius Jard's answer
– Jason
Nov 14 '18 at 22:05
@Jason ok man, nice to hear. By the way I was the first replier, but not problem, the important thing to have a better answer( that's his one ).
– Barbaros Özhan
Nov 14 '18 at 22:08
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%2f53306510%2fsql-error-2207-on-to-timestamp-using-datetime-format%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
You're trying to consume a date value that contains a T, and it looks like you're trying to declare to TO_TIMESTAMP that the T is a literal value to be ignored. Problem is you're doing this by putting 'T' (apostrophe-T-apostrophe, escaped) which is bumping the parser on by 3 characters and it is then encountering ':1' from 23:17 when it is expecting HH:
--your date, and underneath it, the format you gave
2018-10-08T23:17:44.728
yyyy-MM-dd'T'HH:mm:ss.SSS
Can you see how the HH aligns (vertically) with :1? Postgres is complaining that it was expecting an integer that it could parse to 23, but it encountered the string :1 which isn't an integer.
This question:
Postgres- have to_timestamp() ignore/not read a specific character in middle of date/time string
Implies you can put a space in the format where the T is, or just cast the string you have to a Timestamp - postgres can apparently parse that string as a Timestamp without you having to literally lay the format out for it explicitly
Try:
SELECT *
FROM history
WHERE create_time > TIMESTAMP '2018-10-08T23:17:44.728'
SELECT *
FROM history
WHERE create_time > cast('2018-10-08T23:17:44.728' as timestamp)
SELECT *
FROM history
WHERE create_time > TO_TIMESTAMP('2018-10-08T23:17:44.728','yyyy-MM-dd HH:mm:ss.SSS');
You might even find this works:
SELECT *
FROM history
WHERE create_time > TO_TIMESTAMP('2018-10-08T23:17:44.728', 'yyyy MM dd HH mm ss SSS')
The numbers align with the format fields and space is used for everything else you want to ignore (hyphens, colons, dots etc)
answered Nov 14 '18 at 18:48
Caius JardCaius Jard
11.9k21239
The first one with TIMESTAMP '2018-10-08T23:17:44.728' worked.
– Jason
Nov 14 '18 at 22:06
add a comment |
You're trying to consume a date value that contains a T, and it looks like you're trying to declare to TO_TIMESTAMP that the T is a literal value to be ignored. Problem is you're doing this by putting 'T' (apostrophe-T-apostrophe, escaped) which is bumping the parser on by 3 characters and it is then encountering ':1' from 23:17 when it is expecting HH:
--your date, and underneath it, the format you gave
2018-10-08T23:17:44.728
yyyy-MM-dd'T'HH:mm:ss.SSS
Can you see how the HH aligns (vertically) with :1? Postgres is complaining that it was expecting an integer that it could parse to 23, but it encountered the string :1 which isn't an integer.
This question:
Postgres- have to_timestamp() ignore/not read a specific character in middle of date/time string
Implies you can put a space in the format where the T is, or just cast the string you have to a Timestamp - postgres can apparently parse that string as a Timestamp without you having to literally lay the format out for it explicitly
Try:
SELECT *
FROM history
WHERE create_time > TIMESTAMP '2018-10-08T23:17:44.728'
SELECT *
FROM history
WHERE create_time > cast('2018-10-08T23:17:44.728' as timestamp)
SELECT *
FROM history
WHERE create_time > TO_TIMESTAMP('2018-10-08T23:17:44.728','yyyy-MM-dd HH:mm:ss.SSS');
You might even find this works:
SELECT *
FROM history
WHERE create_time > TO_TIMESTAMP('2018-10-08T23:17:44.728', 'yyyy MM dd HH mm ss SSS')
The numbers align with the format fields and space is used for everything else you want to ignore (hyphens, colons, dots etc)
answered Nov 14 '18 at 18:48
Caius JardCaius Jard
11.9k21239
The first one with TIMESTAMP '2018-10-08T23:17:44.728' worked.
– Jason
Nov 14 '18 at 22:06
add a comment |
You're trying to consume a date value that contains a T, and it looks like you're trying to declare to TO_TIMESTAMP that the T is a literal value to be ignored. Problem is you're doing this by putting 'T' (apostrophe-T-apostrophe, escaped) which is bumping the parser on by 3 characters and it is then encountering ':1' from 23:17 when it is expecting HH:
--your date, and underneath it, the format you gave
2018-10-08T23:17:44.728
yyyy-MM-dd'T'HH:mm:ss.SSS
Can you see how the HH aligns (vertically) with :1? Postgres is complaining that it was expecting an integer that it could parse to 23, but it encountered the string :1 which isn't an integer.
This question:
Postgres- have to_timestamp() ignore/not read a specific character in middle of date/time string
Implies you can put a space in the format where the T is, or just cast the string you have to a Timestamp - postgres can apparently parse that string as a Timestamp without you having to literally lay the format out for it explicitly
Try:
SELECT *
FROM history
WHERE create_time > TIMESTAMP '2018-10-08T23:17:44.728'
SELECT *
FROM history
WHERE create_time > cast('2018-10-08T23:17:44.728' as timestamp)
SELECT *
FROM history
WHERE create_time > TO_TIMESTAMP('2018-10-08T23:17:44.728','yyyy-MM-dd HH:mm:ss.SSS');
You might even find this works:
SELECT *
FROM history
WHERE create_time > TO_TIMESTAMP('2018-10-08T23:17:44.728', 'yyyy MM dd HH mm ss SSS')
The numbers align with the format fields and space is used for everything else you want to ignore (hyphens, colons, dots etc)
answered Nov 14 '18 at 18:48
Caius JardCaius Jard
11.9k21239
You're trying to consume a date value that contains a T, and it looks like you're trying to declare to TO_TIMESTAMP that the T is a literal value to be ignored. Problem is you're doing this by putting 'T' (apostrophe-T-apostrophe, escaped) which is bumping the parser on by 3 characters and it is then encountering ':1' from 23:17 when it is expecting HH:
--your date, and underneath it, the format you gave
2018-10-08T23:17:44.728
yyyy-MM-dd'T'HH:mm:ss.SSS
Can you see how the HH aligns (vertically) with :1? Postgres is complaining that it was expecting an integer that it could parse to 23, but it encountered the string :1 which isn't an integer.
This question:
Postgres- have to_timestamp() ignore/not read a specific character in middle of date/time string
Implies you can put a space in the format where the T is, or just cast the string you have to a Timestamp - postgres can apparently parse that string as a Timestamp without you having to literally lay the format out for it explicitly
Try:
SELECT *
FROM history
WHERE create_time > TIMESTAMP '2018-10-08T23:17:44.728'
SELECT *
FROM history
WHERE create_time > cast('2018-10-08T23:17:44.728' as timestamp)
SELECT *
FROM history
WHERE create_time > TO_TIMESTAMP('2018-10-08T23:17:44.728','yyyy-MM-dd HH:mm:ss.SSS');
You might even find this works:
SELECT *
FROM history
WHERE create_time > TO_TIMESTAMP('2018-10-08T23:17:44.728', 'yyyy MM dd HH mm ss SSS')
The numbers align with the format fields and space is used for everything else you want to ignore (hyphens, colons, dots etc)
answered Nov 14 '18 at 18:48
Caius JardCaius Jard
11.9k21239
edited Nov 14 '18 at 19:00
answered Nov 14 '18 at 18:48
Caius JardCaius Jard
11.9k21239
answered Nov 14 '18 at 18:48
Caius JardCaius Jard
11.9k21239
answered Nov 14 '18 at 18:48
Caius JardCaius Jard
11.9k21239
11.9k21239
The first one with TIMESTAMP '2018-10-08T23:17:44.728' worked.
– Jason
Nov 14 '18 at 22:06
add a comment |
The first one with TIMESTAMP '2018-10-08T23:17:44.728' worked.
– Jason
Nov 14 '18 at 22:06
The first one with TIMESTAMP '2018-10-08T23:17:44.728' worked.
– Jason
Nov 14 '18 at 22:06
The first one with TIMESTAMP '2018-10-08T23:17:44.728' worked.
– Jason
Nov 14 '18 at 22:06
add a comment |
The problem is due to using ''T'' which's before HH, and DB signals that, you might use
TO_TIMESTAMP('2018-10-08 23:17:44.728','yyyy-mm-dd HH24:MI:SS.MS')
instead.
answered Nov 14 '18 at 18:34
Barbaros ÖzhanBarbaros Özhan
13.5k71633
I don't think it's a typo, I think it is an erroneous expectation that that is how the T in the date value should be consumed
– Caius Jard
Nov 14 '18 at 18:44
@CaiusJard ok, thanks.
– Barbaros Özhan
Nov 14 '18 at 18:52
This worked too, but required me to remove the 'T' from my date and came slightly after Caius Jard's answer
– Jason
Nov 14 '18 at 22:05
@Jason ok man, nice to hear. By the way I was the first replier, but not problem, the important thing to have a better answer( that's his one ).
– Barbaros Özhan
Nov 14 '18 at 22:08
add a comment |
The problem is due to using ''T'' which's before HH, and DB signals that, you might use
TO_TIMESTAMP('2018-10-08 23:17:44.728','yyyy-mm-dd HH24:MI:SS.MS')
instead.
answered Nov 14 '18 at 18:34
Barbaros ÖzhanBarbaros Özhan
13.5k71633
I don't think it's a typo, I think it is an erroneous expectation that that is how the T in the date value should be consumed
– Caius Jard
Nov 14 '18 at 18:44
@CaiusJard ok, thanks.
– Barbaros Özhan
Nov 14 '18 at 18:52
This worked too, but required me to remove the 'T' from my date and came slightly after Caius Jard's answer
– Jason
Nov 14 '18 at 22:05
@Jason ok man, nice to hear. By the way I was the first replier, but not problem, the important thing to have a better answer( that's his one ).
– Barbaros Özhan
Nov 14 '18 at 22:08
add a comment |
The problem is due to using ''T'' which's before HH, and DB signals that, you might use
TO_TIMESTAMP('2018-10-08 23:17:44.728','yyyy-mm-dd HH24:MI:SS.MS')
instead.
answered Nov 14 '18 at 18:34
Barbaros ÖzhanBarbaros Özhan
13.5k71633
The problem is due to using ''T'' which's before HH, and DB signals that, you might use
TO_TIMESTAMP('2018-10-08 23:17:44.728','yyyy-mm-dd HH24:MI:SS.MS')
instead.
answered Nov 14 '18 at 18:34
Barbaros ÖzhanBarbaros Özhan
13.5k71633
edited Nov 14 '18 at 18:51
answered Nov 14 '18 at 18:34
Barbaros ÖzhanBarbaros Özhan
13.5k71633
answered Nov 14 '18 at 18:34
Barbaros ÖzhanBarbaros Özhan
13.5k71633
answered Nov 14 '18 at 18:34
Barbaros ÖzhanBarbaros Özhan
13.5k71633
13.5k71633
I don't think it's a typo, I think it is an erroneous expectation that that is how the T in the date value should be consumed
– Caius Jard
Nov 14 '18 at 18:44
@CaiusJard ok, thanks.
– Barbaros Özhan
Nov 14 '18 at 18:52
This worked too, but required me to remove the 'T' from my date and came slightly after Caius Jard's answer
– Jason
Nov 14 '18 at 22:05
@Jason ok man, nice to hear. By the way I was the first replier, but not problem, the important thing to have a better answer( that's his one ).
– Barbaros Özhan
Nov 14 '18 at 22:08
add a comment |
I don't think it's a typo, I think it is an erroneous expectation that that is how the T in the date value should be consumed
– Caius Jard
Nov 14 '18 at 18:44
@CaiusJard ok, thanks.
– Barbaros Özhan
Nov 14 '18 at 18:52
This worked too, but required me to remove the 'T' from my date and came slightly after Caius Jard's answer
– Jason
Nov 14 '18 at 22:05
@Jason ok man, nice to hear. By the way I was the first replier, but not problem, the important thing to have a better answer( that's his one ).
– Barbaros Özhan
Nov 14 '18 at 22:08
I don't think it's a typo, I think it is an erroneous expectation that that is how the T in the date value should be consumed
– Caius Jard
Nov 14 '18 at 18:44
I don't think it's a typo, I think it is an erroneous expectation that that is how the T in the date value should be consumed
– Caius Jard
Nov 14 '18 at 18:44
@CaiusJard ok, thanks.
– Barbaros Özhan
Nov 14 '18 at 18:52
@CaiusJard ok, thanks.
– Barbaros Özhan
Nov 14 '18 at 18:52
This worked too, but required me to remove the 'T' from my date and came slightly after Caius Jard's answer
– Jason
Nov 14 '18 at 22:05
This worked too, but required me to remove the 'T' from my date and came slightly after Caius Jard's answer
– Jason
Nov 14 '18 at 22:05
@Jason ok man, nice to hear. By the way I was the first replier, but not problem, the important thing to have a better answer( that's his one ).
– Barbaros Özhan
Nov 14 '18 at 22:08
@Jason ok man, nice to hear. By the way I was the first replier, but not problem, the important thing to have a better answer( that's his one ).
– Barbaros Özhan
Nov 14 '18 at 22:08
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%2f53306510%2fsql-error-2207-on-to-timestamp-using-datetime-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
