Merging timeseries in postgres

From: Tim Smith <randomdev4+postgres(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Merging timeseries in postgres
Date: 2016-07-14 11:56:19
Message-ID: CA+HuS5E8bSoRxQYeuHyE2uacY87m8Yg1RaDx6ZjLnKiGLMZpQg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I've got a bit of query-writers block ! I've tried various join styles
but can't get it to do what I want to achieve.

Assume I have a bunch of time-series tables :

create table test(dx date,n numeric);
create table test1(dx1 date,nx1 numeric);
insert into test values('2000-01-01','0.001');
insert into test1 values('2002-01-02','0.002');
insert into test1 values('2003-01-03','0.002');

What I want to do is create a view that merges these together with
time as the index, i.e the output would look like :

2000-01-01 0.001 (null)
2002-01-02 (null) 0.002
2003-01-03 (null) 0.003

I can't quite figure out how to keep the index independent and make a
clean join, typical outer join constructs end up with results like :

dx | nx | dx1 | nx1
----+----+------------+-------
| | 2002-01-02 | 0.001
| | 2003-01-02 | 0.002
(2 rows)

dx | nx | dx1 | nx1
------------+-------+------------+-------
2000-01-02 | 0.005 | |
| | 2002-01-02 | 0.001
| | 2003-01-02 | 0.002

Which isn't very pretty and doesn't really achieve what I want.

As I said "sql-writers block !" ;-(

Tim

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2016-07-14 12:11:26 Re: Merging timeseries in postgres
Previous Message AMatveev 2016-07-14 10:41:06 Re: Memory usage per session