Re: Reg Date/Time function

On 1 August 2017 at 12:30, <segu(dot)sandeep(at)gmail(dot)com> wrote:
> Hi,
> I am going through PostgreSQL, for the first day. And it was great till
> now.
> One quick question/doubt regarding the function &quot;justify_days(interval)&quot;
>
> select justify_days(interval &#39;365 days&#39;);
>
> this statement returns 1 year 5 days, whereas I feel it should be just 1
> year.
>
> Please correct me if I am wrong.. Thanks for all your time.

It seems you are trying to convert a time interval type to days. The
most reliable way to get this is to extract the epoch, which is in
number of seconds, then convert this to days (divide by 60 * 60 * 24).

SELECT x, extract(epoch from x)/86400 AS days
FROM (
SELECT '1 year'::interval AS x
UNION ALL SELECT '365 days'
) AS sub;

x | days
----------+--------
1 year | 365.25
365 days | 365
(2 rows)

A typical "year" indeed has 365.25 days, when you consider leap years
typically every 4th. As noted previously, justify_days(interval) has a
special use for 360-day calendars[1].

[1] https://en.wikipedia.org/wiki/360-day_calendar