pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedUnderstanding years part of Interval
5+ messages / 2 participants
[nested] [flat]
* Understanding years part of Interval
@ 2023-02-06 11:20 Marcos Pegoraro <marcos@f10.com.br>
0 siblings, 1 reply; 5+ messages in thread
From: Marcos Pegoraro @ 2023-02-06 11:20 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
I was just playing with some random timestamps for a week, for a month, for
a year ...
select distinct current_date+((random()::numeric)||'month')::interval from
generate_series(1,100) order by 1;
It´s with distinct clause because if you change that 'month' for a 'year'
it´ll return only 12 rows, instead of 100. So, why years part of interval
works differently than any other ?
select '1.01 week'::interval; --> 0 years 0 mons 7 days 1 hours 40 mins
48.00 secs
select '1.01 month'::interval; --> 0 years 1 mons 0 days 7 hours 12 mins
0.00 secs
select '1.01 year'::interval; --> 1 years 0 mons 0 days 0 hours 0 mins 0.00
secs
thanks
Marcos
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Understanding years part of Interval
@ 2023-02-06 13:59 Erik Wienhold <ewie@ewie.name>
parent: Marcos Pegoraro <marcos@f10.com.br>
0 siblings, 1 reply; 5+ messages in thread
From: Erik Wienhold @ 2023-02-06 13:59 UTC (permalink / raw)
To: Marcos Pegoraro <marcos@f10.com.br>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
> On 06/02/2023 12:20 CET Marcos Pegoraro <marcos@f10.com.br> wrote:
>
> I was just playing with some random timestamps for a week, for a month,
> for a year ...
>
> select distinct current_date+((random()::numeric)||'month')::interval from generate_series(1,100) order by 1;
> It´s with distinct clause because if you change that 'month' for a 'year'
> it´ll return only 12 rows, instead of 100. So, why years part of interval
> works differently than any other ?
>
> select '1.01 week'::interval; --> 0 years 0 mons 7 days 1 hours 40 mins 48.00 secs
> select '1.01 month'::interval; --> 0 years 1 mons 0 days 7 hours 12 mins 0.00 secs
> select '1.01 year'::interval; --> 1 years 0 mons 0 days 0 hours 0 mins 0.00 secs
Explained in https://www.postgresql.org/docs/15/datatype-datetime.html#DATATYPE-INTERVAL-INPUT:
Field values can have fractional parts: for example, '1.5 weeks' or
'01:02:03.45'. However, because interval internally stores only
three integer units (months, days, microseconds), fractional units
must be spilled to smaller units. Fractional parts of units greater
than months are rounded to be an integer number of months, e.g.
'1.5 years' becomes '1 year 6 mons'. Fractional parts of weeks and
days are computed to be an integer number of days and microseconds,
assuming 30 days per month and 24 hours per day, e.g., '1.75 months'
becomes 1 mon 22 days 12:00:00. Only seconds will ever be shown as
fractional on output.
Internally interval values are stored as months, days, and
microseconds. This is done because the number of days in a month
varies, and a day can have 23 or 25 hours if a daylight savings time
adjustment is involved.
--
Erik
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Understanding years part of Interval
@ 2023-02-06 17:33 Marcos Pegoraro <marcos@f10.com.br>
parent: Erik Wienhold <ewie@ewie.name>
0 siblings, 2 replies; 5+ messages in thread
From: Marcos Pegoraro @ 2023-02-06 17:33 UTC (permalink / raw)
To: Erik Wienhold <ewie@ewie.name>; pgsql-hackers <pgsql-hackers@postgresql.org>
Em seg., 6 de fev. de 2023 às 10:59, Erik Wienhold <ewie@ewie.name>
escreveu:
> > On 06/02/2023 12:20 CET Marcos Pegoraro <marcos@f10.com.br> wrote:
> >
> > I was just playing with some random timestamps for a week, for a month,
> > for a year ...
> >
> > select distinct current_date+((random()::numeric)||'month')::interval
> from generate_series(1,100) order by 1;
> > It´s with distinct clause because if you change that 'month' for a 'year'
> > it´ll return only 12 rows, instead of 100. So, why years part of interval
> > works differently than any other ?
> >
> > select '1.01 week'::interval; --> 0 years 0 mons 7 days 1 hours 40 mins
> 48.00 secs
> > select '1.01 month'::interval; --> 0 years 1 mons 0 days 7 hours 12 mins
> 0.00 secs
> > select '1.01 year'::interval; --> 1 years 0 mons 0 days 0 hours 0 mins
> 0.00 secs
>
> Explained in
> https://www.postgresql.org/docs/15/datatype-datetime.html#DATATYPE-INTERVAL-INPUT
> :
>
> Field values can have fractional parts: for example, '1.5 weeks' or
> '01:02:03.45'. However, because interval internally stores only
> three integer units (months, days, microseconds), fractional units
> must be spilled to smaller units. Fractional parts of units greater
> than months are rounded to be an integer number of months, e.g.
> '1.5 years' becomes '1 year 6 mons'. Fractional parts of weeks and
> days are computed to be an integer number of days and microseconds,
> assuming 30 days per month and 24 hours per day, e.g., '1.75
> months'
> becomes 1 mon 22 days 12:00:00. Only seconds will ever be shown as
> fractional on output.
>
> Internally interval values are stored as months, days, and
> microseconds. This is done because the number of days in a month
> varies, and a day can have 23 or 25 hours if a daylight savings
> time
> adjustment is involved.
>
> I´ve sent this message initially to general and Erik told me it's
documented, so it's better to hackers help me if this has an explaining why
it's done that way.
select '1 year'::interval = '1.05 year'::interval -->true ?
I cannot agree that this select returns true.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Understanding years part of Interval
@ 2023-02-06 19:29 Erik Wienhold <ewie@ewie.name>
parent: Marcos Pegoraro <marcos@f10.com.br>
1 sibling, 0 replies; 5+ messages in thread
From: Erik Wienhold @ 2023-02-06 19:29 UTC (permalink / raw)
To: pgsql-general
> On 06/02/2023 18:33 CET Marcos Pegoraro <marcos@f10.com.br> wrote:
>
> Em seg., 6 de fev. de 2023 às 10:59, Erik Wienhold <ewie@ewie.name> escreveu:
> > > On 06/02/2023 12:20 CET Marcos Pegoraro <marcos@f10.com.br> wrote:
> > >
> > > I was just playing with some random timestamps for a week, for a month,
> > > for a year ...
> > >
> > > select distinct current_date+((random()::numeric)||'month')::interval from generate_series(1,100) order by 1;
> > > It´s with distinct clause because if you change that 'month' for a 'year'
> > > it´ll return only 12 rows, instead of 100. So, why years part of interval
> > > works differently than any other ?
> > >
> > > select '1.01 week'::interval; --> 0 years 0 mons 7 days 1 hours 40 mins 48.00 secs
> > > select '1.01 month'::interval; --> 0 years 1 mons 0 days 7 hours 12 mins 0.00 secs
> > > select '1.01 year'::interval; --> 1 years 0 mons 0 days 0 hours 0 mins 0.00 secs
> >
> > Explained in https://www.postgresql.org/docs/15/datatype-datetime.html#DATATYPE-INTERVAL-INPUT:
> >
> > Field values can have fractional parts: for example, '1.5 weeks' or
> > '01:02:03.45'. However, because interval internally stores only
> > three integer units (months, days, microseconds), fractional units
> > must be spilled to smaller units. Fractional parts of units greater
> > than months are rounded to be an integer number of months, e.g.
> > '1.5 years' becomes '1 year 6 mons'. Fractional parts of weeks and
> > days are computed to be an integer number of days and microseconds,
> > assuming 30 days per month and 24 hours per day, e.g., '1.75 months'
> > becomes 1 mon 22 days 12:00:00. Only seconds will ever be shown as
> > fractional on output.
> >
> > Internally interval values are stored as months, days, and
> > microseconds. This is done because the number of days in a month
> > varies, and a day can have 23 or 25 hours if a daylight savings time
> > adjustment is involved.
> >
> I´ve sent this message initially to general and Erik told me it's documented,
> so it's better to hackers help me if this has an explaining why it's done that way.
>
> select '1 year'::interval = '1.05 year'::interval -->true ?
> I cannot agree that this select returns true.
The years are converted to months and the fractional month is rounded half up:
1.05 year = 12.6 month
=> 1 year 0.6 month
=> 1 year 1 month (after rounding)
Compare that to 12.5 months to see when the rounding occurs:
12.5 month / 12 month
=> 1.0416... years
Plug 1.0416 and 1.0417 into the interval to observe the rounding:
=# select '1.0416 year'::interval, '1.0417 year'::interval;
interval | interval
----------+--------------
1 year | 1 year 1 mon
--
Erik
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Understanding years part of Interval
@ 2023-02-07 12:00 Marcos Pegoraro <marcos@f10.com.br>
parent: Marcos Pegoraro <marcos@f10.com.br>
1 sibling, 0 replies; 5+ messages in thread
From: Marcos Pegoraro @ 2023-02-07 12:00 UTC (permalink / raw)
To: pgsql-hackers <pgsql-hackers@postgresql.org>
>
> The years are converted to months and the fractional month is rounded half
>> up:
>>
>> 1.05 year = 12.6 month
>> => 1 year 0.6 month
>> => 1 year 1 month (after rounding)
>>
>> Compare that to 12.5 months to see when the rounding occurs:
>>
>> 12.5 month / 12 month
>> => 1.0416... years
>>
>> Plug 1.0416 and 1.0417 into the interval to observe the rounding:
>>
>> =# select '1.0416 year'::interval, '1.0417 year'::interval;
>> interval | interval
>> ----------+--------------
>> 1 year | 1 year 1 mon
>>
>> I understood what you explained, but cannot agree that it's correct.
> Run these and you'll see the first and second select are fine, the third
> ... why ?
>
> select distinct current_date + ((random()::numeric) * '1 year'::interval)
> from generate_series(1,100) order by 1;
> select distinct current_date + ((random()::numeric) * '12
> month'::interval) from generate_series(1,100) order by 1;
> select distinct current_date + ((random()::numeric) || 'year')::interval
> from generate_series(1,100) order by 1;
>
> So, I have to think ... never use fractional parts on years, right ?
>
Only to be written, if somebody has to work with fractional parts of years.
This way works
select distinct (random()::numeric) * ('1 year'::interval) from
generate_series(1,100) order by 1;
This way doesn´t
select distinct ((random()::numeric) || 'year')::interval from
generate_series(1,100) order by 1;
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2023-02-07 12:00 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-02-06 11:20 Understanding years part of Interval Marcos Pegoraro <marcos@f10.com.br>
2023-02-06 13:59 ` Erik Wienhold <ewie@ewie.name>
2023-02-06 17:33 ` Marcos Pegoraro <marcos@f10.com.br>
2023-02-06 19:29 ` Erik Wienhold <ewie@ewie.name>
2023-02-07 12:00 ` Marcos Pegoraro <marcos@f10.com.br>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox