Search between two dates with specific time with each date
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have two dates. 2019-01-01(fromDate)
and 2019-01-10(toDate)
. And now I want to search only the 13:00
to 16:00
time of each date. May I ask if is it possible using query only? Any answer is much appreciated
SELECT *
FROM table
WHERE fromDate >= 2019-01-01 AND toDate <= 2019-01-10
mysql sql
New contributor
add a comment |
I have two dates. 2019-01-01(fromDate)
and 2019-01-10(toDate)
. And now I want to search only the 13:00
to 16:00
time of each date. May I ask if is it possible using query only? Any answer is much appreciated
SELECT *
FROM table
WHERE fromDate >= 2019-01-01 AND toDate <= 2019-01-10
mysql sql
New contributor
stackoverflow.com/questions/4714899/mysql-compare-between-time Look at this to know more on comparing time
– Abhijith Nagarajan
20 hours ago
1
Sample data and desired results would help. What are you actually comparing?
– Gordon Linoff
16 hours ago
add a comment |
I have two dates. 2019-01-01(fromDate)
and 2019-01-10(toDate)
. And now I want to search only the 13:00
to 16:00
time of each date. May I ask if is it possible using query only? Any answer is much appreciated
SELECT *
FROM table
WHERE fromDate >= 2019-01-01 AND toDate <= 2019-01-10
mysql sql
New contributor
I have two dates. 2019-01-01(fromDate)
and 2019-01-10(toDate)
. And now I want to search only the 13:00
to 16:00
time of each date. May I ask if is it possible using query only? Any answer is much appreciated
SELECT *
FROM table
WHERE fromDate >= 2019-01-01 AND toDate <= 2019-01-10
mysql sql
mysql sql
New contributor
New contributor
edited 17 hours ago
Kiran Desai
2651315
2651315
New contributor
asked 20 hours ago
Ayiko NishimuraAyiko Nishimura
362
362
New contributor
New contributor
stackoverflow.com/questions/4714899/mysql-compare-between-time Look at this to know more on comparing time
– Abhijith Nagarajan
20 hours ago
1
Sample data and desired results would help. What are you actually comparing?
– Gordon Linoff
16 hours ago
add a comment |
stackoverflow.com/questions/4714899/mysql-compare-between-time Look at this to know more on comparing time
– Abhijith Nagarajan
20 hours ago
1
Sample data and desired results would help. What are you actually comparing?
– Gordon Linoff
16 hours ago
stackoverflow.com/questions/4714899/mysql-compare-between-time Look at this to know more on comparing time
– Abhijith Nagarajan
20 hours ago
stackoverflow.com/questions/4714899/mysql-compare-between-time Look at this to know more on comparing time
– Abhijith Nagarajan
20 hours ago
1
1
Sample data and desired results would help. What are you actually comparing?
– Gordon Linoff
16 hours ago
Sample data and desired results would help. What are you actually comparing?
– Gordon Linoff
16 hours ago
add a comment |
2 Answers
2
active
oldest
votes
You can try to use STR_TO_DATE function with the format and HOUR
function.
SELECT *
FROM table
WHERE
fromDate >= STR_TO_DATE('2019-01-01', '%Y-%m-%d')
AND
toDate <= STR_TO_DATE('2019-01-10', '%Y-%m-%d')
AND
(HOUR(fromDate) BETWEEN 13 AND 16 OR HOUR(toDate) BETWEEN 13 AND 16)
1
The query does not filter the records between those hours.
– Abhijith Nagarajan
20 hours ago
based on your example this2019-01-01 13:00:00
is fromDate. And toDate is2019-01-01 16:00:00
. What about the next date2019-01-02
. I want to also search the time which is from13:00:00
to16:00:00
in all dates. Is that possible?
– Ayiko Nishimura
20 hours ago
1
Ok I edit my answer :)
– D-Shih
19 hours ago
Thank you for your knowledge. Regarding the added query(HOUR(fromDate) BETWEEN 13 AND 16 OR HOUR(toDate) BETWEEN 13 AND 16)
. Is it possible to add minute on it. For example13:55:00
.
– Ayiko Nishimura
19 hours ago
Yes you can try to useMINUTE
function. dev.mysql.com/doc/refman/5.5/en/…
– D-Shih
19 hours ago
|
show 1 more comment
Use separate conditions on the date and on the time:
SELECT *
FROM table
WHERE fromDate >= '2019-01-01' AND
toDate < '2019-01-11' and
time(fromdate) between '13:00:00' and '16:00:00' and
time(todate) between '13:00:00' and '16:00:00;
Or, if you don't want 16:00:00
exactly, you can use hour()
:
SELECT *
FROM table
WHERE fromDate >= '2019-01-01' AND
toDate < '2019-01-11' and
hour(fromdate) in (13, 14, 15) and
hour(todate) in (13, 14, 15)
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
});
}
});
Ayiko Nishimura is a new contributor. Be nice, and check out our Code of Conduct.
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%2f55759280%2fsearch-between-two-dates-with-specific-time-with-each-date%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 can try to use STR_TO_DATE function with the format and HOUR
function.
SELECT *
FROM table
WHERE
fromDate >= STR_TO_DATE('2019-01-01', '%Y-%m-%d')
AND
toDate <= STR_TO_DATE('2019-01-10', '%Y-%m-%d')
AND
(HOUR(fromDate) BETWEEN 13 AND 16 OR HOUR(toDate) BETWEEN 13 AND 16)
1
The query does not filter the records between those hours.
– Abhijith Nagarajan
20 hours ago
based on your example this2019-01-01 13:00:00
is fromDate. And toDate is2019-01-01 16:00:00
. What about the next date2019-01-02
. I want to also search the time which is from13:00:00
to16:00:00
in all dates. Is that possible?
– Ayiko Nishimura
20 hours ago
1
Ok I edit my answer :)
– D-Shih
19 hours ago
Thank you for your knowledge. Regarding the added query(HOUR(fromDate) BETWEEN 13 AND 16 OR HOUR(toDate) BETWEEN 13 AND 16)
. Is it possible to add minute on it. For example13:55:00
.
– Ayiko Nishimura
19 hours ago
Yes you can try to useMINUTE
function. dev.mysql.com/doc/refman/5.5/en/…
– D-Shih
19 hours ago
|
show 1 more comment
You can try to use STR_TO_DATE function with the format and HOUR
function.
SELECT *
FROM table
WHERE
fromDate >= STR_TO_DATE('2019-01-01', '%Y-%m-%d')
AND
toDate <= STR_TO_DATE('2019-01-10', '%Y-%m-%d')
AND
(HOUR(fromDate) BETWEEN 13 AND 16 OR HOUR(toDate) BETWEEN 13 AND 16)
1
The query does not filter the records between those hours.
– Abhijith Nagarajan
20 hours ago
based on your example this2019-01-01 13:00:00
is fromDate. And toDate is2019-01-01 16:00:00
. What about the next date2019-01-02
. I want to also search the time which is from13:00:00
to16:00:00
in all dates. Is that possible?
– Ayiko Nishimura
20 hours ago
1
Ok I edit my answer :)
– D-Shih
19 hours ago
Thank you for your knowledge. Regarding the added query(HOUR(fromDate) BETWEEN 13 AND 16 OR HOUR(toDate) BETWEEN 13 AND 16)
. Is it possible to add minute on it. For example13:55:00
.
– Ayiko Nishimura
19 hours ago
Yes you can try to useMINUTE
function. dev.mysql.com/doc/refman/5.5/en/…
– D-Shih
19 hours ago
|
show 1 more comment
You can try to use STR_TO_DATE function with the format and HOUR
function.
SELECT *
FROM table
WHERE
fromDate >= STR_TO_DATE('2019-01-01', '%Y-%m-%d')
AND
toDate <= STR_TO_DATE('2019-01-10', '%Y-%m-%d')
AND
(HOUR(fromDate) BETWEEN 13 AND 16 OR HOUR(toDate) BETWEEN 13 AND 16)
You can try to use STR_TO_DATE function with the format and HOUR
function.
SELECT *
FROM table
WHERE
fromDate >= STR_TO_DATE('2019-01-01', '%Y-%m-%d')
AND
toDate <= STR_TO_DATE('2019-01-10', '%Y-%m-%d')
AND
(HOUR(fromDate) BETWEEN 13 AND 16 OR HOUR(toDate) BETWEEN 13 AND 16)
edited 19 hours ago
answered 20 hours ago
D-ShihD-Shih
27.6k61532
27.6k61532
1
The query does not filter the records between those hours.
– Abhijith Nagarajan
20 hours ago
based on your example this2019-01-01 13:00:00
is fromDate. And toDate is2019-01-01 16:00:00
. What about the next date2019-01-02
. I want to also search the time which is from13:00:00
to16:00:00
in all dates. Is that possible?
– Ayiko Nishimura
20 hours ago
1
Ok I edit my answer :)
– D-Shih
19 hours ago
Thank you for your knowledge. Regarding the added query(HOUR(fromDate) BETWEEN 13 AND 16 OR HOUR(toDate) BETWEEN 13 AND 16)
. Is it possible to add minute on it. For example13:55:00
.
– Ayiko Nishimura
19 hours ago
Yes you can try to useMINUTE
function. dev.mysql.com/doc/refman/5.5/en/…
– D-Shih
19 hours ago
|
show 1 more comment
1
The query does not filter the records between those hours.
– Abhijith Nagarajan
20 hours ago
based on your example this2019-01-01 13:00:00
is fromDate. And toDate is2019-01-01 16:00:00
. What about the next date2019-01-02
. I want to also search the time which is from13:00:00
to16:00:00
in all dates. Is that possible?
– Ayiko Nishimura
20 hours ago
1
Ok I edit my answer :)
– D-Shih
19 hours ago
Thank you for your knowledge. Regarding the added query(HOUR(fromDate) BETWEEN 13 AND 16 OR HOUR(toDate) BETWEEN 13 AND 16)
. Is it possible to add minute on it. For example13:55:00
.
– Ayiko Nishimura
19 hours ago
Yes you can try to useMINUTE
function. dev.mysql.com/doc/refman/5.5/en/…
– D-Shih
19 hours ago
1
1
The query does not filter the records between those hours.
– Abhijith Nagarajan
20 hours ago
The query does not filter the records between those hours.
– Abhijith Nagarajan
20 hours ago
based on your example this
2019-01-01 13:00:00
is fromDate. And toDate is 2019-01-01 16:00:00
. What about the next date 2019-01-02
. I want to also search the time which is from 13:00:00
to 16:00:00
in all dates. Is that possible?– Ayiko Nishimura
20 hours ago
based on your example this
2019-01-01 13:00:00
is fromDate. And toDate is 2019-01-01 16:00:00
. What about the next date 2019-01-02
. I want to also search the time which is from 13:00:00
to 16:00:00
in all dates. Is that possible?– Ayiko Nishimura
20 hours ago
1
1
Ok I edit my answer :)
– D-Shih
19 hours ago
Ok I edit my answer :)
– D-Shih
19 hours ago
Thank you for your knowledge. Regarding the added query
(HOUR(fromDate) BETWEEN 13 AND 16 OR HOUR(toDate) BETWEEN 13 AND 16)
. Is it possible to add minute on it. For example 13:55:00
.– Ayiko Nishimura
19 hours ago
Thank you for your knowledge. Regarding the added query
(HOUR(fromDate) BETWEEN 13 AND 16 OR HOUR(toDate) BETWEEN 13 AND 16)
. Is it possible to add minute on it. For example 13:55:00
.– Ayiko Nishimura
19 hours ago
Yes you can try to use
MINUTE
function. dev.mysql.com/doc/refman/5.5/en/…– D-Shih
19 hours ago
Yes you can try to use
MINUTE
function. dev.mysql.com/doc/refman/5.5/en/…– D-Shih
19 hours ago
|
show 1 more comment
Use separate conditions on the date and on the time:
SELECT *
FROM table
WHERE fromDate >= '2019-01-01' AND
toDate < '2019-01-11' and
time(fromdate) between '13:00:00' and '16:00:00' and
time(todate) between '13:00:00' and '16:00:00;
Or, if you don't want 16:00:00
exactly, you can use hour()
:
SELECT *
FROM table
WHERE fromDate >= '2019-01-01' AND
toDate < '2019-01-11' and
hour(fromdate) in (13, 14, 15) and
hour(todate) in (13, 14, 15)
add a comment |
Use separate conditions on the date and on the time:
SELECT *
FROM table
WHERE fromDate >= '2019-01-01' AND
toDate < '2019-01-11' and
time(fromdate) between '13:00:00' and '16:00:00' and
time(todate) between '13:00:00' and '16:00:00;
Or, if you don't want 16:00:00
exactly, you can use hour()
:
SELECT *
FROM table
WHERE fromDate >= '2019-01-01' AND
toDate < '2019-01-11' and
hour(fromdate) in (13, 14, 15) and
hour(todate) in (13, 14, 15)
add a comment |
Use separate conditions on the date and on the time:
SELECT *
FROM table
WHERE fromDate >= '2019-01-01' AND
toDate < '2019-01-11' and
time(fromdate) between '13:00:00' and '16:00:00' and
time(todate) between '13:00:00' and '16:00:00;
Or, if you don't want 16:00:00
exactly, you can use hour()
:
SELECT *
FROM table
WHERE fromDate >= '2019-01-01' AND
toDate < '2019-01-11' and
hour(fromdate) in (13, 14, 15) and
hour(todate) in (13, 14, 15)
Use separate conditions on the date and on the time:
SELECT *
FROM table
WHERE fromDate >= '2019-01-01' AND
toDate < '2019-01-11' and
time(fromdate) between '13:00:00' and '16:00:00' and
time(todate) between '13:00:00' and '16:00:00;
Or, if you don't want 16:00:00
exactly, you can use hour()
:
SELECT *
FROM table
WHERE fromDate >= '2019-01-01' AND
toDate < '2019-01-11' and
hour(fromdate) in (13, 14, 15) and
hour(todate) in (13, 14, 15)
answered 17 hours ago
Gordon LinoffGordon Linoff
800k37321426
800k37321426
add a comment |
add a comment |
Ayiko Nishimura is a new contributor. Be nice, and check out our Code of Conduct.
Ayiko Nishimura is a new contributor. Be nice, and check out our Code of Conduct.
Ayiko Nishimura is a new contributor. Be nice, and check out our Code of Conduct.
Ayiko Nishimura is a new contributor. Be nice, and check out our Code of Conduct.
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%2f55759280%2fsearch-between-two-dates-with-specific-time-with-each-date%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
stackoverflow.com/questions/4714899/mysql-compare-between-time Look at this to know more on comparing time
– Abhijith Nagarajan
20 hours ago
1
Sample data and desired results would help. What are you actually comparing?
– Gordon Linoff
16 hours ago