Re: Comparing dates in DDL

Re: Comparing dates in DDL

Am 04.01.19 um 18:32 schrieb Rich Shepard:
>> other solution for such 2 fields: you can use DATERANGE, only one field.
>
>   Only if all projects have a known end_date; some don't.

that's not a problem:

test=*# create table projects(duration daterange default 
daterange(current_date,null) check(lower(duration) is not null));
CREATE TABLE


Regards, Andreas

-- 
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Re: Comparing dates in DDL

On 1/4/19 10:26 AM, Rich Shepard wrote:
> On Fri, 4 Jan 2019, Rob Sargent wrote:
>
>> Is the end_date always knowable at record insert?
>
> Rob,
>
>   Not always. Sometimes projects have known end dates, other times the 
> end
> is interminate until it happens.
>
>> CHECK(end_date is null or start_date <= end_date)
>
>   So a default of NULL should be applied, or just allowed to happen?
>
> Thanks,
>
> Rich
>
This is exactly what null is for.  Much preferable to some arbitrary 
value which will confuse analyses.

Comparing dates in DDL

   I have a projects table that includes these two columns:

start_date date DEFAULT CURRENT_DATE,
end_date date
 	CONSTRAINT valid_start_date
 	CHECK (start_date <= end_date),

   1. Do I need a DEFAULT value for the end_date?
   2. If so, please suggest a value for it.

TIA,

Rich

Re: Comparing dates in DDL

On Fri, 4 Jan 2019, Igor Korot wrote:

>>    1. Do I need a DEFAULT value for the end_date?
>>    2. If so, please suggest a value for it.
>
> start_date.day() + 1?

   Thanks, Igor. I did not pick up this syntax when I looked at data types
and their DDL usage.

Regards,

Rich

Re: Comparing dates in DDL

On Fri, 4 Jan 2019, Rob Sargent wrote:

> Is the end_date always knowable at record insert?

Rob,

   Not always. Sometimes projects have known end dates, other times the end
is interminate until it happens.

> CHECK(end_date is null or start_date <= end_date)

   So a default of NULL should be applied, or just allowed to happen?

Thanks,

Rich

Re: Comparing dates in DDL

On Fri, 4 Jan 2019, David G. Johnston wrote:

> The is null expression is redundant since check constraints pass when the
> result is unknown.

David,

   I wondered about this since NULL can be missing, unknown, or otherwise
defined. Are there benefits to allowing an empty value in that column when
checking that it's later than the start date rather than explicitly setting
a default date after the start date?

Regards,

Rich

Re: Comparing dates in DDL

On Fri, 4 Jan 2019, Andreas Kretschmer wrote:

> no, you can use NULL, for instance. You don't need an explicit value.
> But maybe you want to set the start_date to NOT NULL.

Andreas,

   Yes, I added NOT NULL to the start_date column.

>> 2. If so, please suggest a value for it.
>
> other solution for such 2 fields: you can use DATERANGE, only one field.

   Only if all projects have a known end_date; some don't.

Thanks,

Rich

Re: Comparing dates in DDL

On Fri, 4 Jan 2019, David G. Johnston wrote:

>>   I wondered about this since NULL can be missing, unknown, or otherwise
>> defined. Are there benefits to allowing an empty value in that column when
>> checking that it's later than the start date rather than explicitly setting
>> a default date after the start date?
>>
>>
> I don’t understand the question...

David,

   Understandable. :-)

   Perhaps this is more clear: is there a difference between not specifying a
default end_date value and specifying NULL as the default end_date value?

Regards,

Rich

Re: Comparing dates in DDL

On Fri, 4 Jan 2019, David G. Johnston wrote:

> No. If no default is available and a value for the field is not provided
> the stored value will be null; a default of null is thus also redundant
> specification.

David,

   Thanks for clarifying.

Regards,

Rich

Re: Comparing dates in DDL

On Fri, 4 Jan 2019, Andreas Kretschmer wrote:

>>   Only if all projects have a known end_date; some don't.
>
> that's not a problem:
>
> test=*# create table projects(duration daterange default 
> daterange(current_date,null) check(lower(duration) is not null));

Andreas,

   Thank you. That's a data type I've not before used.

Regards,

Rich

Re: Comparing dates in DDL

On Fri, 4 Jan 2019, Rich Shepard wrote:

>  Thank you. That's a data type I've not before used.

