Thread: [DOCS] Reg Date/Time function
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/8.2/static/functions-datetime.html Description: Hi, I am going through PostgreSQL, for the first day. And it was great till now. One quick question/doubt regarding the function "justify_days(interval)" select justify_days(interval '365 days'); 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.
Per the docs, justify_days is to "Adjust interval so 30-day time periods are represented as months" so 360 days = 12 months = 1 year so 365 days is 1-year 5-days.
There are all sorts of oddities and special assumptions regarding date/time calculations made even more complicated by the need to support special use-cases such as 30/360 financial coupon factor calculations (every month is 30-days and years have 360 days).
Cheers,
Steve
On Mon, Jul 31, 2017 at 5:30 PM, <segu.sandeep@gmail.com> wrote:
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/8.2/static/functions- datetime.html
Description:
Hi,
I am going through PostgreSQL, for the first day. And it was great till
now.
One quick question/doubt regarding the function "justify_days(interval)"
select justify_days(interval '365 days');
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.
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
Thank you Steve for the information.
Thanks,
Sandeep Segu.
On Tue, Aug 1, 2017 at 2:04 PM, Steve Crawford <scrawford@pinpointresearch.com> wrote:
Per the docs, justify_days is to "Adjust interval so 30-day time periods are represented as months" so 360 days = 12 months = 1 year so 365 days is 1-year 5-days.There are all sorts of oddities and special assumptions regarding date/time calculations made even more complicated by the need to support special use-cases such as 30/360 financial coupon factor calculations (every month is 30-days and years have 360 days).Cheers,SteveOn Mon, Jul 31, 2017 at 5:30 PM, <segu.sandeep@gmail.com> wrote:The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/8.2/static/functions-datetim e.html
Description:
Hi,
I am going through PostgreSQL, for the first day. And it was great till
now.
One quick question/doubt regarding the function "justify_days(interval)"
select justify_days(interval '365 days');
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.
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
On 1 August 2017 at 12:30, <segu.sandeep@gmail.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 "justify_days(interval)"
>
> select justify_days(interval '365 days');
>
> 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
Thank you Mike.. Great information.
I heard about the 360-day calendar, for the first time and I am so thankful to you for this. I appreciate your time.
Thanks,
Sandeep Segu.
On Tue, Aug 1, 2017 at 7:20 PM, Mike Toews <mwtoews@gmail.com> wrote:
On 1 August 2017 at 12:30, <segu.sandeep@gmail.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 "justify_days(interval)"
>
> select justify_days(interval '365 days');
>
> 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