Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
How i can get the difference of closest two date in two table join in postgresql
with table1(activity_timestamp, activity) as ( values ('2016-12-23 13:53:47.608561'::timestamp, 'details viewed'), ('2017-01-09 14:15:52.570397', 'details viewed'), ('2016-12-27 16:06:39.138994', 'details viewed'), ('2016-12-24 21:09:56.159436', 'details viewed')), table2(activity_timestamp, activity) as ( values ('2016-12-23 13:54:47.608561'::timestamp, 'reading'), ('2017-01-09 14:17:52.570397', 'reading'), ('2016-12-27 16:10:39.138994', 'reading'), ('2016-12-24 21:012:56.159436', 'reading')), result as ( select *, activity_timestamp - (select max(activity_timestamp) from table1 as t1 where t2.activity_timestamp > t1.activity_timestamp) as diff from table2 as t2 order by activity_timestamp, activity) select *, extract('epoch' from diff) as seconds, extract('epoch' from diff) / 60 as minutes from result;
run
|
edit
|
history
|
help
0
Q2
SQL - Unpivot Table
1
PostreSQL: Subquery
PosgresSQL Sandbox: Legitimate Resale
w3c sql data
SQL social network practice by Han Wang 20200720 - 2
Weekly Average Starting on Different Days
tegs and users
xD