CODEWITHSUNDEEP
sqlpostgresql
Matus Hmelar
Matus Hmelar

Reputation: 448

How to group sum results by date with custom start time PostrgresQL

I am trying to group my sum results by custom day in Postgresql. As regular day starts at 00:00 , I would like mine to start at 04:00am , so if there is entry with time 2019-01-03 02:23 it would count into '2019-01-02' instead.

Right now my code looks like this: Bottom part works perfectly on day type 00:00 - 23.59 , however I would like to group it by my upper range created above. I just don't know how to connect those two parts.

with dateRange as(
SELECT
generate_series(
MIN(to_date(payments2.paymenttime,'DD Mon YYYY')) + interval '4 hour',
max(to_date(payments2.paymenttime,'DD Mon YYYY')),
'24 hour') as theday
from payments2
)

select 
sum(cast(payments2.servicecharge as money)) as total,
to_date(payments2.paymenttime,'DD Mon YYYY') as date
from payments2
group by date

Result like this 

+------------+------------+
|    total   |    date    |
+------------+------------+
|     20     | 2019-01-01 |
+------------+------------+
|     60     | 2019-01-02 |
+------------+------------+
|     35     | 2019-01-03 |
+------------+------------+
|     21     | 2019-01-04 |
+------------+------------+

Many thanks for your help.

Upvotes: 0

Views: 45

Answers (1)

cogitoergosum
cogitoergosum

Reputation: 661

If I didn't misunderstand your question, you just need to subtract 4 hours from the timestamp before casting to date, you don't even need the CTE.

Something like 

select 
sum(cast(payments2.servicecharge as money)) as total,
(to_timestamp(payments2.paymenttime,'DD Mon YYYY HH24:MI:SS') - interval '4 hours')::date as date
from payments2
group by date

Yu may need to use a different format in the to_timestamp function depending on the format of the payments2.paymenttime string

Upvotes: 1

Related Questions