Postgres Pro
Downloads
Home   >   mailing lists

Thread: Storing a time interval

Storing a time interval

From
stan
Date:
I need to create a table to store terms and conditions for purchase
orders.

Some of the attributes of a PO include payment terms. Quite often these will
be 2 periods associated with these, the first is a period on which if you
pay, you receive a discount, and the 2nd is when payment is due with no
discount. I am thinking of storing these two attributes as time internals.

What is the wisdom as to how to declare the type of these columns?


-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                        -- Benjamin Franklin

Re: Storing a time interval

From
Michael Lewis
Date:
You certainly could choose to store as tstzrange, but why not use two fields?

Re: Storing a time interval

From
Adrian Klaver
Date:
On 11/8/19 11:57 AM, Michael Lewis wrote:
> You certainly could choose to store as tstzrange, but why not use two 
> fields?
> 
> https://www.postgresql.org/docs/current/rangetypes.html

I would lean more to a composite type:

https://www.postgresql.org/docs/11/rowtypes.html

Range types are built around looking for values within the range, 
whereas the OP is looking for two discrete values. The two field method 
you suggest above also encapsulates that.




-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Storing a time interval

From
stan
Date:
On Fri, Nov 08, 2019 at 12:12:59PM -0800, Adrian Klaver wrote:
> On 11/8/19 11:57 AM, Michael Lewis wrote:
> > You certainly could choose to store as??tstzrange, but why not use two
> > fields?
> > 
> > https://www.postgresql.org/docs/current/rangetypes.html
> 
> I would lean more to a composite type:
> 
> https://www.postgresql.org/docs/11/rowtypes.html
> 
> Range types are built around looking for values within the range, whereas
> the OP is looking for two discrete values. The two field method you suggest
> above also encapsulates that.
> 
Thanks folks. 

After thinking over both your sugestions, I beleive teh optimum way to do this
may look like:


CREATE TYPE po_dates AS (
    po_isssued_date        timestamptz,
    discount_last_date     timestamptz,
    net_date               timestamptz
    );

-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                        -- Benjamin Franklin

Re: Storing a time interval

From
Michael Lewis
Date:
CREATE TYPE po_dates AS (
    po_isssued_date        timestamptz,
        discount_last_date     timestamptz,
        net_date               timestamptz
        );

What advantage does combining these three values into a custom composite type give you rather than just storing directly? Are you going to reuse this po_dates type on many tables?

Re: Storing a time interval

From
Steve Baldwin
Date:
I agree with Michael. Another consideration is how the composite type is going to be handled in the DB layer of your processing code (e.g. node, python, ...).  In the scenario you described it seems unlikely you will be either having multiple columns of that type on your PO table, or using that composite type on a different table, so apart from the 'interest' factor, I'm not seeing any practical benefit.  Composite types are also slightly painful in the change they bring to the way you reference them. For example typically you need to surround the outer column in brackets - e.g. (dates).discount_last_date. If you are using an ORM library, does it know how to deal with that?

Steve

On Sat, Nov 9, 2019 at 8:11 AM Michael Lewis <mlewis@entrata.com> wrote:
CREATE TYPE po_dates AS (
    po_isssued_date        timestamptz,
        discount_last_date     timestamptz,
        net_date               timestamptz
        );

What advantage does combining these three values into a custom composite type give you rather than just storing directly? Are you going to reuse this po_dates type on many tables?

Re: Storing a time interval

From
Pankaj Jangid
Date:
Steve Baldwin <steve.baldwin@gmail.com> writes:
> I agree with Michael. Another consideration is how the composite type is
> going to be handled in the DB layer of your processing code (e.g. node,
> python, ...).  In the scenario you described it seems unlikely you will be
> either having multiple columns of that type on your PO table, or using that
> composite type on a different table, so apart from the 'interest' factor,
> I'm not seeing any practical benefit.  Composite types are also slightly
> painful in the change they bring to the way you reference them. For example
> typically you need to surround the outer column in brackets - e.g.
> (dates).discount_last_date. If you are using an ORM library, does it know
> how to deal with that?
>

I faced a similar issue when using Enums with with rust lang. The Diesel
ORM didn't support it directly. Had to struggle with custom code in the
Model layer.

-- 
Pankaj Jangid