从具有创建日期的表中获取上周的数据

Question

使用下面的查询检索上周的数据，但我得到的错误是

Postgres错误：“强制转换”位置或附近的语法错误: 127

我不知道错误在哪里：

SELECT count(*), extract(day from createdon) AS period
FROM orders
WHERE servicename =:serviceName AND  createdon BETWEEN 
    CAST(NOW() AS CAST(DATE-EXTRACT(DOW FROM NOW()) AS INTEGER-7)) AND
    CAST(NOW() AS CAST(DATE-EXTRACT(DOW from NOW()) AS INTEGER))
GROUP BY  extract(day from createdon)
ORDER BY extract(day from createdon);

Answer 1

你让事情变得太复杂了。要获得上周的数据，只需在“本周开始”减去7天之后得到所有数据：

“本周开始”可以使用date_trunc('week', current_date)进行评估。

如果你减去7天，你就得到了前一周的开始：date_trunc('week', current_date) - interval '7' day。如果你减去一天，你就得到了前一周的结束。

date_trunc总是使用星期一作为一周的开始，所以如果你的一周从周日开始，那就再重复一次，例如date_trunc('week', current_date)::date - 8将是前一周的星期日。

把这一切加在一起：

SELECT count(*), extract(day from createdon) AS period
FROM orders
WHERE servicename =:serviceName 
  AND createdon 
      between date_trunc('week', current_date)::date - 7 
          and date_trunc('week', current_date)::date - 1
GROUP BY extract(day from createdon)
ORDER BY extract(day from createdon);

如果您的列是时间戳列，您可以简单地将createdon转换为一个日期，以消除时间部分：

  AND createdon::date 
      between date_trunc('week', current_date)::date - 7 
          and date_trunc('week', current_date)::date

请注意，createdon上的常规索引不会用于该条件，如果需要性能，则需要在createdon::date上创建索引。

如果不能(或不希望)创建这样的索引，则需要使用与between不同的内容

  AND createdon >= date_trunc('week', current_date)::date - 7 
  AND createdon < date_trunc('week', current_date)::date

(注意使用的是<而不是<=，这就是“中间”使用的内容)

另一种选择是将日期信息转换为周和年的组合：

AND to_char(createdon, 'iyyy-iw') = to_char(date_trunc('week', current_date)::date - 7, 'iyyy-iw')

请注意，我在上面使用了ISO周定义。如果使用的是不同的周编号系统，则需要为to_char()函数提供不同的to_char()。

Answer 2

如果您使用的是北美周系统(其周从周日开始)，那么您的原始方法就足够好了，只需使用正确的CAST(<epr> AS <type>)语法即可。

SELECT   COUNT(*),
         EXTRACT(DAY FROM createdon) period
FROM     orders
WHERE    servicename = 'Cell Tower Monitoring'
AND      createdon BETWEEN CURRENT_DATE - CAST(EXTRACT(DOW FROM CURRENT_DATE) AS INTEGER) - 7 
                       AND CURRENT_DATE - CAST(EXTRACT(DOW FROM CURRENT_DATE) AS INTEGER) - 1
GROUP BY EXTRACT(DAY FROM createdon)
ORDER BY EXTRACT(DAY FROM createdon);

注意事项：这假定createdon是DATE列。如果它是一个TIMESTAMP (或TIMESTAMP WITH TIME ZONE)，您需要一个稍微不同的版本：

SELECT   COUNT(*),
         EXTRACT(DAY FROM createdon) period
FROM     orders
WHERE    servicename = 'Cell Tower Monitoring'
AND      createdon >= CURRENT_TIMESTAMP - INTERVAL '1 day' * (EXTRACT(DOW FROM CURRENT_TIMESTAMP) + 7)
AND      createdon <  CURRENT_TIMESTAMP - INTERVAL '1 day' *  EXTRACT(DOW FROM CURRENT_TIMESTAMP)
GROUP BY EXTRACT(DAY FROM createdon)
ORDER BY EXTRACT(DAY FROM createdon);

如果您想使用ISO系统(其周从周一开始)，那么只需使用ISODOW而不是DOW。或者，您可以使用date_trunc('week', ...)函数，比如在姓名的答案中。

如果您想再使用一个星期的系统(f.ex )。从周六开始)，您需要在CASE表达式中添加一些额外的逻辑，因为从DOW中减去1不会在这一周开始时给出预期的结果(f.ex )。星期六是两周前的一周)。