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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

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

kes-kes
The following bug has been logged on the website:

Bug reference:      14850
Logged by:          Eugen Konkov
Email address:      [hidden email]
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.

Thank you.


--
Sent via pgsql-bugs mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Reply | Threaded
Open this post in threaded view
|

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

Pavel Stehule
Hi

2017-10-11 12:35 GMT+02:00 <[hidden email]>:
The following bug has been logged on the website:

Bug reference:      14850
Logged by:          Eugen Konkov
Email address:      [hidden email]
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


Thank you.


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

Reply | Threaded
Open this post in threaded view
|

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

a.zakirov
In reply to this post by kes-kes
On Wed, Oct 11, 2017 at 10:35:12AM +0000, [hidden email] 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 ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Reply | Threaded
Open this post in threaded view
|

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

kes-kes
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" <[hidden email]>:

> On Wed, Oct 11, 2017 at 10:35:12AM +0000, [hidden email] 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 ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Previous Thread Next Thread