Postgres Pro
Downloads
Home   >   mailing lists

Thread: How cast interval to minutes as number?

How cast interval to minutes as number?

From
Andreas
Date:
Hi,

I have a column with timestamps  --> some_ts  .
I'd like to calculate the amount of time between this timestamp and NOW().

select   some_ts - NOW()   from some_table;

This results in a column of type  interval.

How would I get this interval converted into e.g. minutes.
I need the whole value converted not just the 0-59 minutes part.
Like  a difference of exactly 1 day = 24*60 minutes rather than 0
minutes if I just take the minute-part.

Seconds would be fine, too.


select EXTRACT (EPOCH FROM INTERVAL some_ts - NOW()) from ....

gets me a syntax error

Help?

Regards
Andreas

Re: How cast interval to minutes as number?

From
Maximilian Tyrtania
Date:
Hi Andreas,

Am 25.11.2007 8:43 Uhr schrieb "Andreas" unter <maps.on@gmx.net>:

> Hi,
>
> I have a column with timestamps  --> some_ts  .
> I'd like to calculate the amount of time between this timestamp and NOW().
>
> select   some_ts - NOW()   from some_table;
>
> This results in a column of type  interval.
>
> How would I get this interval converted into e.g. minutes.
> I need the whole value converted not just the 0-59 minutes part.
> Like  a difference of exactly 1 day = 24*60 minutes rather than 0
> minutes if I just take the minute-part.
>
> Seconds would be fine, too.
>
>
> select EXTRACT (EPOCH FROM INTERVAL some_ts - NOW()) from ....
>
> gets me a syntax error
>
> Help?

I think the "age" function might do what you are looking for.

http://www.postgresql.org/docs/8.2/interactive/functions-datetime.html

Best wishes,

- Maximilian Tyrtania | mty@fischerappelt.de
  fischerAppelt Kommunikation GmbH
  Tucholskystr.18 | D-10117 Berlin | Germany
  Tel. ++49-30-726146-728
  http://www.fischerappelt.de

--

Re: How cast interval to minutes as number?

From
Andreas Kretschmer
Date:
Andreas <maps.on@gmx.net> schrieb:

> Hi,
>
> I have a column with timestamps  --> some_ts  .
> I'd like to calculate the amount of time between this timestamp and NOW().
>
> select   some_ts - NOW()   from some_table;
>
> This results in a column of type  interval.
>  How would I get this interval converted into e.g. minutes.

Okay, lets assume i want to know how old is my daughter, in minutes:

test=# select EXTRACT (EPOCH FROM  now() - '2007-07-28 09:15:00'::timestamp )::int/60;
 ?column?
----------
   172896
(1 row)

You can exchange the fix timestamp with a column from a table, no
problem.


Hope that helps, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Re: How cast interval to minutes as number?

From
Andreas
Date:
Maximilian Tyrtania schrieb:
> Am 25.11.2007 8:43 Uhr schrieb "Andreas" unter <maps.on@gmx.net>:
>
>> I have a column with timestamps  --> some_ts  .
>> I'd like to calculate the amount of time between this timestamp and NOW().
>>
>> select   some_ts - NOW()   from some_table;
>>
>> This results in a column of type  interval.
>>
>> How would I get this interval converted into e.g. minutes.
>> I need the whole value converted not just the 0-59 minutes part.
>> Like  a difference of exactly 1 day = 24*60 minutes rather than 0
>> minutes if I just take the minute-part.
>>
>> Seconds would be fine, too.
>>
> I think the "age" function might do what you are looking for.
>
> http://www.postgresql.org/docs/8.2/interactive/functions-datetime.html
>

Not really.
The doc says age results to months and days.
I need minutes for intraday calculations.

There must be some more exact way to store time differences.
As far as I understood intervals are abstracts but no fix representation
of a distinct timespan, like INTERVAL '1 month' could equally be
February or August, even though August has more days.

It's still nice,too, to be able to add 2 months to a date and get the
same day 2 months later without considering the actual number of days
between the two dates.

Re: How cast interval to minutes as number?

From
Andreas
Date:
Andreas Kretschmer schrieb:
> Andreas <maps.on@gmx.net> schrieb:
>
>> This results in a column of type  interval.
>>  How would I get this interval converted into e.g. minutes.
>>
>
> Okay, lets assume i want to know how old is my daughter, in minutes:
> test=# select EXTRACT (EPOCH FROM  now() - '2007-07-28 09:15:00'::timestamp )::int/60;
>
Thanks and congratulations   ;-)

Andreas