如何与postgres约会

admin

如何与postgres约会

sql

我试图查询并返回过去四个星期中所有记录的总和。我使用了时间戳，但是可以使用。我正在尝试更改精度，并仅使用日期。

简短地代替时间戳范围，我只需要日期范围。

这就是我在时间戳记上的意思。

with date_ranges (range_name, range_dates) as
              ( values ('week_0', tstzrange ((now()-interval '6 days'),  now(),'[]'))
                      , ('week_1', tstzrange ((now()-interval '13 days'), (now()-interval '7 days'), '[]'))
                      , ('week_2', tstzrange ((now()-interval '20 days'), (now()-interval '14 days'),'[]'))
                      , ('week_3', tstzrange ((now()-interval '27 days'), (now()-interval '21 days'),'[]')) 
              ) 
  select range_name, range_dates, sum("transactionTotal") total_amount 
  from "MoneyTransactions" mt
  join date_ranges dr on (mt."createdAt" <@ range_dates)
  group by range_name, range_dates
  order by range_name;

我尝试更改tstzrange，daterange但出现以下错误

函数daterange（带时区的时间戳，带时区的时间戳，未知）不存在

我对SQL的了解不多，所以对它的解释不多。

阅读 390

2021-07-01

共1个答案

admin

您只需要转换为最新即可。但是，在执行此操作时要小心。沿排他（默认上限）应使用7的倍数。因此，它看起来像：

with date_ranges (range_name, range_dates) as
              ( values ('week_0', tstzrange ((now()::date-interval '7 days'),  now()::date))
                      , ('week_1', tstzrange ((now()::date-interval '14 days'), (now()::date-interval '7 days')))
                      , ('week_2', tstzrange ((now()::date-interval '21 days'), (now()::date-interval '14 days')))
                      , ('week_3', tstzrange ((now()::date-interval '28 days'), (now()::date-interval '21 days')))
              )
          select range_name, range_dates, sum("transactionTotal") total_amount 
  from "MoneyTransactions" mt
  join date_ranges dr on (mt."createdAt" <@ range_dates)
  group by range_name, range_dates
  order by range_name;

2021-07-01