代码之家  ›  专栏  ›  技术社区  ›  AndreaNobili

为什么我不能在一个日期范围内获得记录?

  •  1
  • AndreaNobili  · 技术社区  · 7 年前

    MySql

    气象预报

    CREATE TABLE MeteoForecast (
      id                   BigInt(20) NOT NULL AUTO_INCREMENT,
      localization_id      BigInt(20) NOT NULL,
      seasonal_forecast_id BigInt(20),
      meteo_warning_id     BigInt(20),
      start_date           DateTime NOT NULL,
      end_date             DateTime NOT NULL,
      min_temp             Float,
      max_temp             Float,
      icon_link            VarChar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL, 
      PRIMARY KEY (
          id
      )
    ) ENGINE=InnoDB AUTO_INCREMENT=3 ROW_FORMAT=DYNAMIC DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
    

    它包含以下数据:

    id                   localization_id      start_date              end_date                min_temp             max_temp             icon_link                                                                                                                                                                                                                                                      
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    1                    1                    18/09/2017 06:00:00     18/09/2017 12:00:00     15                   24                   Mostly_Cloudy_Icon.png                                                                                                                                                                                                                                         
    2                    1                    18/09/2017 12:00:00     18/09/2017 18:00:00     15                   24                   Light_Rain.png                                                                                                                                                                                                                                                 
    3                    1                    19/09/2017 06:00:00     19/09/2017 12:00:00     12                   22                   Mostly_Cloudy_Icon.png                                                                                                                                                                                                                                         
    4                    1                    19/09/2017 12:00:00     19/09/2017 18:00:00     13                   16                   Mostly_Cloudy_Icon.png                                                                                                                                                                                                                                         
    5                    1                    20/09/2017 06:00:00     20/09/2017 12:00:00     18                   26                   Light_Rain.png                                                                                                                                                                                                                                                 
    6                    1                    20/09/2017 12:00:00     20/09/2017 18:00:00     17                   25                   Light_Rain.png                                                                                                                                                                                                                                                 
    

    它表示meteo预测,如您所见,它有开始日期时间和结束日期时间。因此,对于特定位置(由 本地化id 字段)我可以在同一天有更多记录(不同的小时范围,在示例中我创建了2个范围:从06:00到12:00和从12:00到18:00)。

    我想从开始日期起2天内检索所有相关记录,因此我尝试这样做:

    select * from MeteoForecast where 
    start_date between  '18/09/2017 06:00:00' and '20/09/2017 06:00:00'
    order by start_date desc;
    

    但它给了我0条记录。

    我也试过:

    select * from MeteoForecast where 
    start_date >=  '18/09/2017 06:00:00' and 
    end_date < '20/09/2017 18:00:00'
    order by start_date desc;
    

    有什么问题?我错过了什么?如何解决此问题?

    1 回复  |  直到 7 年前
        1
  •  3
  •   Hatim Stovewala    7 年前

    试试这个。

    select * from MeteoForecast 
    where start_date between  '2017-09-18 06:00:00' and '2017-09-20 06:00:00'
    order by start_date desc;
    

    Datetime