BUG #14850: Implement optinal additinal parameter for 'justify' date/time function

Hi

2017-10-11 12:35 GMT+02:00 <kes-kes@yandex.ru>:

The following bug has been logged on the website:

Bug reference: 14850
Logged by: Eugen Konkov
Email address: kes-kes@yandex.ru
PostgreSQL version: 10.0
Operating system: Linux mint 18: Linux work 4.4.0-57-generic #78-Ubu
Description:

Hi. I try to do next math:

select extract( month from justify_days( timestamp '2016-01-31' +interval
'1
month' -timestamp '2016-01-31') );
date_part
-----------
0
(1 row)

I expect `1` but get `0`. But here everything is right:

Adjust interval so 30-day time periods are represented as months

https://www.postgresql.org/docs/9.6/static/functions-datetime.html

But with ability to setup justify date the math will be more sharp.

Please implement next feature:

select extract( month from justify_days( timestamp '2016-01-31' +interval
'1
month' -timestamp '2016-01-31'), timestamp '2016-01-31' );
date_part
-----------
1
(1 row)

This is useful when I try to calculate how much month are left between
service start and end dates.

This is not the bug, so pgsql-hackers, pgsql-general are better places for
this discussion

I am thinking so your request has sense, and should be registered in ToDo
list https://wiki.postgresql.org/wiki/Todo

You can try to connect people from PostgreSQL Pro company for
implementation.

Regards

Pavel

<div><br /></div><div><br /></div><div>-------- Пересылаемое сообщение--------</div><div>11.10.2017, 17:12, "Pavel Stehule" &lt;pavel.stehule@gmail.com&gt;:</div><div><br /></div><div><div dir="ltr">Hi<br /><div><div><br /><div><span>2017-10-11 12</span>:35 GMT+02:00 <span dir="ltr">&lt;<a href="mailto:kes-kes@yandex.ru" target="_blank">kes-kes@yandex.ru</a>&gt;</span>:<br /><blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex;">The following bug has been logged on the website:<br />
<br />
Bug reference:      14850<br />
Logged by:          Eugen Konkov<br />
Email address:      <a href="mailto:kes-kes@yandex.ru">kes-kes@yandex.ru</a><br />
PostgreSQL version: 10.0<br />
Operating system:   Linux mint 18: Linux work 4.4.0-57-generic #78-Ubu<br />
Description:<br />
<br />
Hi. I try to do next math:<br />
<br />
select extract( month from justify_days( timestamp '<span>2016-01-31</span>' +interval '1<br />
month' -timestamp '<span>2016-01-31</span>') );<br />
 date_part<br />
-----------<br />
         0<br />
(1 row)<br />
<br />
I expect `1` but get `0`. But here everything is right:<br />
<br />
&gt;Adjust interval so 30-day time periods are represented as months<br />
<br />
<a href="https://www.postgresql.org/docs/9.6/static/functions-datetime.html&quot; rel="noreferrer" target="_blank">https://www.postgresql.org/&lt;wbr />docs/9.6/static/functions-<wbr />datetime.html</a><br />
<br />
But with ability to setup justify date the math will be more sharp.<br />
<br />
Please implement next feature:<br />
<br />
select extract( month from justify_days( timestamp '<span>2016-01-31</span>' +interval '1<br />
month' -timestamp '<span>2016-01-31</span>'), timestamp '<span>2016-01-31</span>' );<br />
 date_part<br />
-----------<br />
         1<br />
(1 row)<br />
<br />
This is useful when I try to calculate how much month are left between<br />
service start and end dates.<br /></blockquote><div><br /></div><div>This is not the bug, so pgsql-hackers, pgsql-general are better places for this discussion <br /></div><div><br /></div><div>I am thinking so your request has sense, and should be registered in ToDo list <a href="https://wiki.postgresql.org/wiki/Todo&quot;&gt;https://wiki.postgresql.org/wiki/Todo&lt;/a&gt;&lt;/div&gt;&lt;div&gt;&lt;br /></div><div>You can try to connect people from PostgreSQL Pro company for implementation.</div><div><br /></div><div>Regards</div><div><br /></div><div>Pavel</div><div><br /></div><blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex;">
<br />
Thank you.<br />
<span><font color="#888888"><br />
<br />
--<br />
Sent via pgsql-bugs mailing list (<a href="mailto:pgsql-bugs@postgresql.org">pgsql-bugs@postgresql.org</a>)<br />
To make changes to your subscription:<br />
<a href="http://www.postgresql.org/mailpref/pgsql-bugs&quot; rel="noreferrer" target="_blank">http://www.postgresql.org/&lt;wbr />mailpref/pgsql-bugs</a><br />
</font></span></blockquote></div><br /></div></div></div>
</div><div><br /></div><div>-------- Конец пересылаемого сообщения --------</div>

On Wed, Oct 11, 2017 at 10:35:12AM +0000, kes-kes@yandex.ru wrote:

Adjust interval so 30-day time periods are represented as months

https://www.postgresql.org/docs/9.6/static/functions-datetime.html

Yes, it seems that it is because timestamp operations return '29 days':

=# SELECT timestamp '2016-01-31' +interval '1 month' -timestamp '2016-01-31';
?column?
----------
29 days

You can also try the following, maybe it is appropriate for you:

=# SELECT timestamp '2016-01-31' -timestamp '2016-01-31' +interval '1 month';
?column?
----------
1 mon

=# SELECT extract( month from justify_days( timestamp '2016-01-31'
-timestamp '2016-01-31' +interval '1 month') );
date_part
-----------
1

--
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

No. Your example work only for those values.

Try this:
SELECT extract( month from justify_days( timestamp '2016-02-29' -timestamp '2016-01-31') );
date_part
-----------
0
(1 row)

select extract( month from justify_days( timestamp '2016-02-29' -timestamp '2016-01-31'), timestamp '2016-01-31' );
date_part
-----------
1
(1 row)

12.10.2017, 12:15, "Arthur Zakirov" <a.zakirov@postgrespro.ru>:

On Wed, Oct 11, 2017 at 10:35:12AM +0000, kes-kes@yandex.ru wrote:

 >Adjust interval so 30-day time periods are represented as months

 https://www.postgresql.org/docs/9.6/static/functions-datetime.html

Yes, it seems that it is because timestamp operations return '29 days':

=# SELECT timestamp '2016-01-31' +interval '1 month' -timestamp '2016-01-31';
 ?column?
----------
 29 days

You can also try the following, maybe it is appropriate for you:

=# SELECT timestamp '2016-01-31' -timestamp '2016-01-31' +interval '1 month';
 ?column?
----------
 1 mon

=# SELECT extract( month from justify_days( timestamp '2016-01-31'
-timestamp '2016-01-31' +interval '1 month') );
 date_part
-----------
         1

--
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

A 'month' is an abstract measurement of time. Sometimes it's 29 days, 30,
or 31. You cannot say "I have 30 days, how many months is that?" because
the answer is "it depends".

<date> - <date> gives you an interval in days. In your example, you took
Jan 31 2016 and added "1 month". Postgres says "I know feb 2016 is 29
days" and did it automatically for you. When you then subtracted Jan 31
2016, you now have "29 days". Postgres can no longer say "that is 1
month" because you cannot go that direction.

You are also using extract(month from X) incorrectly if you want the number
of months between any time period. That will only return a value between
0 and 11.

It will also be difficult because you are starting from a random day in the
month, making it hard to really know what you mean. Postgres' age()
function may be able to help you with 'months'.

flpg=# select age( '2016-02-01'::timestamp, '2016-01-01'::timestamp );
age
-------
1 mon

flpg=# select age( '2016-02-29'::timestamp, '2016-01-31'::timestamp );
age
---------
29 days
(1 row)

flpg=# select age( '2016-03-01'::timestamp, '2016-01-31'::timestamp );
age
-------------
1 mon 1 day

On Thu, Oct 12, 2017 at 4:00 AM, KES <kes-kes@yandex.ru> wrote:

<div>huh... how did I miss `age` function???</div><div> </div><div>Thanks. I give me the idea. I will add 5days when the month of day is greater or equal to 28</div><div> </div><div><div>select age( '2016-02-29'::date +'5days'::interval, '2016-01-31'::date+'5days'::interval );</div><div>  age  </div><div>-------</div><div> 1 mon</div><div>(1 row)</div><div> </div><div>It seems resolves my issue</div><div> </div><div> </div></div><div><br /></div><div><br /></div><div>12.10.2017, 17:38, "Brian Dunavant" &lt;brian@omniti.com&gt;:</div><blockquote type="cite"><div dir="ltr">A 'month' is an abstract measurement of time.  Sometimes it's 29 days, 30, or 31.   You cannot say "I have 30 days, how many months is that?" because the answer is "it depends".<div><br /></div><div>&lt;date&gt; - &lt;date&gt; gives you an interval in days.   In your example, you took Jan 31 2016 and added "1 month".  Postgres says "I know feb 2016 is 29 days" and did it automatically for you.   When you then subtracted Jan 31 2016, you now have "29 days".   Postgres can no longer say "that is 1 month" because you cannot go that direction.</div><div><br /></div><div>You are also using extract(month from X) incorrectly if you want the number of months between any time period.   That will only return a value between 0 and 11.</div><div><br /></div><div>It will also be difficult because you are starting from a random day in the month, making it hard to really know what you mean.  Postgres' age() function may be able to help you with 'months'.  </div><div><div><br /></div><div>flpg=# select age( '<span>2016-02-01</span>'::timestamp, '<span>2016-01-01</span>'::timestamp );</div><div>  age</div><div>-------</div><div> 1 mon</div></div><div><br /></div><div><div>flpg=# select age( '<span>2016-02-29</span>'::timestamp, '<span>2016-01-31</span>'::timestamp );</div><div>   age</div><div>---------</div><div> 29 days</div><div>(1 row)</div></div><div><br /></div><div><div>flpg=# select age( '<span>2016-03-01</span>'::timestamp, '<span>2016-01-31</span>'::timestamp );</div><div>     age</div><div>-------------</div><div> 1 mon 1 day</div></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div></div><div><br /><div>On Thu, Oct 12, 2017 at 4:00 AM, KES <span dir="ltr">&lt;<a href="mailto:kes-kes@yandex.ru" target="_blank">kes-kes@yandex.ru</a>&gt;</span> wrote:<br /><blockquote style="margin:0 0 0 0.8ex;border-left:1px #ccc solid;padding-left:1ex;"><div><br /></div><div><br /></div><div>-------- Пересылаемое сообщение--------</div><div>11.10.2017, 17:12, "Pavel Stehule" &lt;<a href="mailto:pavel.stehule@gmail.com" target="_blank">pavel.stehule@gmail.com</a>&gt;:</div><div><div><div><br /></div><div><div dir="ltr">Hi<br /><div><div><br /><div><span><span>2017-10-11 12</span></span>:35 GMT+02:00 <span dir="ltr">&lt;<a href="mailto:kes-kes@yandex.ru" target="_blank">kes-kes@yandex.ru</a>&gt;</span>:<br /><blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex;">The following bug has been logged on the website:<br />
<br />
Bug reference:      14850<br />
Logged by:          Eugen Konkov<br />
Email address:      <a href="mailto:kes-kes@yandex.ru" target="_blank">kes-kes@yandex.ru</a><br />
PostgreSQL version: 10.0<br />
Operating system:   Linux mint 18: Linux work 4.4.0-57-generic #78-Ubu<br />
Description:<br />
<br />
Hi. I try to do next math:<br />
<br />
select extract( month from justify_days( timestamp '<span><span>2016-01-31</span></span>' +interval '1<br />
month' -timestamp '<span><span>2016-01-31</span></span>') );<br />
 date_part<br />
-----------<br />
         0<br />
(1 row)<br />
<br />
I expect `1` but get `0`. But here everything is right:<br />
<br />
&gt;Adjust interval so 30-day time periods are represented as months<br />
<br />
<a href="https://www.postgresql.org/docs/9.6/static/functions-datetime.html&quot; rel="noreferrer" target="_blank">https://www.postgresql.org/doc&lt;wbr />s/9.6/static/functions-datetim<wbr />e.html</a><br />
<br />
But with ability to setup justify date the math will be more sharp.<br />
<br />
Please implement next feature:<br />
<br />
select extract( month from justify_days( timestamp '<span><span>2016-01-31</span></span>' +interval '1<br />
month' -timestamp '<span><span>2016-01-31</span></span>'), timestamp '<span><span>2016-01-31</span></span>' );<br />
 date_part<br />
-----------<br />
         1<br />
(1 row)<br />
<br />
This is useful when I try to calculate how much month are left between<br />
service start and end dates.<br /></blockquote><div><br /></div><div>This is not the bug, so pgsql-hackers, pgsql-general are better places for this discussion <br /></div><div><br /></div><div>I am thinking so your request has sense, and should be registered in ToDo list <a href="https://wiki.postgresql.org/wiki/Todo&quot; target="_blank">https://wiki.postgresql.org/&lt;wbr />wiki/Todo</a></div><div><br /></div><div>You can try to connect people from PostgreSQL Pro company for implementation.</div><div><br /></div><div>Regards</div><div><br /></div><div>Pavel</div><div><br /></div><blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex;">
<br />
Thank you.<br />
<span><font color="#888888"><br />
<br />
--<br />
Sent via pgsql-bugs mailing list (<a href="mailto:pgsql-bugs@postgresql.org" target="_blank">pgsql-bugs@postgresql.org</a>)<br />
To make changes to your subscription:<br />
<a href="http://www.postgresql.org/mailpref/pgsql-bugs&quot; rel="noreferrer" target="_blank">http://www.postgresql.org/mail&lt;wbr />pref/pgsql-bugs</a><br />
</font></span></blockquote></div><br /></div></div></div>
</div><div><br /></div></div></div><div>-------- Конец пересылаемого сообщения --------</div>

</blockquote></div><br /></div>
</blockquote>