mysql - 如何在 postgres 中按日期过滤 - 从 mysql 到 postgres

Question

更新:我正在尝试将 mysql 代码转换为 postgres 代码，我想在我的 where 子句中的两个日期之间进行过滤

原始Mysql

SELECT concat(month(DD1.open_date),'/', DATE_FORMAT(DD1.open_date, '%y')) AS month_year,
           venues.name AS venue_name,
           DD1.`server_name` AS SERVER_NAME,
           DD1.`server_id` AS `Server ID`,
           AVG(lr.`rating`) AS server_overall_rating,
           all_overall_rating,
           AVG(lr.`service`) AS server_service_rating,
           all_service_rating,
           avg(spend) AS server_check_avg,
           all_check_avg,
           avg(cover_avg) AS server_cover_avg,
           all_cover_avg
    FROM loyalty_visits DD1
    JOIN
      (SELECT avg(lr.rating) AS all_overall_rating,
              avg(lr.service) AS all_service_rating,
              avg(cover_avg) AS all_cover_avg,
              avg(spend) AS all_check_avg,
              open_date
       FROM loyalty_visits lv
       LEFT OUTER JOIN loyalty_ratings lr ON lr.loyalty_visit_id = lv.id
       WHERE lv.venue_id = 4046
       AND open_date BETWEEN date_sub(now(), interval 12 MONTH) AND now()
       GROUP BY lv.venue_ID,
                MONTH(open_date), year(open_date)) AS overall_cover_avg ON MONTH(overall_cover_avg.open_date) = MONTH(DD1.open_date)
    AND year(overall_cover_avg.open_date) = year(DD1.open_date)
    LEFT OUTER JOIN loyalty_member_visits lmv ON lmv.loyalty_visit_id = DD1.id
    LEFT OUTER JOIN loyalty_ratings lr ON lmv.loyalty_visit_id = lr.loyalty_visit_id
    JOIN venues ON venues.id = DD1.venue_id
    WHERE DD1.parent_venue_id = 4041
      AND DD1.server_id = 6991
      AND DD1.venue_id = 4046
      AND DD1.open_date BETWEEN date('2015-01-29 14:15:55') AND date('2013-09-29 10:15:55')
    GROUP BY DD1.venue_ID,
             MONTH(DD1.open_date), year(DD1.open_date)
    ORDER BY DD1.open_date LIMIT 6

Postgres注意:我知道我的投影与原始 mysql 相比并不完全存在，这是因为我实际上试图理解子查询(我不知道它是如何工作的)并且还试图了解一些选择语句的位置来自 mysql 版本，因此我可以充分转换为 Postgres。

还有

在 Mysql 中作为 DD1 的 loyalty_visits 在 Postgres 中更改为 members_visits 作为 mv，在 Postgres 中作为 lr 的 loyalty_ratings 在 Postgres 中更改为 just ratings as r。其余的应该还是一样的。

SELECT mv.check_number,
mv.open_date,
mv.code,
mv.cover_avg,
reservation_loyalty_user_id,
v.restaurant_id,
table_name,
terminal_id
FROM member_visits as mv
FROM member_visits as mv
JOIN
      (SELECT avg(lr.rating) AS all_overall_rating,
              avg(lr.service) AS all_service_rating,
              avg(cover_avg) AS all_cover_avg,
              avg(spend) AS all_check_avg,
              open_date
       FROM loyalty_visits lv
       LEFT OUTER JOIN ratings lr ON lr.loyalty_visit_id = lv.id
       WHERE lv.venue_id = 4046
         AND open_date BETWEEN date_sub(now(), interval 3 MONTH) AND now()
       GROUP BY lv.venue_ID,
                   MONTH(open_date), year(open_date)) AS overall_cover_avg  ON     MONTH(overall_cover_avg.open_date) = MONTH(DD1.open_date)
    AND year(overall_cover_avg.open_date) = year(DD1.open_date)
LEFT JOIN ratings r 
    ON mv.loyalty_visit_id = r.loyalty_visit_id
JOIN venues v
    ON v.id = mv.venue_id
WHERE mv.parent_venue_id = 4041
AND mv.server_id = 6991
AND mv.venue_id = 4046
AND mv.open_date BETWEEN date_trunc('2015/01/20', day) AND date_trunc('2013/09/29', day)
GROUP BY mv.venue_ID,
            MONTH(mv.open_date), YEAR(mv.open_date)
ORDER BY mv.open_date
limit 10

我遇到了两个错误:

1) 在这一行

AND open_date BETWEEN date_sub(now(), interval 12 MONTH) AND now(),

有没有可能从现在到12个月没有数据？我不确定如何调试它。

2) 我也收到一条错误消息整数的无效输入语法:“2015-01-2013 14:15:55”

我看过这篇文章Filter by Dates in SQL但我不确定我是否喜欢这个解决方案，它是一个 SQL 解决方案。然后我尝试使用 date_trunc，但我也不确定这是否正确。

我应该使用什么？

Answer 1

There is no date_sub() Postgres 中的函数。只需从时间戳(或日期)中减去时间间隔。您的间隔文字也是错误的。

SQL 标准要求将值放在单引号中，例如间隔“12”个月或间隔“5”分钟。 Postgres 还允许将值和单位用单引号括起来的语法 interval '1 month'

AND open_date BETWEEN now() - interval '12' MONTH AND now()

您的date_trunc 用法is also wrong .字符串文字 '2015/01/20' 只会在当前日期格式匹配时转换为日期。一般来说，无论日期格式设置如何，使用 ANSI 日期文字来确保您的 SQL 在每个环境中运行都比 好得多:

date_trunc('day', DATE '2015-01-20')

Postgres 也(始终)接受使用 ISO 格式的字符串文字，因此 '2015-01-20' 的工作方式与 DATE '2015-01-20' 在 Postgres 中

Postgres 中也没有date() 函数。我建议使用 ANSI 日期和时间戳文字。因此，不要使用 date('2015-01-29 14:15:55') 使用 timestamp '2015-01-29 14:15:55' SQL 标准。如果您不想使用 ISO 日期格式，则应使用 to_date() 或 to_timestamp() functions with an appropriate format mask .

Postgres 中也没有date_format 函数。使用 to_char()。同样这个 is documented in the manual

Postgres 中也没有year() 或month() 函数。使用 SQL 标准:extract(year from open_date) 或 extract(month from open_date) 或 Postgres 特定的 date_part() 函数。这也是documented in the manual