查询当前日期时间mysql [英] Query of current date in time mysql

问题描述

我在mysql中有一个日期时间类型的时间列，名为createdAt，所以我想找出这样的时间范围的数据

I have a column of time with type datetime in mysql called createdAt, so I want find out the data with time range like this

createdAt >= SUBDATE(DATE_SUB(curdate(), INTERVAL 1 WEEK), WEEKDAY(curdate()))
       and createdAt <= ADDDATE(DATE_SUB(curdate(), INTERVAL 1 WEEK), 6-WEEKDAY(curdate()))

假设基于此问题的 createdAt curdate() 是 2020 年 10 月 8 日，因此时间范围将是 2020 年 9 月 28 日至 2020 年 10 月 4 日

assume createdAt curdate() based on this question made are october 8th 2020, so that time range will be 28th september 2020 until 04th october 2020

但是当我手动检查时，如果我在另一个媒体中添加了一些时间范围，为什么计数会有点不同?例如:使用此查询，交易计数为 87 笔交易，但随后我使用另一种媒体计算了 2020 年 9 月 28 日 00:00:00 至 10 月 4 日 23:59:59 之间的交易，其 90 笔交易，是任何我可以添加查询日期范围内的时间范围?

But when I check manually, if I add some time range in another media, why the counting is little bit more different? For example: using this query, count of transaction is 87 transaction, but then I used another media to count the transaction between 28th september 2020 00:00:00 until 04th october 23:59:59 its 90 transaction, is that any I can add in query for time range in my query date range?

推荐答案

你的表达式 ADDDATE(DATE_SUB(curdate(), INTERVAL 1 WEEK), 6-WEEKDAY(curdate())) 只返回上周的日期部分，因此不包括从这一天开始的所有数据.

Your expression ADDDATE(DATE_SUB(curdate(), INTERVAL 1 WEEK), 6-WEEKDAY(curdate())) returns only date part of last week day, so all data after start this day is not included.

您有多种选择来解决这个问题:

You have couple of options to solve this:

1. Round createdAt 迄今为止没有时间部分:

SELECT *
FROM your_table
WHERE DATE(createdAt) BETWEEN 
    SUBDATE(DATE_SUB(curdate(), INTERVAL 1 WEEK), WEEKDAY(curdate())) AND
    ADDDATE(DATE_SUB(curdate(), INTERVAL 1 WEEK), 6-WEEKDAY(curdate()));

1. 在第二天使用 strong less 条件:

SELECT *
FROM your_table
WHERE 
    createdAt >= SUBDATE(DATE_SUB(curdate(), INTERVAL 1 WEEK), WEEKDAY(curdate())) AND
    createdAt < ADDDATE(DATE_SUB(curdate(), INTERVAL 1 WEEK), 7-WEEKDAY(curdate()));

这篇关于查询当前日期时间mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！