CODEWITHSUNDEEP
sqlpostgresqldate-arithmetic
Roman Martyshchuk
Roman Martyshchuk

Reputation: 245

How can i get number of quarters between two dates in PostgreSQL?

I have MS SQL function DATEDIFF

SELECT DATEDIFF(QQ, 0, '2018-09-05')

that returns 474(integer).

What is PostgreSQL equivalent of this function ?

Upvotes: 5

Views: 5610

Answers (4)

yopLa
yopLa

Reputation: 1

Let's say you have two dates in the example table: start_date and end_date

Here is how to get the difference between these dates in number of quarters:

select * (year(end_date)*4 + quarter(end_date)) - (year(start_date)*4 + quarter(start_date)) as quarter_diff 
from example

Upvotes: 0

Tim Yeung
Tim Yeung

Reputation: 21

I think the current accepted is incorrect.

e.g. if you change date '1900-01-01' to '2017-07-01' you actually get 6 quarter diff.

  • But expected result should be:
    • include first and last quarter: 5
    • exclude first and last quarter: 3
    • exclude last quarter: 4
    select 
        age('2018-09-05','2017-07-01') age,
        (EXTRACT(QUARTER FROM TIMESTAMP '2018-09-05') + 
      date_part('year',age('2018-09-05','2017-07-01')) * 4) -1 QUARTER

Result:

age                 |quarter|
--------------------|-------|
1 year 2 mons 4 days|      6|

The reason is age('2018-09-05','2017-07-01') return 1 year 2 mons 4 days (maybe more than 12 month).

The answer i use to calculate number of quarter: 

with params as (
    select 
        '2017-07-01 00:00:00'::timestamp date_from,
        '2018-09-05 00:00:00'::timestamp date_to
)
select      
    extract( quarter from date_from)::int date_from_quarter,
    (extract( quarter from date_to))::int date_to_quarter,
    age(date_to, date_from) date_diff,
    (extract(year from date_to) - extract(year from date_from))::int how_many_yr,
    -- exclude begin and end
    (extract(year from date_to) - extract(year from date_from))::int* 4 - extract( quarter from date_from)::int + (extract( quarter from date_to))::int - 1 quarter_diff,
    -- exclude begin quarter 
    (extract(year from date_to) - extract(year from date_from))::int* 4 - extract( quarter from date_from)::int + (extract( quarter from date_to))::int quarter_diff_include_current_quarter
    from params
;

Result: 

date_from_quarter|date_to_quarter|date_diff           |how_many_yr|quarter_diff|quarter_diff_include_current_quarter|
-----------------|---------------|--------------------|-----------|------------|------------------------------------|
                3|              3|1 year 2 mons 4 days|          1|           3|                                   4|

Upvotes: 2

Juozas
Juozas

Reputation: 935

Its complicated on Postgres:

( (DATE_PART('year',  '2018-09-05') - DATE_PART('year', '1900-01-01')) * 12 
   + (DATE_PART('month',  '2018-09-05') - DATE_PART('month', '1900-01-01'))
) / 4

Additional resources: topic

Upvotes: 0

D-Shih
D-Shih

Reputation: 46239

MSSQL 0 is the date '1900-01-01' in DATEDIFF(QQ, 0, '2018-09-05'), that function will get the number of QUARTER from 1900-01-01 to 2018-09-05

But PostgreSQL does not have a QUARTER number function.

You can try to use

  1. EXTRACT(QUARTER FROM TIMESTAMP '2018-09-05') to get this month QUARTER number.

  2. date_part('year',age('2018-09-05','1900-01-01')) get year number between '2018-09-05' and '1900-01-01'.

then do some calculation.

select (EXTRACT(QUARTER FROM TIMESTAMP '2018-09-05') + 
      date_part('year',age('2018-09-05','1900-01-01')) * 4) -1 QUARTER

Results:

| quarter |
|---------|
|     474 |

Upvotes: 2

Related Questions