MySQL 两个时间戳之间的MYSQL查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21032335/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MYSQL query between two timestamps
提问by user2068441
I have the following entry in my DB table
我的数据库表中有以下条目
eventName(varchar 100) -> myEvent
date(timestamp) -> 2013-03-26 09:00:00
and I am trying to use the following query;
我正在尝试使用以下查询；
SELECT *
FROM eventList
WHERE `date` BETWEEN UNIX_TIMESTAMP(1364256001) AND UNIX_TIMESTAMP(1364342399)
i.e between 2013-03-26 00:00:01 and 2013-03-26 23:59:59
即在 2013-03-26 00:00:01 和 2013-03-26 23:59:59 之间
but it is giving me 0 results.
但它给了我 0 结果。
I have tried expanding the date range with no luck and there are definitely results within the range.
我试过扩大日期范围，但没有运气，范围内肯定有结果。
any help is appreciated.
任何帮助表示赞赏。
回答by Frank Conry
Try:
尝试：
SELECT *
FROM eventList
WHERE `date` BETWEEN FROM_UNIXTIME(1364256001) AND FROM_UNIXTIME(1364342399)
Or
或者
SELECT *
FROM eventList WHERE `date`
BETWEEN '2013-03-26 00:00:01' AND '2013-03-26 23:59:59'
回答by Amaynut
You just need to convert your dates to UNIX_TIMESTAMP. You can write your query like this:
您只需要将日期转换为UNIX_TIMESTAMP. 您可以像这样编写查询：
SELECT *
FROM eventList
WHERE
date BETWEEN
UNIX_TIMESTAMP('2013/03/26')
AND
UNIX_TIMESTAMP('2013/03/27 23:59:59');
When you don't specify the time, MySQL will assume 00:00:00as the time for the given date.
当您不指定时间时，MySQL 将假定00:00:00为给定日期的时间。
回答by androsfat
Try this one. It works for me.
试试这个。这个对我有用。
SELECT *
FROM eventList
WHERE DATE(date)
BETWEEN '2013-03-26' AND '2013-03-27'
回答by Ragu S Mech
SELECT * FROM `orders` WHERE `order_date_time` BETWEEN 1534809600 AND 1536718364
回答by Supriyanka Kharade
Try below code. Worked in my case. Hope this helps!
试试下面的代码。在我的情况下工作。希望这可以帮助！
select id,total_Hour,
(coalesce(weekday_1,0)+coalesce(weekday_2,0)+coalesce(weekday_3,0)) as weekday_Listing_Hrs,
(coalesce(weekend_1,0)+coalesce(weekend_2,0)+coalesce(weekend_3,0)) as weekend_Listing_Hrs
from
select *,
listing_duration_Hour-(coalesce(weekday_1,0)+coalesce(weekday_2,0)+coalesce(weekday_3,0)+coalesce(weekend_1,0)+coalesce(weekend_2,0)) as weekend_3
from
(
select * ,
case when date(Start_Date) = date(End_Date) and weekday(Start_Date) in (0,1,2,3,4)
then timestampdiff(hour,Start_Date,End_Date)
when date(Start_Date) != date(End_Date) and weekday(Start_Date) in (0,1,2,3,4)
then 24-timestampdiff(hour,date(Start_Date),Start_Date)
end as weekday_1,
case when date(Start_Date) != date(End_Date) and weekday(End_Date) in (0,1,2,3,4)
then timestampdiff(hour,date(End_Date),End_Date)
end as weekday_2,
case when date(Start_Date) != date(End_Date) then
(5*(DATEDIFF(date(End_Date),adddate(date(Start_Date),+1)) DIV 7) +
MID('0123455501234445012333450122234501101234000123450',7 * WEEKDAY(adddate(date(Start_Date),+1))
+ WEEKDAY(date(End_Date)) + 1, 1))* 24 end as weekday_3,
case when date(Start_Date) = date(End_Date) and weekday(Start_Date) in (5,6)
then timestampdiff(hour,Start_Date,End_Date)
when date(Start_Date) != date(End_Date) and weekday(Start_Date) in (5,6)
then 24-timestampdiff(hour,date(Start_Date),Start_Date)
end as weekend_1,
case when date(Start_Date) != date(End_Date) and weekday(End_Date) in (5,6)
then timestampdiff(hour,date(End_Date),End_Date)
end as weekend_2
from
TABLE_1
)
回答by Jagdeep Singh
@Amaynut Thanks
@Amaynut 谢谢
SELECT *
FROM eventList
WHERE date BETWEEN UNIX_TIMESTAMP('2017-08-01') AND UNIX_TIMESTAMP('2017/08/01');
above mention, code works and my problem solved.
上面提到，代码有效，我的问题解决了。
回答by Tenison
Try the following:
请尝试以下操作：
SELECT * FROM eventList WHERE
date BETWEEN
STR_TO_DATE('2013/03/26', '%Y/%m/%d')
AND
STR_TO_DATE('2013/03/27', '%y/%m/%d')
