在两个时间戳之间查询MYSQL数据

Question

我在我的数据库表中有以下条目。

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个结果。

我尝试扩大日期范围，但没有成功，在这个范围内肯定有结果。

感激任何帮助。

- Ben

Answer 1

尝试：

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'

- Frank Conry

---

如果我创建了 $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 ); 并使用 BETWEEN FROM_UNIXTIME($today_start) AND FROM_UNIXTIME($today_end)，它没有返回应该返回的内容，因此我必须直接使用变量。这个可以在日期上工作（以可读形式，如2013-03-26 00:00:01），但不适用于时间戳，就像我的情况一样，但并非普遍适用。 - MR_AMDEV

为什么不直接提供时间戳呢？ - MR_AMDEV

Answer 2

试一试这个。对我有效。

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

- androsfat

Answer 3

您只需要将日期转换为 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。

- Amaynut

Answer 4

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

- Ragu S Mech

---

为什么有人会在查询中编写时间戳值？请参阅其他答案以了解如何使用格式化日期。 - mrkernelpanic

我不知道为什么这个可以工作，尽管如果我有一个时间戳并且想要使用 UNIX_TIMESTAMP(mytimestamp) 或者 FROM_UNIXTIME(mytimestamp)。在我的情况下，这两个都不起作用，而使用 PHP 变量/直接时间戳却可以工作！ - MR_AMDEV

Answer 5

尝试这个，它对我有效

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

- Maahadev Tugaon

Answer 6

你甚至可以传递变量：

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

- Yoda - user264474

Answer 7

请尝试以下方法：

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

- Tenison

Answer 8

@Amaynut 谢谢

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

如上所述，代码可以运行并且我的问题已经解决。

- Jagdeep Singh

Answer 9

尝试下面的代码。 在我的情况下有效。 希望这可以帮助你！

    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
    )

- Supriyanka Kharade

---

这不是问题的答案。请修改您的答案并提供解释。 - jpp