[mysql] MYSQL query between two timestamps

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

but it is giving me 0 results.

I have tried expanding the date range with no luck and there are definitely results within the range.

any help is appreciated.

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
    )

Try this its worked for me

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 `orders` WHERE `order_date_time`  BETWEEN 1534809600 AND 1536718364

You just need to convert your dates to UNIX_TIMESTAMP. You can write your query like this:

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:00 as the time for the given date.

Try this one. It works for me.

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

@Amaynut Thanks

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.

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