DATE field - should I use default value other than NULL

[rajchahal]

Hi I'm running mysql5 and php5. I've created a table that lists events and has several publish and unpublish DATE fields. (8 fields in total 4 for publish and 4 for unpublish - this is to cover re-occuring events)

 

At the moment, I'm not giving the fields any default values other than NULL.

 

The sql (in PHP) will echo the event if todays date is in between one on the publish pair.

 

i.e. echo if today >= publish1 or today <= unpublish1 and today >=publish2 or today <=unpublish2 etc

This works a treat.

However, when I want to find event that have lapsed I have a few complication. This is because the sql doen't work with NULL so i've created a tempory variable and test this with sql.

 

if (empty ($row['pub2']))

{

$unpub3a ='0000-00-00';

//echo ("no data in 3");

}

 

So going back if I had set sql to default to 0000-00-00 then their would be no need for this tempory variables..

Am I doing this right ?

Thanks

 

[fenway]

What do you mean "SQL doesn't work with NULL"?

[rajchahal]

it gives me the wrong result

in the database i've got 1st occurance set only :

pub1 = 01-07-2007, unpub1 = 02-07-2007

pub2 = null, unpub2=null

 

I wanted to list all archived events (remember each event can have more than 1 occurance i.e pub1, pub2)

Sql was something like this :

(($dateVal > $row['unpub1']) and ($dateVal > $row['unpub2']))

 

It exectued but gave me the wrong results because of Null (i think anyway)

 

 

 

[fenway]

Oh, I see... you mean your query, not "SQL" in general.

 

Yes, NULL aren't fun to use for date ranges.

 

DATE supports '1000-01-01' to '9999-12-31' -- if you use these values (or 0000-00-00, depending on your sql mode), instead of NULLs, then your queries will work just fine -- besides, it makes the data and index smaller anyway .

[rajchahal]

Thanks fenway

 

Just to be sure are you saying that in my database table under 'Default Value' I should use 0000-00-00 and not 'NULL' ?

I thought that would increase the database size?

 

(see attached image - current settings)

 

Thanks again

 

[attachment deleted by admin]

[fenway]

I'm saying that you can specify NOT NULL, and use different values.

[Barand]

Hi I'm running mysql5 and php5. I've created a table that lists events and has several publish and unpublish DATE fields. (8 fields in total 4 for publish and 4 for unpublish - this is to cover re-occuring events)

 

If you normalized your data then, not only would the queries be simpler with a single date pair instead of four, but you'd be free of the null/0000-00-00 problem.

[fenway]

Hi I'm running mysql5 and php5. I've created a table that lists events and has several publish and unpublish DATE fields. (8 fields in total 4 for publish and 4 for unpublish - this is to cover re-occuring events)

 

If you normalized your data then, not only would the queries be simpler with a single date pair instead of four, but you'd be free of the null/0000-00-00 problem.

Yeah, that too.

[rajchahal]

hi

 

I've now realised I need 2 extra pairs, When you say 'If you normalized your data' dou you mean I require another table for date pairs and link this table to the events table?

I was originally going to do this, but wouldn't the query be more complex ? can you give an example ?

 

Thanks