CODEWITHSUNDEEP
postgresqltimestampintervals
Ivan
Ivan

Reputation: 804

How to date_trunc by custom trimester in PostgreSQL?

I'm working on a project where the fiscal year is measured by trimesters, and for as odd as it sounds every trimester is measured from the 10th day of the month instead of the 1st due to country regulations. So the first trimester is considered to start on Jan 10th and ends three months after on the 9th of that month. Is there a way to date_trunc or simply group a timestamp column using these custom trimesters in PostgreSQL?

So far I've only been able to query the data by month/day/week with something like:

FROM myTable SELECT( SUM(price) as total, date_trunc('month', 'timpestamp') ) GROUP BY (total)

Upvotes: 2

Views: 1958

Answers (2)

klin
klin

Reputation: 121654

Use the function (or the expression inside it):

create or replace function get_trimester(timestamp)
returns integer language sql immutable as $$
    select (extract('month' from $1::date- 9)::int- 1)/ 3 + 1
$$;

Check the function for some dates:

with my_table(t) as (
values 
    ('2017-01-09'::timestamp),
    ('2017-01-10'),
    ('2017-04-09'),
    ('2017-04-10'),
    ('2017-07-09'),
    ('2017-07-10'),
    ('2017-10-09'),
    ('2017-10-10'),
    ('2017-12-31')
)

select t, get_trimester(t)
from my_table

          t          | get_trimester 
---------------------+---------------
 2017-01-09 00:00:00 |             4
 2017-01-10 00:00:00 |             1
 2017-04-09 00:00:00 |             1
 2017-04-10 00:00:00 |             2
 2017-07-09 00:00:00 |             2
 2017-07-10 00:00:00 |             3
 2017-10-09 00:00:00 |             3
 2017-10-10 00:00:00 |             4
 2017-12-31 00:00:00 |             4
(9 rows)

Upvotes: 2

Vao Tsun
Vao Tsun

Reputation: 51559

yo can join the result agants prepared intervals, eg:

t=# select starts,starts+'3 month'::interval ends,mod(ord,4) from  generate_series('2015-10-01'::date,'2018-10-01'::date,'3 month'::interval) with ordinality t(starts,ord);
         starts         |          ends          | mod
------------------------+------------------------+-----
 2015-10-01 00:00:00+00 | 2016-01-01 00:00:00+00 |   1
 2016-01-01 00:00:00+00 | 2016-04-01 00:00:00+00 |   2
 2016-04-01 00:00:00+00 | 2016-07-01 00:00:00+00 |   3
 2016-07-01 00:00:00+00 | 2016-10-01 00:00:00+00 |   0
 2016-10-01 00:00:00+00 | 2017-01-01 00:00:00+00 |   1
 2017-01-01 00:00:00+00 | 2017-04-01 00:00:00+00 |   2
 2017-04-01 00:00:00+00 | 2017-07-01 00:00:00+00 |   3
 2017-07-01 00:00:00+00 | 2017-10-01 00:00:00+00 |   0
 2017-10-01 00:00:00+00 | 2018-01-01 00:00:00+00 |   1
 2018-01-01 00:00:00+00 | 2018-04-01 00:00:00+00 |   2
 2018-04-01 00:00:00+00 | 2018-07-01 00:00:00+00 |   3
 2018-07-01 00:00:00+00 | 2018-10-01 00:00:00+00 |   0
 2018-10-01 00:00:00+00 | 2019-01-01 00:00:00+00 |   1
(13 rows)

here you can use the remainder of rownum order divided by number of semestres to get the semester number (of course you need to deal with 0 - either call it four, or jus start from minus one semester and just use mod(ord,4)+1, or use case when and so on)

Upvotes: 2

Related Questions