0

I want to add a new column created_datetime. This column will automatically be filled with a the current date/time value when inserting a new row.

How should I handle the value for existing rows. Leave null? or set a default?

r_j
  • 111
  • 2
  • 1
    That depends on your use case. Do you want there to always be a value? Are you able to backfill the created_datetime or is setting it to the minimum datetime value sufficient? Otherwise, leaving them null will consume less space on the table but probably a negligible amount unless your table has millions+ of records in it. – J.D. Sep 20 '21 at 11:56

1 Answers1

3

The meaning of NULL is sometimes interpreted differently, but generally speaking it is the value used to symbolize "unknown".

Using a magical token/placeholder value (such as 1900-1-1) is generally an anti-pattern and bad habit. It might be an obvious placeholder in some cases (ex, event times for a system created in the 21st century), but for other cases it might be less obvious (dates of birth for historical figures).

Your use case is the perfect example of when you should allow NULL values in your database. This other answer addresses usage of NULL is much more detail.

I'd suggest adding the new column as null-able, populating the CreateDate when that value is known (ie, new rows), and leaving it NULL when the value is unknown (ie, existing rows)

AMtwo
  • 16,141
  • 1
  • 32
  • 61