how to calculate only days between two dates in postgres sql query .

Question

Suppose I have given two dates, if difference is one month then it should be display as 30 days.Even months also need to convert into days I have tried with age( date,now()::timestamp without time zone) but it is giving months-dates-years format. ex: 10 mons 24 days 13:57:40.5265.But I need following way. For example if two months difference is there then I need output as 60 days.

But the difference between the same day in February and March is 28 or 29 days? And the difference between the same day in March and April is 31 days. Even if you extract the # of days you're not gonna get 30 days for each month to month comparison. — Scott Marlowe
– Scott Marlowe, Commented Apr 7, 2017 at 20:48
Note that in 9.6.1 I get days for this query: select '2017-10-03 00:00:00'::timestamp - '2017-02-03 00:00:00'::timestamp; ?column? ---------- 242 days — Scott Marlowe
– Scott Marlowe, Commented Apr 7, 2017 at 20:50

pozs · Accepted Answer · 2017-04-07 11:23:36Z

15

Don't use the age() function for date/time arithmetic. It only returns "symbolic" results (which are good enough for human representation, but almost meaningless for date/time calculations; compared to the standard difference).

The standard difference operator (-) returns day-based results for both date, timestamp and timestamp with time zone (the former returns days as int, the latter two return day-based intervals):

From the day-based intervals you can extract days with the extract() function:

select current_date - '2017-01-01',
       extract(day from now()::timestamp - '2017-01-01 00:00:00'),
       extract(day from now()            - '2017-01-01 00:00:00Z');

http://rextester.com/RBTO71933

edited Apr 7, 2017 at 11:23

answered Apr 7, 2017 at 10:45

pozs

36.6k5 gold badges61 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user330315 Over a year ago

age() returns an interval not a "symbolic" result. Which is a data type that can be used very easily in calculations and is definitely not "meaningless"

pozs Over a year ago

@a_horse_with_no_name from the docs age(timestamp, timestamp): Subtract arguments, producing a "symbolic" result that uses years and months, rather than just days, yes technically its type is interval, but given its (the function's) semantics and the general interval justification rules, it's not meant to be used in date/time arithmetics (maybe in some edge-cases).

Łukasz Kamiński · Accepted Answer · 2017-04-07 10:29:31Z

0

Both of those will give that result.

select make_interval(days => ((extract(epoch from '2016-04-10'::date) - extract(epoch from '2016-02-10'::date))/(60*60*24))::integer)::text;

select format('%1$s days', ((extract(epoch from '2016-04-10'::date) - extract(epoch from '2016-02-10'::date))/(60*60*24))::integer);

answered Apr 7, 2017 at 10:29

Łukasz Kamiński

6,0401 gold badge26 silver badges33 bronze badges

Collectives™ on Stack Overflow

how to calculate only days between two dates in postgres sql query .

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related