Method to query DB on a column set as string which stores Timestamp information

Multi tool use
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have a DB field which was set as String and its storing timestamp information.(Which was mistake in first place). Now I face the challenge of querying the table using that column as if it was TimeStamp information(Basically using between operator).
I am using Hibernate and I tried using the CAST operator as
select *
from table
where cast(columName as Timestamp) between startTime and endTime
And unfortunately this doesn't work. Is there a way in which I can accomplish this query using hibernate?
mysql database hibernate casting hql
add a comment |
I have a DB field which was set as String and its storing timestamp information.(Which was mistake in first place). Now I face the challenge of querying the table using that column as if it was TimeStamp information(Basically using between operator).
I am using Hibernate and I tried using the CAST operator as
select *
from table
where cast(columName as Timestamp) between startTime and endTime
And unfortunately this doesn't work. Is there a way in which I can accomplish this query using hibernate?
mysql database hibernate casting hql
If the string fields are always of the same format, it will be easier to add a timestamp column and transfer the values from string to DateTime. It will make everything else much simpler.
– Nic3500
Dec 13 '18 at 0:36
Thats correct. But we can't change it because initially it was storing some other information, which we have to still persist.
– user2885295
Dec 13 '18 at 0:39
Put the other information in it's own column. If you have other information with a different format (or not a date at all), your "between" query will fail anyway.
– Nic3500
Dec 13 '18 at 0:42
"Doesn't work" means what? Errors? No results? Sits on couch all day?
– danblack
Dec 13 '18 at 2:31
add a comment |
I have a DB field which was set as String and its storing timestamp information.(Which was mistake in first place). Now I face the challenge of querying the table using that column as if it was TimeStamp information(Basically using between operator).
I am using Hibernate and I tried using the CAST operator as
select *
from table
where cast(columName as Timestamp) between startTime and endTime
And unfortunately this doesn't work. Is there a way in which I can accomplish this query using hibernate?
mysql database hibernate casting hql
I have a DB field which was set as String and its storing timestamp information.(Which was mistake in first place). Now I face the challenge of querying the table using that column as if it was TimeStamp information(Basically using between operator).
I am using Hibernate and I tried using the CAST operator as
select *
from table
where cast(columName as Timestamp) between startTime and endTime
And unfortunately this doesn't work. Is there a way in which I can accomplish this query using hibernate?
mysql database hibernate casting hql
mysql database hibernate casting hql
edited Jan 4 at 18:24
marc_s
586k13011281272
586k13011281272
asked Dec 13 '18 at 0:33
user2885295user2885295
105119
105119
If the string fields are always of the same format, it will be easier to add a timestamp column and transfer the values from string to DateTime. It will make everything else much simpler.
– Nic3500
Dec 13 '18 at 0:36
Thats correct. But we can't change it because initially it was storing some other information, which we have to still persist.
– user2885295
Dec 13 '18 at 0:39
Put the other information in it's own column. If you have other information with a different format (or not a date at all), your "between" query will fail anyway.
– Nic3500
Dec 13 '18 at 0:42
"Doesn't work" means what? Errors? No results? Sits on couch all day?
– danblack
Dec 13 '18 at 2:31
add a comment |
If the string fields are always of the same format, it will be easier to add a timestamp column and transfer the values from string to DateTime. It will make everything else much simpler.
– Nic3500
Dec 13 '18 at 0:36
Thats correct. But we can't change it because initially it was storing some other information, which we have to still persist.
– user2885295
Dec 13 '18 at 0:39
Put the other information in it's own column. If you have other information with a different format (or not a date at all), your "between" query will fail anyway.
– Nic3500
Dec 13 '18 at 0:42
"Doesn't work" means what? Errors? No results? Sits on couch all day?
– danblack
Dec 13 '18 at 2:31
If the string fields are always of the same format, it will be easier to add a timestamp column and transfer the values from string to DateTime. It will make everything else much simpler.
– Nic3500
Dec 13 '18 at 0:36
If the string fields are always of the same format, it will be easier to add a timestamp column and transfer the values from string to DateTime. It will make everything else much simpler.
– Nic3500
Dec 13 '18 at 0:36
Thats correct. But we can't change it because initially it was storing some other information, which we have to still persist.
– user2885295
Dec 13 '18 at 0:39
Thats correct. But we can't change it because initially it was storing some other information, which we have to still persist.
– user2885295
Dec 13 '18 at 0:39
Put the other information in it's own column. If you have other information with a different format (or not a date at all), your "between" query will fail anyway.
– Nic3500
Dec 13 '18 at 0:42
Put the other information in it's own column. If you have other information with a different format (or not a date at all), your "between" query will fail anyway.
– Nic3500
Dec 13 '18 at 0:42
"Doesn't work" means what? Errors? No results? Sits on couch all day?
– danblack
Dec 13 '18 at 2:31
"Doesn't work" means what? Errors? No results? Sits on couch all day?
– danblack
Dec 13 '18 at 2:31
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%2f53753394%2fmethod-to-query-db-on-a-column-set-as-string-which-stores-timestamp-information%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%2f53753394%2fmethod-to-query-db-on-a-column-set-as-string-which-stores-timestamp-information%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
fnevFNt5MMRzrtdI 877onytC eYGBS,aCZL1L Sxta1tmLYSf0JEAGIb xt 1T9,0ZgGBdWFiRc4N,8Fuc0vX CBX
If the string fields are always of the same format, it will be easier to add a timestamp column and transfer the values from string to DateTime. It will make everything else much simpler.
– Nic3500
Dec 13 '18 at 0:36
Thats correct. But we can't change it because initially it was storing some other information, which we have to still persist.
– user2885295
Dec 13 '18 at 0:39
Put the other information in it's own column. If you have other information with a different format (or not a date at all), your "between" query will fail anyway.
– Nic3500
Dec 13 '18 at 0:42
"Doesn't work" means what? Errors? No results? Sits on couch all day?
– danblack
Dec 13 '18 at 2:31