두 타임스탬프 사이의 MYSQL 쿼리

두 타임스탬프 사이의 MYSQL 쿼리

DB 테이블에 다음 항목이 있습니다.

eventName(varchar 100) -> myEvent
date(timestamp) -> 2013-03-26 09:00:00

다음 쿼리를 사용하려고 합니다.

SELECT * 
FROM eventList 
WHERE `date` BETWEEN UNIX_TIMESTAMP(1364256001) AND UNIX_TIMESTAMP(1364342399)

즉, 2013-03-26 00:00:01 ~ 2013-03-26 23:59:59

하지만 결과는 0입니다.

날짜 범위를 넓히려고 해 봤지만, 확실히 그 범위 내에 결과가 있습니다.

어떤 도움이라도 감사합니다.

시험:

SELECT * 
FROM eventList 
WHERE  `date` BETWEEN FROM_UNIXTIME(1364256001) AND FROM_UNIXTIME(1364342399)

또는

SELECT * 
FROM eventList WHERE  `date` 
BETWEEN '2013-03-26 00:00:01' AND '2013-03-26 23:59:59'

이거 드셔보세요.저는 좋아요.

SELECT * FROM eventList
WHERE DATE(date) 
  BETWEEN 
    '2013-03-26' 
  AND 
    '2013-03-27'

날짜를 다음 날짜로 변환하기만 하면 됩니다.UNIX_TIMESTAMP다음과 같이 쿼리를 작성할 수 있습니다.

SELECT *
FROM eventList
WHERE
  date BETWEEN
      UNIX_TIMESTAMP('2013/03/26')
      AND
      UNIX_TIMESTAMP('2013/03/27 23:59:59');

시간을 지정하지 않으면 MySQL은 다음을 가정합니다.00:00:00지정된 날짜의 시간으로 표시됩니다.

SELECT * FROM `orders` WHERE `order_date_time`  BETWEEN 1534809600 AND 1536718364

이거 먹어봐 나한텐 효과가 있었어

SELECT * from bookedroom
    WHERE UNIX_TIMESTAMP('2020-8-07 5:31')
        between UNIX_TIMESTAMP('2020-8-07 5:30') and
        UNIX_TIMESTAMP('2020-8-09 5:30')

다음을 시도해 보십시오.

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')

@Amaynut Thanks

SELECT * 
FROM eventList 
WHERE date BETWEEN UNIX_TIMESTAMP('2017-08-01') AND UNIX_TIMESTAMP('2017/08/01');

위의 언급, 코드가 작동하고 문제가 해결되었습니다.

변수를 전달할 수도 있습니다.

$timestamp_start = '2022-04-07 20:00:00.000'; // example
$timestamp_end   = '2022-04-21 20:00:01.000'; // example


AND table.date_created BETWEEN '$timestamp_start' AND '$timestamp_end'

아래 코드를 사용해 보십시오.내 경우엔 통했어.이게 도움이 됐으면 좋겠네요!

    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
    )

언급URL : https://stackoverflow.com/questions/21032335/mysql-query-between-two-timestamps