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 ...
Language:
Ada
Assembly
Bash
C#
C++ (gcc)
C++ (clang)
C++ (vc++)
C (gcc)
C (clang)
C (vc)
Client Side
Clojure
Common Lisp
D
Elixir
Erlang
F#
Fortran
Go
Haskell
Java
Javascript
Kotlin
Lua
MySql
Node.js
Ocaml
Octave
Objective-C
Oracle
Pascal
Perl
Php
PostgreSQL
Prolog
Python
Python 3
R
Rust
Ruby
Scala
Scheme
Sql Server
Swift
Tcl
Visual Basic
Layout:
Vertical
Horizontal
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;
absolute service time: 0,77 sec
edit mode
|
history
|
discussion
activity_timestamp
activity
diff
seconds
minutes
1
23.12.2016 13:54:47
reading
00:01:00
60
1
2
24.12.2016 21:12:56
reading
00:03:00
180
3
3
27.12.2016 16:10:39
reading
00:04:00
240
4
4
09.01.2017 14:17:52
reading
00:02:00
120
2