尝试下面的代码。 在我的情况下有效。 希望这可以帮助你!
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
)
$today_start = DateToTimestamp( date('Y-m-d h:i:s A', strtotime('today midnight')), $timezone ); $today_end = DateToTimestamp( date('Y-m-d h:i:s A', strtotime('-1 seconds ' ,strtotime('tomorrow midnight'))), $timezone );
并使用BETWEEN FROM_UNIXTIME($today_start) AND FROM_UNIXTIME($today_end)
,它没有返回应该返回的内容,因此我必须直接使用变量。这个可以在日期上工作(以可读形式,如2013-03-26 00:00:01),但不适用于时间戳,就像我的情况一样,但并非普遍适用。 - MR_AMDEV