SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2005
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Datetime NULL or 0000-00-00 00:00:00

    Hello,

    If a datetime field is not null, is it better to store a NULL value or set 0000-00-00 00:00:00 instead as a default value.

    I have seen people using both approaches I just donīt know what is the best practice regarding null values in general.

    Thanks

  2. #2
    SitePoint Member
    Join Date
    Jan 2010
    Location
    Texas
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    IMHO, 0000-00-00 00:00:00 is a better choice than NULL. This is because not only null same as no value, but null can also be translated into Unknown Value. So no value plus 2 is 2. But Unknown Value plus 2 is Unknown Value. It's just a good practice to use 0000-00-00 00:00:00.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    that's very sloppy logic, agmay, sorry, no offence

    NULL and 0000-00-00 are ~not~ the same

    my advice is: use NULL if there is no value, and stay away from "dummy values" (you can google this term for more information)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast
    Join Date
    Apr 2005
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb

    Hi...

    If you store NULL, can certain date/time functions work incorrectly?

    Will the same advise apply to INT fields, I mean is it better to use NULL than a 0?

    I googled "null vs dummy values" and "null vs default values" and I came across some interesting posts, however people tend to disagree a little so maybe the choice should be based on a specific need rather than a rule of thumb, but then again you dont always know all the variables and what could go wrong.

    Here are some of these posts as reference:

    http://discuss.joelonsoftware.com/de...ign.4.34163.39
    http://stackoverflow.com/questions/2...default-values
    http://stackoverflow.com/questions/4...erver-database

    Thanks

  5. #5
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by raml View Post
    If a datetime field is not null, is it better to store a NULL value or set 0000-00-00 00:00:00 instead as a default value.
    Well if the field is set as NOT NULL then you can't insert a NULL into the field.

    I still prefer the use of NULL over dummy values.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by raml View Post
    If you store NULL, can certain date/time functions work incorrectly?
    no, they will all work correctly

    which one(s) are you worried about?


    Quote Originally Posted by raml View Post
    Will the same advise apply to INT fields, I mean is it better to use NULL than a 0?
    absolutely, yes

    using 0 can lead to inaccuracies
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Suppose you have a list of students and the scores they earned on a test.

    Here are three marks: 50, 60, 70.

    In addition to those students, one student didn't write the test because he was away. Another one wrote the test but got every answer wrong.

    In your scenario you'd enter 0 for both of them. One of those is inaccurate. Also how would you tell them apart? (i.e. which was an actual score and which a dummy value).

    Now also try to average the test scores, if you have a NULL entered for the student who didn't write the scores then you'd average the four scores (0, 50, 60, 70) and come up with 45 which would be correct. If you used a 0 for the "missing" test result then you'd come up with a class average of (0,0,50,60,70) 36 which would not be correct.

    Just my two cents as an example.

  8. #8
    SitePoint Enthusiast
    Join Date
    Apr 2005
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    Originally Posted by r937
    which one(s) are you worried about?
    Someone mentioned this to me and I quote:

    If you store NULL, certain date/time functions may act incorrectly, forcing you to use COALESCE and do something like:

    DATE_ADD(COALESCE(date_field, "0000-00-00 00:00:00"), INTERVAL 1 DAY)
    Originally Posted by guelphdad
    Just my two cents as an example.
    It makes sense to avoid default values when they have no meaning such as in this case with 0000-00-00 00:00:00

    So unless 0 or 0000-00-00 00:00:00 have a purpose is better to go with NULL.

    Thanks to all for your posts!!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •