如何使用 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.

这就是我的时间戳.

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) 不存在

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屋！