pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Erik Wienhold <ewie@ewie.name>
To: Marcos Pegoraro <marcos@f10.com.br>
To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Understanding years part of Interval
Date: Mon, 6 Feb 2023 14:59:11 +0100 (CET)
Message-ID: <1891019909.4271.1675691951281@office.mailbox.org> (raw)
In-Reply-To: <CAB-JLwaO2UfyaSJ7198f2nGvg7b0fztRzhSfTaz-PXa_ju6HOQ@mail.gmail.com>
References: <CAB-JLwaO2UfyaSJ7198f2nGvg7b0fztRzhSfTaz-PXa_ju6HOQ@mail.gmail.com>

> 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





view thread (5+ messages)  latest in thread

Message-ID: <1891019909.4271.1675691951281@office.mailbox.org>
Permalink:  ../1891019909.4271.1675691951281@office.mailbox.org/
Also on:    postgresql.org/message-id/1891019909.4271.1675691951281@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, marcos@f10.com.br
  Subject: Re: Understanding years part of Interval
  In-Reply-To: <1891019909.4271.1675691951281@office.mailbox.org>
  References: <CAB-JLwaO2UfyaSJ7198f2nGvg7b0fztRzhSfTaz-PXa_ju6HOQ@mail.gmail.com> <1891019909.4271.1675691951281@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