Forum OpenACS Q&A: PostgreSQL date questions

Collapse
1: PostgreSQL date questions
Posted by Carl Coryell-Martin on 11/28/00 01:05 AM
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
2: Response to PostgreSQL date questions (response to 1)
Posted by Don Baccus on 11/28/00 01:34 AM
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
3: Response to PostgreSQL date questions (response to 1)
Posted by Radam Batnag on 11/28/00 07:25 AM

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
4: Response to PostgreSQL date questions (response to 1)
Posted by Bart Teeuwisse on 02/15/02 06:03 AM
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
5: Response to PostgreSQL date questions (response to 1)
Posted by Michael A. Cleverly on 02/15/02 06:27 AM
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.