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
test
Movie Rating SQL Analysis Project
Sample tables
Correlated subquery in SELECT clause, and rewritten for Netezza
Food Delivery Website SQL Analysis
Assignment 1(SET A)
Returning IDs with exactly 3 consecutive months of invoice_amt >2000
postgresql: working days
Postgres Practice
1127. User Purchase Platform