CODEWITHSUNDEEP
sqlpostgresql
ditto
ditto

Reputation: 6307

Return day difference between current row timestamp and the 1st returned row

For each row returned, I want to compare it's timestamp column to the very 1st row in the SELECT, and get the date difference in days. How may I do this?

SELECT date
FROM table
ORDER BY DATE ASC

Desired output

id | date                | day difference
0  | 2015-05-02 00:00:00 | day 1
1  | 2015-05-05 00:00:00 | day 3
2  | 2015-05-22 00:00:00 | day 20

Upvotes: 0

Views: 37

Answers (2)

Fabricator
Fabricator

Reputation: 12782

This may be a bit late, but you could use the first_value window function. See http://www.postgresql.org/docs/9.1/static/functions-window.html

select id, date, date - first_value(date) over ()
from Table1;

fiddle

Upvotes: 0

AdamKG
AdamKG

Reputation: 14091

Courtesy of #postgresql on freenode:

% dropdb testdb; createdb testdb; psql -X testdb
psql (9.4.4)
Type "help" for help.

testdb=# create table t as select 0 id, '2015-05-02'::timestamp date;
SELECT 1
testdb=# insert into t select 1, '2015-05-05';
INSERT 0 1
testdb=# insert into t select 2, '2015-05-22';
INSERT 0 1
testdb=# select id, date, date - min(date) over () from t;
 id |        date         | ?column? 
----+---------------------+----------
  0 | 2015-05-02 00:00:00 | 00:00:00
  1 | 2015-05-05 00:00:00 | 3 days
  2 | 2015-05-22 00:00:00 | 20 days
(3 rows)

Upvotes: 1

Related Questions