How to get date_diff from previous rows in Presto?

Question

I'm trying to get a diff_date from Presto from this data.

timespent | 2016-04-09T00:09:07.232Z | 1000          | general
timespent | 2016-04-09T00:09:17.217Z | 10000         | general
timespent | 2016-04-09T00:13:27.123Z | 250000        | general
timespent | 2016-04-09T00:44:21.166Z | 1144020654000 | general

This is my query

select _t, date_diff('second', from_iso8601_timestamp(_ts), SELECT from_iso8601_timestamp(f._ts) from logs f 
              where f._t = 'timespent'
               and f.dt = '2016-04-09'
               and f.uid = 'd2de01a1-8f78-49ce-a065-276c0c24661b'
               order by _ts)
from logs d
where _t = 'timespent'
and dt = '2016-04-09'
and uid = 'd2de01a1-8f78-49ce-a065-276c0c24661b'
order by _ts;

This is the error I get

Query 20160411_150853_00318_fmb4r failed: line 1:61: no viable alternative at input 'SELECT'

score 15 · Accepted Answer · edited Nov 08 '19 at 21:55

15

I think you want lag():

select _t,
       date_diff('second', from_iso8601_timestamp(_ts),
                 lag(from_iso8601_timestamp(f._ts)) over (partition by uid order by dt)
                )
from logs d
where _t = 'timespent' and dt = '2016-04-09' and
      uid = 'd2de01a1-8f78-49ce-a065-276c0c24661b'
order by _ts;

edited Nov 08 '19 at 21:55

Piotr Findeisen

19,480
2
52
82

answered Apr 11 '16 at 15:29

Gordon Linoff

1,242,037
58
646
786

score 4 · Answer 2 · edited Feb 17 '17 at 08:09

4

select date_diff('Day',from_iso8601_date(substr(od.order_date,1,10)),CURRENT_DATE) AS "diff_Days"
from order od;

edited Feb 17 '17 at 08:09

FelixSFD

6,052
10
43
117

answered Feb 17 '17 at 07:49

Rajkumar Kadari

63
5

score 1 · Answer 3 · edited May 23 '18 at 20:38

1

select date_diff('minute',timestamp '2010-05-22 16:00:00+0000', timestamp '2018-05-22 16:05:00+0000');

edited May 23 '18 at 20:38

geisterfurz007

5,292
5
33
54

answered May 23 '18 at 20:37

Monisha

11
1

How to get date_diff from previous rows in Presto?

3 Answers3