54

What is your naming convention for DATETIME columns (in my case, using MS SQL Server)

For a column that stores when the row was created CreatedDatetime makes sense, or LastModifiedDatetime.

But for a simple table, let's say one called Event, would you create columns called:

TABLE Event ==================================================== EventID, // Primary key EventDatetime, // When the event is happening EventEnabled // Is the event is on

or these column names

TABLE Event ==================================================== ID, // Primary key Datetime, // When the event is happening Enabled // Is the event is on

If you'd use neither convention: Please provide the column name you would use.

Improve this question

9 Answers 9

Reset to default
103

I normally name DATETIME columns as ACTION_WORD_on: created_on, completed_on, etc.

The ACTION_WORD defines what the column represents, and the suffix (_on) indicates that the column represents time.

Other suffixes (or even prefixes) may be used to specify the data type (_at, _UTC, when_, etc).

Be descriptive. Be consistent.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I like the suggestion to use _on. I normally use _at. But I searched this question because certain action words + _at are ambiguous whether the column is about time or location. _on is more explicitly about time.
17

Why call it EventDateTime, when you don't also use EventIDInt, or EventEnbaledVarchar? Why inlcude the data type in the column name? (My rule of thumb is, if they're accessing data in a table, they better know what the column data types are, 'cause otherwise they don't know what they're working with.)

These days I prefer what I think of as descriptive column names, such as:
CreateDate
DateCreated
CreatedAt
CreatedOn (if there's no time portion)
AddedOn (might be semanitcally more appropriate, depending on the data)

Picking a "label" and using it consistantly in every table that requires that kind data is also a good thing. For example, having a "CreateDate" column in (almost) every table is fine, because then you will always know which column in every table will tell you when a row was created. Don't get hung up with the "but they all have to have unique names" argument; if you're writing a query, you had better know which tables you're pulling each column from.

--Edit--

I just recalled an exception I've done in the past. If a DateTime (or SmallDateTime) column will contain no time portion, just the date, as a "reminder" I'd put "Date" in the column name, such as "BilledDate" instead of "Billed" or "BilledOn". This shouldn't apply when tracking when rows were added, since you'd want the time as well.

Improve this answer

2 Comments

Thanks for your column name examples - This is what I was hoping for, something to keep my mind out of it's naming rut
In response why not to suffix int... DateTime suffix makes sense because it is not only the type, but also the value it represents. Integer only describes the type, not the value it represents. If the table name is event, then its obvious its the event DataTime, similarly to how "name" would be the event name. One wouldnt say event_name. I am not proposing this solution, just explaining what I suspect was the original posters thought.
14

The name should communicate what Business meaning of the data is in the column... "DateTime" is just the Type of the data. Is it when the event happened? when it was recorded? when it was stored in the DB? When the data was last modified?

If it efficiently communicates the meaning of what the column contains, the name is fine. "DateTime" is not fine. "EventDateTime" is only very slightly better. If the table holds events, then any datetime field in the table is an EventDateTime (It records some datetime related to the event). Although if there's only one datetime column in an "Events" table, then EventDateTime implies that it's when the event happened, so that's probably ok.

Choose or select the name so it communicates the meaning of the value...

Given edited question, some suggested names might be:

Occurred, or OccurredDateTime, or OccurredUTC, (or OccurredLocal), or, if events in your business model have duration, then perhaps StartedUtc, or BeganUtc, or InitiatedUtc, etc.

Improve this answer

4 Comments

So in this case what would you use as the column name?
@Peter- read the last sentence: "[Choose] the name so it communicates the meaning of the value." You haven't described the meaning of the datetime column in your table, so we can't recommend a more meaningful column name yet.
Fair comment - I've updated the original question with code comments explaining the meaning of which column.
It may be worth noting that the proposed EventDateTime or DateTime column is actually describing an event of the Event object. So, as suggested above, a column name with "Occurred" in it seems like a great candidate.
4

I prefer to create columns in the second form--although I'd probably want a more descriptive name than Datetime, depending on what its use would be.

Edit: In this sort of situation, I might actually go with a hybrid for that single field, and make it 'EventDate', 'StartDate', or something similar.

Improve this answer

1 Comment

In the case of the event, what would you use instead of 'Datetime' as the column name?
3

Maybe that's just me, but I don't believe you should name your columns with data types, neither replicate the table name all over the fields.

Improve this answer

Comments

2

I would avoid using datatypes for column names (a DATETIME column called Datetime), so I vote for the first option.

Improve this answer

2 Comments

Unique column names are easier to work with. If you have DateTime (as a name) in 10 different tables, you always need to use prefix the column name with the table name in queries etc. So I go for the first option.
@Marc: In a way, it's just a single character difference. It's not that much harder to reference Event.Id compared to EventId. Whether the prefix is attached to the column name, or something you have to do whenever using joins, they both have their advantages/drawbacks.
2

I'd call the column HappensAt, because the row describe an event and the attribute (column) in question details when it happens. As a general rule I try to name my tables with singular nouns and my attributes with phrases that can be used to read, like

tablename(key) columname columnvalue

So I would then be able to say

event(131) HappensAt Dec 21, 2009, 21:30

However this isn't an inviolable rule. I'd still record the date someone was born in a BirthDate column, not a WasBornOn column. You have to bear in mind the common usages of natural language when you name things. Strive for natural usage and the rest will follow. Follow rules blindly and your readers will struggle for comprehension.

Improve this answer

Comments

1

there are many good answers here, so I won't duplicate. But remember don't ever name a column a reserved word!!!

also, I really like the column names in option 1.

Improve this answer

2 Comments

If you ever get stuck having to deal with a database with "impossible" column, table and other names, remember that the SQL standard reserves the double-quote to quote names, and so "THIS COLUMN" is a valid column name. On the few occasions when I've actually used a keyword I've preferred to rename it immediately, so I have no idea whether this extends to SQL keywords. Why even go near the potential source of pain?
It's fine to use reserved words, if they are the best description for the column. Just escape them.
0

I would likely use something like "WhenRaisedUtc".

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.