Comparing dates in DDL

Hi, Rich,

On Fri, Jan 4, 2019 at 10:53 AM Rich Shepard <rshepard@appl-ecosys.com> 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.

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

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

Hi, Rich,

On Fri, Jan 4, 2019 at 10:53 AM Rich Shepard <rshepard@appl-ecosys.com> 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)

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

On Friday, January 4, 2019, Rob Sargent <robjsargent@gmail.com> 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.

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

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

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

On Friday, January 4, 2019, Rich Shepard <rshepard@appl-ecosys.com> 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.

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

On Friday, January 4, 2019, Rich Shepard <rshepard@appl-ecosys.com> 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.

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.

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

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

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

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

On Fri, Jan 4, 2019 at 2:21 PM Rich Shepard <rshepard@appl-ecosys.com> 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.

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

On Fri, Jan 4, 2019 at 4:19 PM Rich Shepard <rshepard@appl-ecosys.com>
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