Forum OpenACS Q&A: PostgreSQL date questions

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?


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.

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 '
    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;
    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;
LANGUAGE 'plpgsql';

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))
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.