Comparing dates in DDL

classic Classic list List threaded Threaded
21 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Comparing dates in DDL

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?
   2. If so, please suggest a value for it.

TIA,

Rich

Reply | Threaded
Open this post in threaded view
|

Re: Comparing dates in DDL

Igor Korot
Hi, Rich,

On Fri, Jan 4, 2019 at 10:53 AM Rich Shepard <[hidden email]> 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
>

Reply | Threaded
Open this post in threaded view
|

Re: Comparing dates in DDL

Andreas Kretschmer-3
In reply to this post by Rich Shepard


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


lup
Reply | Threaded
Open this post in threaded view
|

Re: Comparing dates in DDL

lup
In reply to this post by Igor Korot

On 1/4/19 10:12 AM, Igor Korot wrote:

> Hi, Rich,
>
> On Fri, Jan 4, 2019 at 10:53 AM Rich Shepard <[hidden email]> 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)


Reply | Threaded
Open this post in threaded view
|

Re: Comparing dates in DDL

Rich Shepard
In reply to this post by Igor Korot
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

Reply | Threaded
Open this post in threaded view
|

Re: Comparing dates in DDL

David G Johnston
In reply to this post by lup
On Friday, January 4, 2019, Rob Sargent <[hidden email]> wrote:
CHECK(end_date is null or start_date <= end_date)

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

David J.

Reply | Threaded
Open this post in threaded view
|

Re: Comparing dates in DDL

Rich Shepard
In reply to this post by lup
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

Reply | Threaded
Open this post in threaded view
|

Re: Comparing dates in DDL

Rich Shepard
In reply to this post by David G Johnston
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

Reply | Threaded
Open this post in threaded view
|

Re: Comparing dates in DDL

Rich Shepard
In reply to this post by Andreas Kretschmer-3
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

Reply | Threaded
Open this post in threaded view
|

Re: Comparing dates in DDL

David G Johnston
In reply to this post by Rich Shepard
On Friday, January 4, 2019, Rich Shepard <[hidden email]> wrote:
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?


I don’t understand the question...

David J. 
Reply | Threaded
Open this post in threaded view
|

Re: Comparing dates in DDL

Rich Shepard
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

Reply | Threaded
Open this post in threaded view
|

Re: Comparing dates in DDL

David G Johnston
On Friday, January 4, 2019, Rich Shepard <[hidden email]> wrote:
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?


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 J.

lup
Reply | Threaded
Open this post in threaded view
|

Re: Comparing dates in DDL

lup
In reply to this post by Rich Shepard

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.

Reply | Threaded
Open this post in threaded view
|

Re: Comparing dates in DDL

Rich Shepard
In reply to this post by David G Johnston
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

Reply | Threaded
Open this post in threaded view
|

Re: Comparing dates in DDL

Andreas Kretschmer-3
In reply to this post by Rich Shepard


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


Reply | Threaded
Open this post in threaded view
|

Re: Comparing dates in DDL

Rich Shepard
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

Reply | Threaded
Open this post in threaded view
|

Re: Comparing dates in DDL

Rich Shepard
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


Reply | Threaded
Open this post in threaded view
|

Re: Comparing dates in DDL

David G Johnston
On Fri, Jan 4, 2019 at 2:21 PM Rich Shepard <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: Comparing dates in DDL

Rich Shepard
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

Reply | Threaded
Open this post in threaded view
|

Re: Comparing dates in DDL

Jeremy Finzel


On Fri, Jan 4, 2019 at 4:19 PM Rich Shepard <[hidden email]> wrote:
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


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.

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.

Thanks,
Jeremy 
12