Storing a time interval
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
You certainly could choose to store as tstzrange, but why not use two
fields?
On 11/8/19 11:57 AM, Michael Lewis wrote:
You certainly could choose to store as tstzrange, but why not use two
fields?
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
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?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
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?
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:
Show quoted text
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?
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