PostgreSQL 在 mySQL 中的 date_trunc

PostgreSQL's date_trunc in mySQL

提问by Yottagray

Recently, I have been getting familiar with PostgreSQL(using 8.2) and found the date_trunc function extremely useful for easily matching time stamps between certain days/months/etc. The real usefulness of the function, I believe, comes from the fact that it keeps the output in the format of a timestamp.

最近，我开始熟悉 PostgreSQL（使用 8.2）并发现 date_trunc 函数对于轻松匹配某些天/月/等之间的时间戳非常有用。我相信，该函数的真正用处在于它以时间戳的格式保存输出。

I have had to switch to mySQL(5.0) and find some of the date functions rather lacking in comparison. The extract function seems useful and the date function I have found solves some of my problems, but is there any way to replicate PostgreSQL's date_trunc?

我不得不切换到 mySQL(5.0) 并找到一些比较缺乏的日期函数。提取函数似乎很有用，我发现的日期函数解决了我的一些问题，但是有没有办法复制 PostgreSQL 的 date_trunc？

Following is an example of how I used to use date_trunc to match queried timestamps to only the last 4 months including the current month, but only if a week has passed into this month already:

以下是我过去如何使用 date_trunc 将查询的时间戳仅与包括当前月份在内的过去 4 个月的时间戳进行匹配的示例，但前提是本月已经过去了一周：

WHERE date_trunc('month', QUERY_DATE) BETWEEN 
    date_trunc('month', now()) - INTERVAL '4 MONTH' AND 
    date_trunc('month', now() - INTERVAL '1 WEEK')

I have no idea how to recreate such a stipulation in mySQL. So, my question at the end of the day, is whether this type of query can be accomplished in mySQL by trying replicate date_trunc(and how) or whether I need to start looking at these types of queries in a different way to make them work in mySQL(and suggestions on how to do that)?

我不知道如何在 mySQL 中重新创建这样的规定。所以，我在一天结束时的问题是，这种类型的查询是否可以通过尝试复制 date_trunc（以及如何）在 mySQL 中完成，或者我是否需要开始以不同的方式查看这些类型的查询以使它们工作在 mySQL（以及如何做到这一点的建议）中？

回答by Charles

The extract function seems useful and the date function I have found solves some of my problems, but is there any way to replicate PostgreSQL's date_trunc?

提取函数似乎很有用，我发现的日期函数解决了我的一些问题，但是有没有办法复制 PostgreSQL 的 date_trunc？

Indeed, EXTRACTlooks like it's going to be the closest match for this specific case.

事实上，EXTRACT看起来这将是这个特定案例的最接近的匹配。

Your original code in PG:

您在 PG 中的原始代码：

WHERE date_trunc('month', QUERY_DATE) BETWEEN 
    date_trunc('month', now()) - INTERVAL '4 MONTH' AND 
    date_trunc('month', now() - INTERVAL '1 WEEK')

Using EXTRACT:

使用EXTRACT：

WHERE EXTRACT(YEAR_MONTH FROM QUERY_DATE)
      BETWEEN
          EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 4 MONTH)
      AND
          EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 1 WEEK)

While it should be functionally identical, this is actually mangling the dates into a YYYYMM string before doing the comparison.

虽然它在功能上应该是相同的，但这实际上是在进行比较之前将日期转换为 YYYYMM 字符串。

Another option would be using DATE_FORMATto rebuild the date string and force it to the beginning of the month:

另一种选择是使用DATE_FORMAT重建日期字符串并将其强制为月初：

WHERE DATE_FORMAT(QUERY_DATE, '%Y-%m-01')
      BETWEEN
          DATE_FORMAT(NOW() - INTERVAL 4 MONTH, '%Y-%m-01')
      AND
          DATE_FORMAT(NOW() - INTERVAL 1 WEEK, '%Y-%m-01')

Also, be aware that MySQL is really poor at dealing with date ranges, even when the field is indexed. You're probably going to end up with a full table scan if you aren't careful.

另外，请注意 MySQL 在处理日期范围方面确实很差，即使该字段已编入索引。如果您不小心，您可能会以全表扫描结束。

回答by Dmitry Efimenko

late to the party, but...

聚会迟到了，但是……

there is a way to get truncated date given you know the interval. For example, if the interval is MONTH, you could get today's date (now()) truncated to the month using the following:

鉴于您知道时间间隔，有一种方法可以截断日期。例如，如果间隔为MONTH，则可以now()使用以下命令将今天的日期 ( ) 截断为月份：

select date_add('1900-01-01', interval TIMESTAMPDIFF(MONTH, '1900-01-01', now()) MONTH);

Given the above, one could create a function to take care of the other intervals as well:

鉴于上述情况，我们可以创建一个函数来处理其他间隔：

DELIMITER //
create function date_trunc(vInterval varchar(7), vDate timestamp)
returns timestamp
begin
    declare toReturn timestamp;

    if vInterval = 'year' then set toReturn = date_add('1900-01-01', interval TIMESTAMPDIFF(YEAR, '1900-01-01', vDate) YEAR);
    elseif vInterval = 'quarter' then set toReturn = date_add('1900-01-01', interval TIMESTAMPDIFF(QUARTER, '1900-01-01', vDate) QUARTER);
    elseif vInterval = 'month' then set toReturn = date_add('1900-01-01', interval TIMESTAMPDIFF(MONTH, '1900-01-01', vDate) MONTH);
    elseif vInterval = 'week' then set toReturn = date_add('1900-01-01', interval TIMESTAMPDIFF(WEEK, '1900-01-01', vDate) WEEK);
    elseif vInterval = 'day' then set toReturn = date_add('1900-01-01', interval TIMESTAMPDIFF(DAY, '1900-01-01', vDate) DAY);
    elseif vInterval = 'hour' then set toReturn = date_add('1900-01-01', interval TIMESTAMPDIFF(HOUR, '1900-01-01', vDate) HOUR);
    elseif vInterval = 'minute' then set toReturn = date_add('1900-01-01', interval TIMESTAMPDIFF(MINUTE, '1900-01-01', vDate) MINUTE);
    END IF;

    return toReturn;
end//
DELIMITER ;

Use it like so:

像这样使用它：

select date_trunc('quarter', now())

回答by Ulad Kasach

Here is a function that mimics postgres' DATE_TRUNCcontract using the DATE_FORMATmysql function that @Charles has recommended above.

这是一个DATE_TRUNC使用DATE_FORMAT@Charles 上面推荐的mysql 函数模仿 postgres合同的函数。

DROP FUNCTION IF EXISTS DATE_TRUNC;
CREATE FUNCTION DATE_TRUNC(
  in_granularity ENUM('hour', 'day', 'month', 'year'),
  in_datetime datetime(6)
)
RETURNS datetime(6)
DETERMINISTIC
BEGIN
  IF (in_granularity = 'hour') THEN
    RETURN DATE_FORMAT(in_datetime, '%Y-%m-%d %H:00:00.0000');
  END IF;
  IF (in_granularity = 'day') THEN
    RETURN DATE_FORMAT(in_datetime, '%Y-%m-%d 00:00:00.0000');
  END IF;
  IF (in_granularity = 'month') THEN
    RETURN DATE_FORMAT(in_datetime, '%Y-%m-01 00:00:00.0000');
  END IF;
  IF (in_granularity = 'year') THEN
    RETURN DATE_FORMAT(in_datetime, '%Y-01-01 00:00:00.0000');
  END IF;
END;