Thread: 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
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 >
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 otherwiseI don’t understand the question...
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?
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
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.