Merging timeseries in postgres

On Thu, Jul 14, 2016 at 7:56 AM, Tim Smith <randomdev4+postgres@gmail.com>
wrote:

2003-01-03 (null) 0.003

​Where​ did the "0.003" come from?

The result you are looking for works if you full outer join on dx1 - at
least for the sample data.

David J.

Whats exactly is wrong with the following query?

select
dx date,
nx,
nx1
from
test t
join test1 t1 on t.dx=t1.dx1
;

----- Original Message -----
From: "Tim Smith" <randomdev4+postgres@gmail.com>
To: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Thursday, July 14, 2016 2:56:19 PM
Subject: [GENERAL] Merging timeseries in postgres

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Nevermind, I misunderstood your question.

The answer is an outer join and if you want the exact output you provided then you can use the following clause.

coalesce(dx, dx1) as date

Is there any reason why these are two different tables? I'd consider changing data structure.

----- Original Message -----
From: "Tim Smith" <randomdev4+postgres@gmail.com>
To: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Thursday, July 14, 2016 2:56:19 PM
Subject: [GENERAL] Merging timeseries in postgres

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Jul 14, 2016 at 8:18 AM, Nick Babadzhanian <nb@cobra.ru> wrote:

Whats exactly is wrong with the following query?

select
dx date,
nx,
nx1
from
test t
join test1 t1 on t.dx=t1.dx1
;

​Please don't top-post.

test t join test1 t1 -- this is the default inner join, your query returns
no records for the given data.

David J.
​

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of David G. Johnston
Sent: Thursday, 14 July, 2016 08:23
To: Nick Babadzhanian
Cc: Tim Smith; pgsql-general
Subject: Re: [GENERAL] Merging timeseries in postgres

On Thu, Jul 14, 2016 at 8:18 AM, Nick Babadzhanian <nb@cobra.ru<mailto:nb@cobra.ru>> wrote:
Whats exactly is wrong with the following query?

select
dx date,
nx,
nx1
from
test t
join test1 t1 on t.dx=t1.dx1
;

​Please don't top-post.

test t join test1 t1 -- this is the default inner join, your query returns no records for the given data.

David J.
​
Look at the above documentation (7.2.1.1) on full join
https://www.postgresql.org/docs/9.3/static/queries-table-expressions.html

select coalesce(dx,dx1)as dt, n, nx1
from test full join test1 on dx=dx1;

Daniel

Thanks for the replies guys. In the end I found an example lurking in the
depths of the Internet that used a CTE and a join to that which seemed to
have the desired effect

On Thursday, 14 July 2016, Begin Daniel <jfd553@hotmail.com> wrote: