MYSQL query between two timestamps

Question

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.

score 95 · Accepted Answer · edited Jan 11 '19 at 11:41

95

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'

edited Jan 11 '19 at 11:41

simhumileco

31,877
16
137
115

answered Jan 09 '14 at 22:21

Frank Conry

2,694
3
29
35

if i create `$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 );` and use this `BETWEEN FROM_UNIXTIME($today_start) AND FROM_UNIXTIME($today_end)` It is not returning what it should return thus i have to use directly the variables. This one does work one dates(in readable forms though like 2013-03-26 00:00:01) but not on timestamps, as in my case But not in general. – MR_AMDEV Apr 06 '19 at 20:41
1

And why not supply directly the timestamps? – MR_AMDEV Apr 06 '19 at 20:43

score 14 · Answer 2 · edited Jun 03 '20 at 02:08

14

Try this one. It works for me.

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

edited Jun 03 '20 at 02:08

Ilyas karim

4,592
4
33
47

answered May 04 '15 at 18:43

androsfat

711
9
16

score 8 · Answer 3 · edited Jan 11 '19 at 11:40

8

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.

edited Jan 11 '19 at 11:40

simhumileco

31,877
16
137
115

answered Apr 27 '16 at 15:27

Amaynut

4,091
6
39
44

score 6 · Answer 4 · edited Jan 11 '19 at 11:37

6

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

edited Jan 11 '19 at 11:37

simhumileco

31,877
16
137
115

answered Aug 23 '18 at 10:13

Ragu S Mech

61
1
1

Why would anyone write the timestamps value in a query? See the other answers on how to use formatted dates. – mrkernelpanic Aug 23 '18 at 10:35
I don't know why this one works although if i have a timestamp and i would like to use `UNIX_TIMESTAMP(mytimestamp)` OR `FROM_UNIXTIME(mytimestamp)` In my case these both are not working and using a php variable /direct timestamp works ! – MR_AMDEV Apr 06 '19 at 20:37

Maahadev Tugaon · Answer 5 · 2020-08-07T14:42:11.283

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

edited Aug 07 '20 at 14:42

answered Aug 07 '20 at 08:20

Maahadev Tugaon

21
3

score 0 · Answer 6 · edited Jan 09 '14 at 23:14

0

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

edited Jan 09 '14 at 23:14

Angel.King.47

7,922
14
60
85

answered Jan 09 '14 at 22:53

Tenison

1

score 0 · Answer 7 · edited Jan 11 '19 at 11:38

0

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

edited Jan 11 '19 at 11:38

simhumileco

31,877
16
137
115

answered Aug 05 '17 at 17:05

Jagdeep Singh

319
3
7

score 0 · Answer 8 · answered Apr 16 '22 at 00:24

0

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'

answered Apr 16 '22 at 00:24

Yoda - user264474

93
1
8

Supriyanka Kharade · Answer 9 · 2018-12-30T17:56:21.603

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
    )

This isn't an answer to the question. Please revise your answer and provide an explanation. — jpp, Dec 30 '18 at 17:34

MYSQL query between two timestamps

9 Answers9

Linked