MYSQL query between two timestamps

Question

I have the following entry in my DB table  eventName varchar 100  - gt  myEvent date timestamp  - gt  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

User · Answer

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

User · Answer

SELECT   FROM  orders  WHERE  order date time   BETWEEN 1534809600 AND 1536718364

User · Answer

Try this one  It works for me   SELECT   FROM eventList WHERE DATE date     BETWEEN       2013-03-26     AND       2013-03-27

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] MYSQL query between two timestamps

Examples related to mysql

Examples related to unix-timestamp