From: Lars Gregersen <lars.gregersen@it.dk>
To: pgsql-sql@postgresql.org
Subject: Select when table have missing data
Date: 2 Mar 2007 01:17:33 -0800
Message-ID: <1172827053.569467.79430@p10g2000cwp.googlegroups.com> (raw)
I have a table that contains historical exchange rates:
date_time | timestamp
xrate | real
There is a maximum of one entry per day, but data are missing on
weekends and holidays. For these missing dates I must use the value
from the previous day (e.g. since data for a Sunday is missing I must
use the value from the Friday just before the weekend).
I have two questions:
1) Since historical exchange rates are not supposed to change I
thought about creating a new table with data for all the missing dates
(calculated using some procedural language). However, I would be much
happier if there was a way to do this using SQL in a SELECT statement.
Do you have any hints for this?
2)
I have a number of other tables where data may be missing for
different reasons. These data may be on a daily or an hourly basis.
When a user selects a range of data from e.g. the 1st of January to
the 1st of February I would like to be able to return a full set of
data where all missing entries are returned as NULL. Is there a smart
way to do this using SQL?
Any hints or references you may have on the subject of handling
missing data in time series data are very welcome.
If there is a smarter way to set up tables for handling this type of
data then please enlighten me.
Thanks
Lars
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lars.gregersen@it.dk
Subject: Re: Select when table have missing data
In-Reply-To: <1172827053.569467.79430@p10g2000cwp.googlegroups.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox