Forum OpenACS Q&A: PostgreSQL date questions

Collapse
Posted by Carl Coryell-Martin on
Is there an easy way to select an interval from postgres in seconds? I would like to do:
select idle_seconds as (sysdate()-idle_since_date) from foo...
and have it return idle_seconds in seconds....

Any thoughts?

cheers,

Collapse
Posted by Don Baccus on
select date_part('epoch', sysdate()) - date_part('epoch', idle_since_date) as idle_seconds from foo;

date_part('epoch', ...) returns the number of seconds from the beginning of the (Unix) epoch, i.e. 1970.

Collapse
Posted by Radam Batnag on

Here's a function that computes the interval, in number of seconds, between two timestamps.

CREATE FUNCTION timebox_get_interval(timestamp, timestamp)
RETURNS integer
AS '
  DECLARE 
    trans_start ALIAS FOR $1;
    trans_end ALIAS FOR $2;
    time_diff interval;
    time_diff_text text;
    sec_str text;
    min_str text;
    hr_str  text;
    sec_interval integer;
  BEGIN
    time_diff := trans_end - trans_start;
    time_diff_text := (time_diff)::text;
    sec_str := substr(time_diff_text, 7, 2);
    min_str := substr(time_diff_text, 4, 2);
    hr_str := substr(time_diff_text, 1, 2);
    sec_interval := sec_str::integer + ((min_str::integer)*60) + ((hr_str::integer)*3600); 
    RETURN sec_interval;
  END;'
LANGUAGE 'plpgsql';

Collapse
Posted by Bart Teeuwisse on
What would be the Oracle equivalent to calculate the interval between two time stamps in seconds? Per Don's suggestion I'm using the following in Postgresql:

(date_part('epoch', now()) - date_part('epoch', in_cart_date))
Collapse
Posted by Michael A. Cleverly on
Oracle does date arithmetic in units of "days".  So subtract one date from the other and then multiply by the number of seconds in a day (86400) to get the number of seconds in the interval in question.