pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Cedar Cox <cedarc@visionforisrael.com>
Cc: pgsql-sql@postgresql.org, Thomas Lockhart <lockhart@alumni.caltech.edu>
Subject: Re: timestamp bug
Date: Sun, 20 May 2001 13:30:44 -0400
Message-ID: <12137.990379844@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.LNX.4.21.0105201225150.17216-100000@nanu.visionforisrael.com>
References: <Pine.LNX.4.21.0105201225150.17216-100000@nanu.visionforisrael.com>

Cedar Cox <cedarc@visionforisrael.com> writes:
> devbarn71=# SELECT to_char(cast('2001 sep 5' as timestamp)+cast('3 days' as interval),'YYYY Mon DD');
>    to_char
> -------------
>  2001 Sep 07
> (1 row)

Here in the US, the "funny" dates are in April and October:

regression=# SELECT cast('2001 oct 27' as timestamp)+cast('1 days' as interval);
        ?column?
------------------------
 2001-10-28 00:00:00-04
(1 row)

regression=# SELECT cast('2001 oct 27' as timestamp)+cast('2 days' as interval);
        ?column?
------------------------
 2001-10-28 23:00:00-05
(1 row)


Perhaps if you want only date arithmetic, not correct-to-the-second
arithmetic, you should use type date:

regression=# SELECT cast('2001 oct 27' as date) + 1;
  ?column?
------------
 2001-10-28
(1 row)

regression=# SELECT cast('2001 oct 27' as date) + 2;
  ?column?
------------
 2001-10-29
(1 row)


However, this does bring up something I've thought about before.
Type interval consists internally of two fields, months and seconds.
Intervals specified in months, years, centuries, etc convert to so many
months; everything in smaller-than-month units is converted to seconds.
Then we can do timestamp arithmetic that copes with the fact that
there's not a fixed equivalence between months and smaller units.
But, when you think about DST jumps, it's obvious that this doesn't go
far enough.  There's not a fixed equivalence between days and smaller
units either.

ISTM that
	'2001-10-27 00:00:00-04'::timestamp + '2 days'::interval
should yield
	'2001-10-29 00:00:00-05'::timestamp
whereas
	'2001-10-27 00:00:00-04'::timestamp + '48 hours'::interval
should yield
	'2001-10-28 23:00:00-05'::timestamp

We cannot make that distinction now, but we could if type interval
contained three fields internally: months, days, and seconds.

			regards, tom lane



view thread (5+ messages)  latest in thread

Message-ID: <12137.990379844@sss.pgh.pa.us>
Permalink:  ../12137.990379844@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/12137.990379844@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, cedarc@visionforisrael.com, lockhart@alumni.caltech.edu
  Subject: Re: timestamp bug
  In-Reply-To: <12137.990379844@sss.pgh.pa.us>
  References: <Pine.LNX.4.21.0105201225150.17216-100000@nanu.visionforisrael.com> <12137.990379844@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