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:

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'

Try this one. It works for me.

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

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.

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

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

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

@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.

You can even pass variables:

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

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
	)