Andreas,

   Thinking more about duration perhaps I'm seeing a problem that really does
not exist: it's a single column for both dates in the table while the UI
needs separate date data entry widgets. Unless I use middleware code when a
project row is first entered I am not seeing how two discrete dates are
combined with sqlalchemy inserts them into the table.

Regards,

Rich

Re: Comparing dates in DDL

On Fri, 4 Jan 2019, David G. Johnston wrote:

> That would be the decision to make - does your toolkit support (or can be
> made to support) the type and are you willing to choose a sub-optimal
> database model because one or more applications happen to do things
> differently?
>
> IMO the daterange datatype is the best type you can choose for the model;
> now you have to figure out and decide where any tradeoffs are and if they
> are worth it given your specific circumstances.

David,

   Thanks for the insights.

Regards,

Rich

Re: Comparing dates in DDL

On Fri, 4 Jan 2019, Jeremy Finzel wrote:

> Another suggestion which hasn’t been mentioned is using ‘infinity’ as the
> end date. I like this because it IMO indicates that the record is clearly
> the current valid record more than null.

Jeremy,

   I believe that infinity was mentioned in this thread.

> But I’m not sure exactly what you are trying to do either. If you are
> creating a new record with changes and closing (ending) the range of the
> original record, then a GIST exclusion index would ensure you have no
> overlapping date ranges for all historical records put together.

   Having overlapping date ranges in a projects table is a Good Thing(TM)
because it means I have multiple, concurrent revenue streams. :-)

Carpe weekend,

Rich

--
I dream of an America where a chicken can cross the road without having its
motives questioned.

Re: Comparing dates in DDL

Hi, Rich,

On Fri, Jan 4, 2019 at 10:53 AM Rich Shepard  wrote:
>
>    I have a projects table that includes these two columns:
>
> start_date date DEFAULT CURRENT_DATE,
> end_date date
>         CONSTRAINT valid_start_date
>         CHECK (start_date <= end_date),
>
>    1. Do I need a DEFAULT value for the end_date?
>    2. If so, please suggest a value for it.

start_date.day() + 1?

Thank you.

>
> TIA,
>
> Rich
>

Re: Comparing dates in DDL

Re: Comparing dates in DDL

Re: Comparing dates in DDL

Re: Comparing dates in DDL

On Fri, Jan 4, 2019 at 2:21 PM Rich Shepard  wrote:
>    Thinking more about duration perhaps I'm seeing a problem that really does
> not exist: it's a single column for both dates in the table while the UI
> needs separate date data entry widgets. Unless I use middleware code when a
> project row is first entered I am not seeing how two discrete dates are
> combined with sqlalchemy inserts them into the table.

That would be the decision to make - does your toolkit support (or can
be made to support) the type and are you willing to choose a
sub-optimal database model because one or more applications happen to
do things differently?

IMO the daterange datatype is the best type you can choose for the
model; now you have to figure out and decide where any tradeoffs are
and if they are worth it given your specific circumstances.

David J.

Re: Comparing dates in DDL

Re: Comparing dates in DDL

Am 04.01.19 um 17:53 schrieb Rich Shepard:
> I have a projects table that includes these two columns:
>
> start_date date DEFAULT CURRENT_DATE,
> end_date date
>     CONSTRAINT valid_start_date
>     CHECK (start_date <= end_date),
>
>   1. Do I need a DEFAULT value for the end_date?

no, you can use NULL, for instance. You don't need an explicit value.
But maybe you want to set the start_date to NOT NULL.

> 2. If so, please suggest a value for it.

other solution for such 2 fields: you can use DATERANGE, only one field.


Regards, Andreas

-- 
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Re: Comparing dates in DDL

On 1/4/19 10:12 AM, Igor Korot wrote:
> Hi, Rich,
>
> On Fri, Jan 4, 2019 at 10:53 AM Rich Shepard  wrote:
>>     I have a projects table that includes these two columns:
>>
>> start_date date DEFAULT CURRENT_DATE,
>> end_date date
>>          CONSTRAINT valid_start_date
>>          CHECK (start_date <= end_date),
>>
>>     1. Do I need a DEFAULT value for the end_date?
>>     2. If so, please suggest a value for it.
> start_date.day() + 1?
>
> Thank you.
>
>> TIA,
>>
>> Rich
>>

Is the end_date always knowable at record insert?

CHECK(end_date is null or start_date <= end_date)