sql选择过去45天的所有数据

mbzjlibv 于 2021-06-25 发布在 Mysql

关注(0)|答案(5)|浏览(422)

我有“警报”表与日期字段-targetdate。
我想选择过去45天的所有数据。
我尝试了下面的代码，但它没有返回任何结果。。。

SELECT userID, refID, `targetDate`
FROM alerts
WHERE type = 'travelSoon'
AND DATEDIFF( CURDATE( ) , targetDate ) > 45

table

id     userID  type    refID   createDate  targetDate  lastSendDate    sent    valid
1   26  travelSoon  NO  2018-05-02 13:54:25 0000-00-00  2018-05-02 00:00:00 0   1
2   26  travelSoon  NO  2018-05-02 13:55:50 2018-06-01  0000-00-00 00:00:00 0   1
3   26  travelSoon  DK  2018-05-02 13:56:12 2018-12-01  0000-00-00 00:00:00 0   1
4   26  travelSoon      2018-05-02 13:59:50 0000-00-00  0000-00-00 00:00:00 0   1
5   26  travelSoon      2018-05-02 14:00:09 2018-08-01  0000-00-00 00:00:00 0   1
6   26  travelSoon  DK  2018-05-02 14:00:48 2018-08-01  0000-00-00 00:00:00 0   1
7   26  travelSoon      2018-05-02 16:45:18 2018-05-01  0000-00-00 00:00:00 0   1
8   26  travelSoon  RO  2018-05-02 16:45:45 2018-04-01  0000-00-00 00:00:00 0   1

sql mysql

来源：https://stackoverflow.com/questions/50269894/sql-select-all-data-that-past-45-days

5条答案

按热度按时间

c9qzyr3d1#

SELECT userID, refID, `targetDate`
FROM alerts
WHERE type = 'travelSoon'
AND targetDate >= ( CURDATE() - INTERVAL 45 DAY )

赞(0）回复(0）举报 2021-06-25

2ul0zpep2#

ansi标准语法为：

SELECT userID, refID, `targetDate`
FROM alerts
WHERE type = 'travelSoon' AND
      targetDate >= CURRENT_DATE - interval '45 day' AND
      targetDate <= CURRENT_DATE;

在mysql中（您的语法建议：

SELECT userID, refID, `targetDate`
FROM alerts
WHERE type = 'travelSoon' AND
      targetDate >= CURRENT_DATE - interval 45 day AND
      targetDate <= CURRENT_DATE;

赞(0）回复(0）举报 2021-06-25

pbgvytdp3#

试试这个。。。

SELECT userid, refid, `targetdate` 
FROM   alerts 
WHERE  type = 'travelSoon' 
       AND Datediff(Curdate(), targetDate) < 45 -- or <=45 
       AND Datediff(Curdate(), targetDate) > 0

在线演示：http://www.sqlfiddle.com/#!9/4ecdc0/4/0
如果你只使用 Datediff(Curdate(), targetDate) < 45 条件下，它可能返回过去和将来的日期。请参考下表。

Today: May 10, 2018

+----+-------------+
| id | targetDate  |  DATEDIFF(CURDATE(), targetDate) 
+----+-------------+
|  2 | ????-??-?? |                             -22 
|  3 | ????-??-?? |                            -205 
|  5 | ????-??-?? |                             -83 
|  6 | ????-??-?? |                             -83 
|  7 | 2018-05-01  |                               9 
|  8 | 2018-04-01  |                              39 
+----+-------------+

为了避免这种情况，你可以使用另一种情况，像这样。。。

Datediff(Curdate(), targetDate) > 0

赞(0）回复(0）举报 2021-06-25

irlmq6kh4#

使用 DATEDIFF() 是个坏主意。它阻止了使用索引的能力，还有一种替代方法不。。。

SELECT *
  FROM alerts
 WHERE type = 'travelSoon'

   AND targetDate >= DATEADD(DAY, -45, GETDATE())    -- SQL Server

   AND targetDate >= CURDATE() - INTERVAL 45 DAY     -- MySQL

http://www.sqlfiddle.com/#!9月4日

赞(0）回复(0）举报 2021-06-25

a9wyjsp75#

在mssql中 DATEDIFF(interval, date1, date2) 返回间隔 date2 - date1 .
应从以下列表中选择间隔：

- year, yyyy, yy = Year
- quarter, qq, q = Quarter
- month, mm, m = month
- dayofyear = Day of the year
- day, dy, y = Day
- week, ww, wk = Week
- weekday, dw, w = Weekday
- hour, hh = hour
- minute, mi, n = Minute
- second, ss, s = Second
- millisecond, ms = Millisecond`

然后使用：

SELECT userID, refID, `targetDate`
FROM alerts
WHERE type = 'travelSoon'
AND DATEDIFF(day, targetDate, GETDATE() ) > 45

对于mysql，您可以使用 TIMESTAMPDIFF(unit,date1,date2) 返回 date1 - date2 . unit 可从中选择 MICROSECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR. ```
SELECT userID, refID, targetDate
FROM alerts
WHERE type = 'travelSoon'
AND TIMESTAMPDIFF(DAY, CURDATE( ), targetDate) > 45

赞(0）回复(0）举报 2021-06-25

我来回答

sql选择过去45天的所有数据

5条答案

相关问题

热门标签

最新问答