CODEWITHSUNDEEP
postgresql
Julie
Julie

Reputation: 101

Extracting the number of days from a calculated interval

I am trying to get a query like the following one to work:

SELECT EXTRACT(DAY FROM INTERVAL to_date - from_date) FROM histories;

In the referenced table, to_date and from_date are of type timestamp without time zone. A regular query like 

SELECT to_date - from_date FROM histories;

Gives me interval results such as '65 days 04:58:09.99'. But using this expression inside the first query gives me an error: invalid input syntax for type interval. I've tried various quotations and even nesting the query without luck. Can this be done?

Upvotes: 7

Views: 16575

Answers (3)

Antoine L
Antoine L

Reputation: 507

Just remove the keyword INTERVAL:

SELECT EXTRACT(DAY FROM to_date - from_date) FROM histories;

Upvotes: 2

Yordan Georgiev
Yordan Georgiev

Reputation: 5420

This example demontrates the creation of a table with trigger which updates the difference between a stop_time and start_time in DDD HH24:MI:SS format where the DDD stands for the amount of dates ...

DROP TABLE IF EXISTS benchmarks ;

  SELECT 'create the "benchmarks" table'
  ;
     CREATE TABLE benchmarks (
        guid           UUID NOT NULL DEFAULT gen_random_uuid()
      , id             bigint UNIQUE NOT NULL DEFAULT cast (to_char(current_timestamp, 'YYMMDDHH12MISS') as bigint)
      , git_hash                char (8) NULL DEFAULT 'hash...'
      , start_time              timestamp NOT NULL DEFAULT DATE_TRUNC('second', NOW())
      , stop_time               timestamp NOT NULL DEFAULT DATE_TRUNC('second', NOW())
      , diff_time               varchar (20) NOT NULL DEFAULT 'HH:MI:SS'
      , update_time             timestamp DEFAULT DATE_TRUNC('second', NOW())
      , CONSTRAINT pk_benchmarks_guid PRIMARY KEY (guid)
      ) WITH (
        OIDS=FALSE
      );

  create unique index idx_uniq_benchmarks_id on benchmarks (id);

-- START trigger trg_benchmarks_upsrt_diff_time
-- hrt = human readable time
CREATE OR REPLACE FUNCTION fnc_benchmarks_upsrt_diff_time()

    RETURNS TRIGGER
    AS $$
       BEGIN
          -- NEW.diff_time = age(NEW.stop_time::timestamp-NEW.start_time::timestamp);
          NEW.diff_time = to_char(NEW.stop_time-NEW.start_time, 'DDD HH24:MI:SS');
          RETURN NEW;
       END;
    $$ LANGUAGE plpgsql;


 CREATE TRIGGER trg_benchmarks_upsrt_diff_time
  BEFORE INSERT OR UPDATE ON benchmarks
  FOR EACH ROW EXECUTE PROCEDURE fnc_benchmarks_upsrt_diff_time();
--
-- STOP trigger trg_benchmarks_upsrt_diff_time

Upvotes: 0

Craig Ringer
Craig Ringer

Reputation: 324275

SELECT EXTRACT(DAY FROM INTERVAL to_date - from_date) FROM histories;

This makes no sense. INTERVAL xxx is syntax for interval literals. So INTERVAL from_date is a syntax error, since from_date isn't a literal. If your code really looks more like INTERVAL '2012-02-01' then that's going to fail, because 2012-02-01 is not valid syntax for an INTERVAL.

The INTERVAL keyword here is just noise. I suspect you misunderstood an example from the documentation. Remove it and the expression will be fine.

I'm guessing you're trying to get the number of days between two dates represented as timestamp or timestamptz.

If so, either cast both to date:

SELECT to_date::date - from_date::date FROM histories;

or get the interval, then extract the day component:

SELECT extract(day from to_date - from_date) FROM histories;

Upvotes: 10

Related Questions