如何使用postgres获取日期 [英] how to get dates with postgres

问题描述

我试图查询并返回过去 4 周内所有记录的总和.我让它工作但是我在使用时间戳时.我正在尝试更改精度并且只使用日期.

I was trying to query and return the sum of all records in the last 4 past weeks. I had it working however I while using timestamps. and I am trying to change the precision and and just use dates.

简单地说，我只想要日期范围，而不是时间戳范围.

briefly instead of timestamp ranges I want just date ranges.

这是我对时间戳的看法.

this what I had with timestamps.

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;

我尝试用 daterange 更改 tstzrange 但我收到以下错误

I tried changing tstzrange with daterange but I get the following error

函数日期范围(带时区的时间戳，带时区的时间戳，未知)不存在

function daterange(timestamp with time zone, timestamp with time zone, unknown) does not exist

我对 SQL 没有太多背景，因此非常感谢您的简单解释.

I don't have that much background with SQL so an small explanation is much appreciated.

推荐答案

您只需将日期转换为日期即可.但是，这样做时要小心.您应该沿独占(默认上限)使用 7 的倍数.因此它看起来像:

You would simply cast to date. However, be careful when you do that. You should use multiples of 7 along exclusive (default upper bounds). Thus it looks like:

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;

这篇关于如何使用postgres获取日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！