Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
extract_pairs
--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
run
|
edit
|
history
|
help
0
SQL primes, sqrt sieve
book suggestion
Webiste SQL Analysis
SQL3
menu sample
sss
Recursive inefficiency
postgresql timestamp
finaleHFEIZFIDSHI
nested array to table