pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Casting dates
3+ messages / 2 participants
[nested] [flat]

* Casting dates
@ 2002-04-29 00:56 Joseph Barillari <jbarilla@princeton.edu>
  2002-04-29 16:02 ` Re: Casting dates Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Joseph Barillari @ 2002-04-29 00:56 UTC (permalink / raw)
  To: pgsql-sql

Hi,

Does anyone know if it's possible to calculate the number of days (or
weeks, seconds, minutes, or what-have-you) in a given interval without
casting to EPOCH and performing manipulations there?

PostgreSQL usually returns intervals created by subtraction in days
and smaller fragments, I would like to know if it's possible for it to
return years. (Integer division of the number of days by 365 would
produce an almost-correct result, but it's rather inelegant.)

Subtracting two timestamps gives a day count:

cal=> select timestamp 'today' - timestamp 'may 1, 2000';
 ?column? 
----------
 727 days
(1 row)

Subtracting two days-only intervals gives the expected result:

cal=> select interval '6000 days' - interval '100 days';
 ?column?  
-----------
 5900 days
(1 row)

But it doesn't work for all like-unit intervals: some are reported
using other quantities:

cal=> select interval '6000 minutes' - interval '100 minutes';
   ?column?   
--------------
 4 days 02:20  <--- not the expected '5900 minutes'
(1 row)

And most notably, it doesn't work for years, where it would be most useful:

cal=> select  timestamp 'jan 24, 1998 00:00' - timestamp 'jan 24, 1990 00:00';
 ?column?  
-----------
 2922 days
(1 row)

cal=> select extract (years from timestamp 'jan 24, 1998 00:00' - timestamp 'jan 24, 1990 00:00');
 date_part 
-----------
         0
(1 row)

My aim is to make it easier to write a function that manipulates years
(the code fragment in question takes two timestamps, A and B, and an
integer N, and subtracts A from B to see if they differ by a multiple
of N years. If not, then it adds years to B to ensure that A and B
differ by a multiple of N).

Any ideas would be appreciated.

Cordially,

Joe Barillari

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Casting dates
  2002-04-29 00:56 Casting dates Joseph Barillari <jbarilla@princeton.edu>
@ 2002-04-29 16:02 ` Josh Berkus <josh@agliodbs.com>
  2002-04-29 16:19   ` Re: Casting dates Joseph Barillari <jbarilla@princeton.edu>
  0 siblings, 1 reply; 3+ messages in thread

From: Josh Berkus @ 2002-04-29 16:02 UTC (permalink / raw)
  To: Joseph Barillari <jbarilla@princeton.edu>; pgsql-sql

Joe,

> Does anyone know if it's possible to calculate the number of days (or
> weeks, seconds, minutes, or what-have-you) in a given interval
> without
> casting to EPOCH and performing manipulations there?
> 
> PostgreSQL usually returns intervals created by subtraction in days
> and smaller fragments, I would like to know if it's possible for it
> to
> return years. (Integer division of the number of days by 365 would
> produce an almost-correct result, but it's rather inelegant.)

Unfortunately, you've hit one of Postgres implementation limits.
   Multiplying and dividing INTERVAL values, while covered by the SQL92
spec, has not been completely implemented in Postgres.   That is, it
should be possible for you to:

SELECT  INTERVAL '3 days 5 hours' / INTERVAL '1 minute'

However, implementing this has been a challenge for all SQL-DB
designers.  I don't know of any DB that does handle INTERVAL division.
 

It's a surmountable challenge, though, but requires somebody to take
the lead in a) working out the logic, and b) writing the code to
implement it.  A) is not a trivial task, either ... while 60 minutes /
1 second is obvious, how about 4 months / 4 days?  Months are not
constant in length.

I've had some ideas for a spec for this myself (expanding on the rather
terse spec in SQL 92) but have not had time to write it up.  Good luck.

-Josh Berkus



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Casting dates
  2002-04-29 00:56 Casting dates Joseph Barillari <jbarilla@princeton.edu>
  2002-04-29 16:02 ` Re: Casting dates Josh Berkus <josh@agliodbs.com>
@ 2002-04-29 16:19   ` Joseph Barillari <jbarilla@princeton.edu>
  0 siblings, 0 replies; 3+ messages in thread

From: Joseph Barillari @ 2002-04-29 16:19 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

>>>>> "JB" == Josh Berkus <josh@agliodbs.com> writes:

    JB> Joe,
    >> Does anyone know if it's possible to calculate the number of
    >> days (or weeks, seconds, minutes, or what-have-you) in a given
    >> interval without casting to EPOCH and performing manipulations
    >> there?
    >> 
    >> PostgreSQL usually returns intervals created by subtraction in
    >> days and smaller fragments, I would like to know if it's
    >> possible for it to return years. (Integer division of the
    >> number of days by 365 would produce an almost-correct result,
    >> but it's rather inelegant.)

    JB> Unfortunately, you've hit one of Postgres implementation
    JB> limits.  Multiplying and dividing INTERVAL values, while
    JB> covered by the SQL92 spec, has not been completely implemented
    JB> in Postgres.  That is, it should be possible for you to:

    JB> SELECT INTERVAL '3 days 5 hours' / INTERVAL '1 minute'

    JB> However, implementing this has been a challenge for all SQL-DB
    JB> designers.  I don't know of any DB that does handle INTERVAL
    JB> division.
 

    JB> It's a surmountable challenge, though, but requires somebody
    JB> to take the lead in a) working out the logic, and b) writing
    JB> the code to implement it.  A) is not a trivial task, either
    JB> ... while 60 minutes / 1 second is obvious, how about 4 months
    JB> / 4 days?  Months are not constant in length.

For the purposes of this application (because it deals in relatively
short intervals, it's a calendaring application), I just divide the
day count by 365 and throw away the remainder.

For example, 

select date_part('day', (timestamp 'jan 1, 1996' -
                        timestamp 'jan 1, 1992')); 

is 1461 days, or 1+365*4 (1992 is a leap year). Integer division of
1461/365 produces the correct year count. This doesn't work for longer
intervals:

cal=> select date_part('day', (timestamp 'jan 1, 2600' 
              - timestamp 'jan 1, 1000'))/365;
     ?column?     
------------------
 1601.06301369863  <-- an error of +1 year, due to accumulated leap days

Obviously, this naive method won't work for astronomical time
intervals, but for my purposes, few people have calendars that
stretch across millennia.

--Joe

^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2002-04-29 16:19 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-04-29 00:56 Casting dates Joseph Barillari <jbarilla@princeton.edu>
2002-04-29 16:02 ` Josh Berkus <josh@agliodbs.com>
2002-04-29 16:19   ` Joseph Barillari <jbarilla@princeton.edu>

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