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'
You can convert timestamp to date with cast(col as date) or date(col) .
All Aggregate Functions can be used as window functions by adding the OVER clause. The aggregate function is computed for each row over the rows within the current row's window frame.
Presto is an open source, distributed SQL query engine designed for fast, interactive queries on data in HDFS, and others. Unlike Hadoop/HDFS, it does not have its own storage system. Thus, Presto is complimentary to Hadoop, with organizations adopting both to solve a broader business challenge.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With