MySQL中PostgreSQL的date_trunc [英] PostgreSQL's date_trunc in mySQL

问题描述

最近，我已经熟悉PostgreSQL(使用8.2)，并发现date_trunc函数对于轻松匹配某些天/月/等之间的时间戳非常有用. 我认为，该功能的真正用途来自于这样一个事实，即它可以将输出保持为时间戳的格式.

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.

我不得不切换到mySQL(5.0)并找到一些日期功能，而这些功能比较缺乏.提取函数似乎很有用，我发现的日期函数解决了我的一些问题，但是有什么方法可以复制PostgreSQL的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?

以下是我以前如何使用date_trunc将查询的时间戳与包括当前月份在内的最近4个月进行匹配的一个示例，但前提是已经有一个星期进入了本月:

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:

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

我不知道如何在mySQL中重新创建这样的规定.所以，最后我的问题是，是否可以通过尝试复制date_trunc(以及如何)在mySQL中完成这种查询，或者我是否需要以不同的方式开始研究这些类型的查询才能使其正常工作在mySQL(以及有关如何做到这一点的建议)?

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

推荐答案

extract函数似乎很有用，我发现的date函数解决了我的一些问题，但是有什么方法可以复制PostgreSQL的date_trunc吗?

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?

确实， EXTRACT 看起来将是此特定情况下最接近的匹配项.

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

您在PG中的原始代码:

Your original code in PG:

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

使用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)

尽管功能上应该相同，但这实际上是在进行比较之前将日期整理为YYYYMM字符串.

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

另一个选择是使用 DATE_FORMAT 重建日期字符串并将其强制到月份的开始:

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

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

另外，请注意，即使对字段进行索引，MySQL在处理日期范围方面的能力也很差.如果不小心，您可能最终会进行全表扫描.

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中PostgreSQL的date_trunc的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！