pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Erik Wienhold <ewie@ewie.name>
To: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Subject: Re: Understanding years part of Interval
Date: Mon, 6 Feb 2023 20:29:58 +0100 (CET)
Message-ID: <371463617.216036.1675711798455@office.mailbox.org> (raw)
In-Reply-To: <1803669038.215195.1675711296688@office.mailbox.org>
References: <CAB-JLwaO2UfyaSJ7198f2nGvg7b0fztRzhSfTaz-PXa_ju6HOQ@mail.gmail.com>
	<1891019909.4271.1675691951281@office.mailbox.org>
	<CAB-JLwY01WNi4BYwnkrMW+MWXEP51gZSk4kg+SEGcTCrfFywDw@mail.gmail.com>
	<1803669038.215195.1675711296688@office.mailbox.org>

> 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





view thread (5+ messages)  latest in thread

Message-ID: <371463617.216036.1675711798455@office.mailbox.org>
Permalink:  ../371463617.216036.1675711798455@office.mailbox.org/
Also on:    postgresql.org/message-id/371463617.216036.1675711798455@office.mailbox.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: ewie@ewie.name
  Subject: Re: Understanding years part of Interval
  In-Reply-To: <371463617.216036.1675711798455@office.mailbox.org>
  References: <CAB-JLwaO2UfyaSJ7198f2nGvg7b0fztRzhSfTaz-PXa_ju6HOQ@mail.gmail.com> <1891019909.4271.1675691951281@office.mailbox.org> <CAB-JLwY01WNi4BYwnkrMW+MWXEP51gZSk4kg+SEGcTCrfFywDw@mail.gmail.com> <1803669038.215195.1675711296688@office.mailbox.org> <371463617.216036.1675711798455@office.mailbox.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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