pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Thomas Lockhart <lockhart@alumni.caltech.edu>
Cc: Tulassay Zsolt <zsolt@tek.bke.hu>
Cc: pgsql-sql@hub.org
Subject: Re: [SQL] timespan problem
Date: Mon, 03 Jan 2000 10:05:15 -0500
Message-ID: <8094.946911915@sss.pgh.pa.us> (raw)
In-Reply-To: <38705200.DFFB9931@alumni.caltech.edu>
References: <Pine.LNX.4.10.10001012142220.17313-100000@tek.bke.hu>
	<20530.946771110@sss.pgh.pa.us>
	<38705200.DFFB9931@alumni.caltech.edu>

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
>> ... The problem appears to be an internal
>> overflow in timespan_in...
>> Thomas, you want to deal with this one? Or is this code all going
>> away in 7.0 anyway?

> I would guess the code is staying, though with bug fixes for this case
> ;-) I'll look at it...

I've thought about it some more and now understand why timespan is
represented as months + seconds: weeks, days, hours, and minutes all
have a unique conversion to seconds, and years and larger units all
have a unique conversion to months, but since months are variable
length the two domains cannot be unified.

So, the fundamental problem here is that the seconds field of a timespan
is int4, and therefore it overflows for timespans exceeding +/- 68 years
--- but only if the timespan is specified using smaller-than-month
units.

A reasonable solution would be to change the seconds field to float8,
which would give it range comparable to datetime itself (as well as the
ability to represent sub-second intervals).

I'm about half tempted to propose changing the months field from int4 to
float8 as well, but there probably would be no real gain from doing so.
OTOH, depending on your platform an int4+float8 structure may occupy 16
bytes anyway...

			regards, tom lane



view thread (5+ messages)

Message-ID: <8094.946911915@sss.pgh.pa.us>
Permalink:  ../8094.946911915@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/8094.946911915@sss.pgh.pa.us

 · 

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-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, lockhart@alumni.caltech.edu, zsolt@tek.bke.hu, pgsql-sql@hub.org
  Subject: Re: [SQL] timespan problem
  In-Reply-To: <8094.946911915@sss.pgh.pa.us>
  References: <Pine.LNX.4.10.10001012142220.17313-100000@tek.bke.hu> <20530.946771110@sss.pgh.pa.us> <38705200.DFFB9931@alumni.caltech.edu> <8094.946911915@sss.pgh.pa.us>

* 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