There are several columns in my database that need a “time-stamp” of when something happened. For instance…
So what is the difference between DATESTAMP and TIMESTAMP?? (Because they both have the format ‘0000-00-00 00:00:00’)
Why would I want to use one over the other for my purpose listed above?
TomTees
Really?! :-/
Interesting…
I figured Zero-Length Strings were the lesser of two sins since they won’t blow up SQL statements like Nulls can. :scratch:
besides, zero-length strings don’t work for DATE or DATETIME columns
That’s true.
Okay, r937, so let me try and summarize your views on the topic of Nulls vs Zero-Length Strings.
[INDENT]- You strongly dislike Zero-Length Strings based on years of experience.
You are not crazy about Nulls, but can live with them.
Zero-Length Strings are worse than Nulls.
A good DB design should strive to have values in fields.
Nulls are permissible, but should be minimized.
Using Default values (other than Zero-Length Strings) is a good thing.
Using Default values (other than Zero-Length Strings) helps reduce Nulls.
Sometimes having a Null value is completely acceptable.
Here are some fields that might be good candidates for Nulls…
[INDENT][COLOR=“Green”]Address2 —> May not apply
Middle Initial –> May not apply
DateShipped —> Order hasn’t shipped yet
Shipping Tele # —> May not apply
AcceptTermsYN —> User may have not made any selection
UpdatedOn —> Record has not been updated
[/COLOR][/INDENT]
Hopefully that is fair assessment of what has been discussed, and it is fairly consistent with what r937 thinks and would do?! :-/
Please let me know what you think in more than one word (i.e. surprise me with a decent response that is at least a few sentences if not more)!!
Thanks,
TomTees
Over at dbForums you said…
I remember you talking about splitting tables out to avoid Nulls, although that would apply (really) with Time-Stamps.
You didn’t stay you hate Nulls, but you seem to want to avoid them if at all possible. (And a lot of developers feel that way.)
Then what would you recommend?
TomTees
Not what you said before.
So, is it okay to set up my table like this…
CREATE TABLE test (
id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
created_on DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
updated_on DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (id)
);
(This syntax wasn’t working before, but now it is?!)
My thinking is that ‘0000-00-00 00:00:00’ is better than a NULL or ‘’ but basically signifies the same thing (i.e. nothing was inserted or updated yet).
Is that acceptable?
And what are your personal feelings, r937, on using DATETIME vs. TIMESTAMP??
TomTees
[quote=“TomTees,post:4,topic:64676”]
Not what you said before.[/quote]if you would be so kind as to look up where i said something that sounds like i don’t like nulls, i would be happy to retract it or explain in more detail what i meant
not to me, no
for my own use? i thought i already said i only use DATETIME
who, me? i thought i just said i wouldn’t want to 
sorry, you got that wrong
i hearts nulls
you want to know how i think?
i think you should forget about triggers and just set the created_on and updated_on columns in your INSERT and UPDATE statements
one remark in that article bears repeating:[indent]The MySQL timestamp is a strange beast indeed. It can only store DateTime values within the Unix epoch
[/indent]
i’ve never needed the automatic TIMESTAMP updating feature, using DATETIME instead and explicitly setting the value of columns like “lastupdated” in the SQL
why explicitly update, and not use TIMESTAMP or a trigger?
because there are instances where you do ~not~ want the “lastupdated” column automatically updated, such as mass changes to the table
the only difference is that TIMESTAMPs can be updated automatically, or by default
it’s rather complicated, and you should really read the manual for more details…[indent]
TIMESTAMP columns are displayed in the same format as DATETIME columns. In other words, the display width is fixed at 19 characters, and the format is ‘YYYY-MM-DD HH:MM:SS’.
TIMESTAMP values are converted from the current time zone to UTC for storage, and converted back from UTC to the current time zone for retrieval. (This occurs only for the TIMESTAMP data type, not for other types such as DATETIME.) By default, the current time zone for each connection is the server’s time. The time zone can be set on a per-connection basis, as described in Section 9.6, “MySQL Server Time Zone Support”. As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable.
The TIMESTAMP data type offers automatic initialization and updating. You can choose whether to use these properties and which column should have them:
•For one TIMESTAMP column in a table, you can assign the current timestamp as the default value and the auto-update value. It is possible to have the current timestamp be the default value for initializing the column, for the auto-update value, or both. It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.
•Any single TIMESTAMP column in a table can be used as the one that is initialized to the current date and time, or updated automatically. This need not be the first TIMESTAMP column.
•If a DEFAULT value is specified for the first TIMESTAMP column in a table, it is not ignored. The default can be CURRENT_TIMESTAMP or a constant date and time value.
•In a CREATE TABLE statement, the first TIMESTAMP column can be declared in any of the following ways:
■With both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses, the column has the current timestamp for its default value, and is automatically updated.
■With neither DEFAULT nor ON UPDATE clauses, it is the same as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.
■With a DEFAULT CURRENT_TIMESTAMP clause and no ON UPDATE clause, the column has the current timestamp for its default value but is not automatically updated.
■With no DEFAULT clause and with an ON UPDATE CURRENT_TIMESTAMP clause, the column has a default of 0 and is automatically updated.
■With a constant DEFAULT value, the column has the given default and is not automatically initialized to the current timestamp. If the column also has an ON UPDATE CURRENT_TIMESTAMP clause, it is automatically updated; otherwise, it has a constant default and is not automatically updated.
[/indent]
I agree it is a fairly common mindset.
And I thought you were in that camp.
6th normal form (or whatever the normal form number is which tries to eliminate nulls completely) is, for me, too messy
That’s good!
when i get asked generic questions about SQL or databases, i usually try (not always successfully) to explain best practice, even if i don’t always follow best practice myself
a little later on in that dbforums thread, you asked me to clarify:to which i replied “no, i am not”
I remember that, but it still sounded like that you were pretty averse to Nulls since you clearly “abhor” Zero-Length Strings.
i would most wholeheartedly recommend NULL instead of a dummy value
Okay.
Why is it that you find Zero-Length Strings to be even worse than Nulls??
And back to the original focus of this thread…
How would I define my table so it is consistent with how YOU feel and what YOU would do??
If I use a trigger for Inserts, then I could take out the NOT NULL from “created_on” since it would (in therory) always have a value.
After the record is created, a NULL “updated_on” would simply signify that a record was created but has yet to be updated.
And I could also create a second trigger for Updates, so that the field would not have a NULL after it was updated.
So the DDL would look like this…
CREATE TABLE test (
id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
created_on DATETIME
updated_on DATETIME,
PRIMARY KEY (id)
);
What do you think, r937?
I’m still learning all about MySQL, Timestamps, Nulls, and how YOU think?! 
TomTees
indeed i did, with the disclaimer that it was a complex issue
note that i did ~not~ say that ~i~ try to avoid nulls, just that “one rule of thumb is that every column should be NOT NULL” which is a fairly accurate expression of one of the core concepts in data modelling (that optional data should be modelled with separate relations, i.e. tables)
6th normal form (or whatever the normal form number is which tries to eliminate nulls completely) is, for me, too messy
when i get asked generic questions about SQL or databases, i usually try (not always successfully) to explain best practice, even if i don’t always follow best practice myself
a little later on in that dbforums thread, you asked me to clarify:
So it seems that you are in the “Nulls should never exist gorilla group”?!
to which i replied “no, i am not”
i would most wholeheartedly recommend NULL instead of a dummy value
I much prefer to have the database handle the update time when the row was updated and manually setting the created time when creating new rows. I use timestamps rather than date times. As far as null goes I much prefer using null over an empty string or 0. Using null seems more politically correct when something doesn’t have something such as; a related row in separate table or no data.
Don’t follow you on that one.
If you are changing all rows that have “Miss” or “Mrs.” to “Ms.” then that qualifies as an update and you would want to capture it.
Anyways, I can see when you are doing some mass update, but don’t want it to affect timestamps.
i dunno – i never tried
please let us know the results of your tests
Here we go again… 
I already know the answer. (And I bet you do too.)
But before doing that, how about you help me figure out what DataTpe to use and the SQL syntax?!
I am leaning toward using DATETIME because it doesn’t run out in 2037 and supposedly performs better in queries.
This is a test query I wrote but it doesn’t work…
CREATE TABLE test (
id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
created_on DATETIME NOT NULL DEFAULT ‘0000-00-00 00:00:00′,
updated_on DATETIME NOT NULL DEFAULT ‘0000-00-00 00:00:00′,
PRIMARY KEY (id)
);
I am trying to mimic what the author did in the article I posted above, however, I’d like to use DATETIME instead of TIMESTAMP. Apparently my syntax is wrong.
Since I know you don’t like Nulls, r937, I figured I would put in ‘0000-00-00 00:00:00′ to signify that the field has not been assigned a timestamp yet.
TomTees
Seriously, though…
TomTees
I’m not sure if this is what you mean but anyway:
`created_on` timestamp NOT NULL default CURRENT_TIMESTAMP,
okay, yeah, sure, whatever
here’s an explicit change –
UPDATE sometable
SET lastupdated = CURRENT_TIMESTAMP -- this logs the change datetime
, somecolumn = 'foo' -- this is the column being changed
WHERE myid = 937 -- this qualifies which rows(s) to update
here’s a change for which i do ~not~ want to log the lastupdated datetime –
UPDATE someothertable
SET salutation = 'Ms' -- this is the column being changed
WHERE salutation IN ( 'Miss','Mrs.' ) -- this qualifies which rows(s) to update
why do i not want the datetime of this change logged? because it would obliterate the individual lastupdated datetimes of all the rows being affected
i dunno – i never tried
please let us know the results of your tests
Glad you mentioned this, because I have been thinking about that today.
It seems very short-sighted to use TIMESTAMP knowing that we will have another Y2K type issue in 2037?!
So using DATETIME is looking more attractive.
i’ve never needed the automatic TIMESTAMP updating feature, using DATETIME instead and explicitly setting the value of columns like “lastupdated” in the SQL
why explicitly update, and not use TIMESTAMP or a trigger?
What do you mean by “explicitly update”?
So I think we both agree that TIMESTAMP isn’t so hot-to-trot after all, but what is wrong with using DATETIME and a Trigger instead?
because there are instances where you do ~not~ want the “lastupdated” column automatically updated, such as mass changes to the table
So can’t you just disable your Trigger while doing maintenance like that and then switch it back on when you are done?
TomTees
I didn’t find the old website article I was looking for, however, I found something even better!!
Below is a an excellent article discussing how to use TIMESTAMP to update CreatedOn and UpdatedOn columns and the Pros and Cons of this approach.
It then transitions into a really good piece on using Triggers and how they are actually much safer.
Here is the article from Richard Quinn’s blog…
[COLOR=“RoyalBlue”][b]MySQL: tracking row creation and row change events with timestamps[/b][/COLOR]
I am curious what people think about using Triggers versus built in datatypes like TIMESTAMP.
Even though Triggers are a little extra work, they seem like the better approach.
What do you think?
TomTees
No, that is only part of it.