Postgres Pro
Downloads
Home   >   mailing lists

Thread: timespan

timespan

From
Bob Dusek
Date:
Hey there,

I've got a more simplified version of a previous question I asked:

does anyone know how to take a value of type timespan and convert it
into a single float8 or int value?

I'm really having a time with this and I haven't been able to come up
with a solution, thus far.  Some experienced assistance would be very
appreciated.

Thank you much,

Bob

Re: [GENERAL] timespan

From
Ulf Mehlig
Date:
Bob Dusek <bobd@palaver.net> wrote:

> does anyone know how to take a value of type timespan and convert it
> into a single float8 or int value?

I don't know whether this will help:

  => select date_part ('epoch', age('29.02.2000'::date,
                                    '29.02.1968'::date)) as days;

will give you the seconds elapsed over the timespan given by
age(). Used with an absolute date, it will give you the number of
seconds since the Unix epoch (negative for dates before 01.01.1970,
positive after). You can divide the resulting number by others, so

  => select date_part ('epoch', age('29.02.2000'::date,
                                    '29.02.1968'::date)) /
            (60*60*24)
            as days;

  days
 -----
 11688
 (1 row)

gives you the number of days in the interval. If you use
datetime-values, a float value can be obtained:

  => select date_part ('epoch', age('29.02.2000'::date,
                                    datetime('29.02.1968','11:33'))) /
            (60*60*24)
             as days;

         days
  -----------
  11681.26875
  (1 row)


In the case of timespans, 'epoch' as date_part() specifier might be
misleading ... Couldn't it be aliased to something like
'elapsed_secs'?

Bye,
Ulf

--
======================================================================
 %%%%%            Ulf Mehlig              <ulf.mehlig@uni-bremen.de>
   %%%%!%%%       Projekt "MADAM"         <umehlig@uni-bremen.de>
%%%% %!% %%%%     ----------------------------------------------------
 ---| %%%         MADAM:  MAngrove    |  Center for Tropical Marine
    ||--%!%              Dynamics     |  Biology
    ||                  And           |  Fahrenheitstrasse 1
 _ /||\_/\_            Management     |
/  /    \  \ ~~~~~~~~~~~~~~~~~        |  28359 Bremen/Germany
  ~~~~~~~~~~~~~~~~~~~~

Re: [GENERAL] timespan

From
A James Lewis
Date:
I'm by no means experienced.... but I'm using this to convert epoch time
to a date field... (INT4 is BAD, but 6.3.2 doesn't have int8 yet....)

datetime_pl_span('1970-01-01 0:0:0'::datetime, text_timespan(int4_text(exp_date)))

This, converts time to epoch,

select date_part('epoch', now()::datetime);

SO!

HOW ABOUT,

date_part('epoch', datetime_pl_span('1970-01-01 0:0:0'::datetime, your_value::timespan));

Way I see it, this returns span time in seconds, so long as the span is
less than 68 years?

Anyone got a better solution?

On Thu, 5 Nov 1998, Bob Dusek wrote:

> Hey there,
>
> I've got a more simplified version of a previous question I asked:
>
> does anyone know how to take a value of type timespan and convert it
> into a single float8 or int value?
>
> I'm really having a time with this and I haven't been able to come up
> with a solution, thus far.  Some experienced assistance would be very
> appreciated.
>
> Thank you much,
>
> Bob
>

James (james@linuxrocks.co.uk)
Vortex Internet
My Windows unders~1 long filena~1, and yours?

Re: [GENERAL] timespan

From
Ulf Mehlig
Date:
Something interesting I overlooked in my own posting:

One query gives 11688 days, the other 11681.26875 ...

I didn't calculate by hand, but emacs' calendar gives 11688 days,
too.

A simpler example:

 => select date_part ('epoch', age(datetime('29.02.2000','00:00'),
                                   datetime('29.02.1968','00:00')));
  date_part
 ----------
 1009843200
     ^

 =>  select date_part ('epoch', age(datetime('29.02.2000','00:00'),
                                    datetime('29.02.1968','00:01')));
  date_part
 ----------
 1009303140
     ^

Ulf

--
======================================================================
 %%%%%            Ulf Mehlig              <ulf.mehlig@uni-bremen.de>
   %%%%!%%%       Projekt "MADAM"         <umehlig@uni-bremen.de>
%%%% %!% %%%%     ----------------------------------------------------
 ---| %%%         MADAM:  MAngrove    |  Center for Tropical Marine
    ||--%!%              Dynamics     |  Biology
    ||                  And           |  Fahrenheitstrasse 1
 _ /||\_/\_            Management     |
/  /    \  \ ~~~~~~~~~~~~~~~~~        |  28359 Bremen/Germany
  ~~~~~~~~~~~~~~~~~~~~

Re: [GENERAL] timespan

From
Bob Dusek
Date:
Here's to Ulf and James,

Thanks!  (a lot)

Your time and help is very, well... helpful.

Bob


A James Lewis wrote:
>
> I'm by no means experienced.... but I'm using this to convert epoch time
> to a date field... (INT4 is BAD, but 6.3.2 doesn't have int8 yet....)
>
> datetime_pl_span('1970-01-01 0:0:0'::datetime, text_timespan(int4_text(exp_date)))
>
> This, converts time to epoch,
>
> select date_part('epoch', now()::datetime);
>
> SO!
>
> HOW ABOUT,
>
> date_part('epoch', datetime_pl_span('1970-01-01 0:0:0'::datetime, your_value::timespan));
>
> Way I see it, this returns span time in seconds, so long as the span is
> less than 68 years?
>
> Anyone got a better solution?
>
> On Thu, 5 Nov 1998, Bob Dusek wrote:
>
> > Hey there,
> >
> > I've got a more simplified version of a previous question I asked:
> >
> > does anyone know how to take a value of type timespan and convert it
> > into a single float8 or int value?
> >
> > I'm really having a time with this and I haven't been able to come up
> > with a solution, thus far.  Some experienced assistance would be very
> > appreciated.
> >
> > Thank you much,
> >
> > Bob
> >
>
> James (james@linuxrocks.co.uk)
> Vortex Internet
> My Windows unders~1 long filena~1, and yours?