Обсуждение: Temporal'sh type sql question

Hi

I have a table that models data in a temporal fashion. I say temporal
fashion as I'm not sure I got this 100% right buy here goes. Basically
each record has a date_from and date_to column. The date_from column
always defaults to the current system date. The date_to column is
either a far future value or the date that that records life-cycle
ended.

For simplicities sake lets say I'm recording some configuration data
that is relatively static. Now there is a need to report on the
changes per day. The temporal data is only storing new events when
they happen, maybe one every 10-15 days.

How can I generate result-set that will carry the previous records
values through till a new event occurred so there is a record for each
"day"?

Cheers
Angus

Hi,

On 6 September 2010 16:18, Angus Miller <all4miller@gmail.com> wrote:
> How can I generate result-set that will carry the previous records
> values through till a new event occurred so there is a record for each
> "day"?

If I understand the problem correct you need to use generate_series()
function (http://www.postgresql.org/docs/8.4/interactive/functions-srf.html).
Just join your data to its result set.

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



--
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com /
Linkedin: http://ru.linkedin.com/in/grayhemp /
JID/GTalk: gray.ru@gmail.com / Skype: gray-hemp / ICQ: 29353802

Thanks Sergery that's what I was after.


1 2010/01/01

On Mon, Sep 6, 2010 at 2:29 PM, Sergey Konoplev <gray.ru@gmail.com> wrote:
> Hi,
>
> On 6 September 2010 16:18, Angus Miller <all4miller@gmail.com> wrote:
>> How can I generate result-set that will carry the previous records
>> values through till a new event occurred so there is a record for each
>> "day"?
>
> If I understand the problem correct you need to use generate_series()
> function (http://www.postgresql.org/docs/8.4/interactive/functions-srf.html).
> Just join your data to its result set.
>
>>
>> Cheers
>> Angus
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
>
> --
> Sergey Konoplev
>
> Blog: http://gray-hemp.blogspot.com /
> Linkedin: http://ru.linkedin.com/in/grayhemp /
> JID/GTalk: gray.ru@gmail.com / Skype: gray-hemp / ICQ: 29353802
>