Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
extract_pairs
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
--PostgreSQL 9.6 --'\\' is a delimiter with t as ( select v.* from (values (1, '10:22'::time, 1204), (1, '10:55'::time, 1100), (1, '10:56'::time, 1130), (1, '11:44'::time, 1000), (2, '10:18'::time, 1100), (2, '11:19'::time, 1105), (2, '11:21'::time, 1109) ) v(id, timestamp, value) ) select v1.id as id, date_trunc('hour', v1.timestamp) as hour, v1.value as start_value, v2.value as stop_value from t v1 join t v2 on v1.id = v2.id and date_trunc('hour', v1.timestamp) = date_trunc('hour', v2.timestamp) and v1.timestamp < v2.timestamp
absolute service time: 0,42 sec
edit mode
|
history
|
discussion
id
hour
start_value
stop_value
1
1
10:00:00
1204
1130
2
1
10:00:00
1204
1100
3
1
10:00:00
1100
1130
4
2
11:00:00
1105
1109