4

When creating database tables I'm often stumped when trying to name my time/date/timestamp fields. Sometimes it's easy, like edit_date and expiration_date... but often it's not as easy. I don't like repeating the table name in the field names because it seems redundant. If you have a posts table, should your timestamp field be post_date? Obviously we shouldn't (can't?) use date/time/timestamp for field names, although sometimes they seem like the best choice if we had the option.

Is there a logical naming convention for naming time-related fields? As this question is probably not definitively answerable I went ahead and wiki'd it from the get-go. Having typed it all out it seems trivial and silly, but hopefully there's someone else out there that has agonized over field names in the past that will sympathize.

Edit:

As a related follow-up question, do you use 'time' or 'date' in your field names when dealing with datetime/timestamp data that has more than just the date component to it (DownloadDate vs. DownloadTime vs. something else)?

Ty W
  • 6,694
  • 4
  • 28
  • 36
  • 3
    I see no reason why you should make this a community wiki (and lose valuable points). Seems like a perfectly legal programming issue to me. – Raj More Mar 03 '10 at 18:14
  • as for putting the table name in the column name: when you join many tables and they all have an `ID` or `Comment` or `Status` column that are in the result set, it would have been nice to have named them `XyzID` or XyzComment` or `XyzStatus`, because you're going to rename it for the result set anyways! – KM. Mar 03 '10 at 18:26
  • Same question is answered in this link: https://dba.stackexchange.com/questions/2677/how-should-i-best-name-my-timestamp-fields – Fariba Jun 16 '20 at 22:07
  • It is answered in this link also: https://dba.stackexchange.com/questions/2677/how-should-i-best-name-my-timestamp-fields – Fariba Jun 16 '20 at 22:09

4 Answers4

6

CreatedOn, CreateDate, ModifiedOn, ModifiedDate

are a few suggestions

Nate Heinrich
  • 1,805
  • 14
  • 14
  • I particularly like `CreatedOn` and `ModifiedOn` as they don't encode the type of the field in its name. You probably wouldn't store a surname in a field called `SurnameString` or `SurnameVarchar` so why should date fields be treated differently? – Chris Kobrzak Sep 12 '18 at 08:58
  • @ChrisKobrzak why would you not encode the type in the name? often that is really useful, specially with industry-standard names that are confusingly mention a wrong type - think 'order-number' which often is not a number but a string. In common language we also have examples - after all it is no "birthedon" but Birthday, or working-hours (and similar in other languages). And your example of names .... 'firstName' or 'givenname' we even encode the position of the name in them. Not to mention that 'name' it self can be thought of as a specific subcategory of strings used for identification. – ABaumstumpf Sep 27 '22 at 06:08
3

The "housekeeping" dates are best named the same in all tables for consistency in the downstream usage, e.g. DateCreated and DateModified. Other dates will be more indicative of their usage and will be particular to an action, e.g. DateInvoiced, DateSold, etc.

Turnkey
  • 9,266
  • 3
  • 27
  • 36
0

DateCreated DateModified PublishTime UpdateTime LastUpdateTime creationTime modificationTime

These properties are also some of the basic properties ... and they should be in all the tables (there can be exception) ... if you are mapping the tables to objects ... these two should be part of the base class (along with UIDs)

jsshah
  • 1,741
  • 1
  • 10
  • 18
0

simply "created", etc. Shorter, no camel case, no underscore. It is frequent enough that it is unnecessary to add more information directly into the name, like "date" or "on" or "at".

Hontvári Levente
  • 1,242
  • 10
  • 16