Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 9 down vote favorite
3

When I am looking to create some timestamp fields (or other date/time style fields), what is the best way to name them? Should I just put record_timestamp?

share|improve this question
@Garik - timestamp as in date/time or as in generated binary? – Marian May 12 '11 at 20:06
@Marian Timestamp is datatype in sql server and it is equivalent of binary(8), or varbinary(8) for nullable field. It is just an auto-generated number. – garik May 12 '11 at 20:21
@Garik - good, we have the same set of words. I asked because I have seen developers use this name for a datetime field, because it's close to English name for time. See wikipedia for what I mean. Now for a binary generated field I'm always using column name RowVersion. – Marian May 12 '11 at 20:38
1  
Given the ambiguity of the question, the above comments don't match the accepted answer. You should either modify the question and change the accepted answer or create a new question asking about column names for SQL Servers timestamp data type. – Leigh Riffel May 12 '11 at 20:56
@Leigh Riffel it does not matter what product I am using. There is one rule for me for naming is avoiding any reserved words for this product. Any way, I agreed with your concept of naming. Thanks. @Marian By the way, rowversion is reserved word for datatype as we know. It's a good enough if we use "[" and "]" around name, but there is a high probability of getting a naming conflict. – garik May 12 '11 at 22:17
show 2 more comments

6 Answers

up vote 13 down vote accepted

You should describe the purpose of the column and not necessarily the data type. You can include date/time/timestamp in the name, but you should also include the meaning. For example

  • CreationDate
  • StartDate
  • Start
  • End
  • StatusTime
  • Accessed
  • Updated

Adding Date/Time/Timestamp and such at the end is particularly useful when the abscence of the addition would conflict with another column. For example, a table may need both a Status and a StatusTime.

share|improve this answer
1  
+1 sounds like the voice of common sense – Jack Douglas♦ May 12 '11 at 17:51
it makes sense. thanks. – garik May 12 '11 at 20:22
1  
The trouble comes when “CreationDate” is actually a timestamp. Makes it difficult to talk about. – bignose May 13 '11 at 1:25
@bignose - then I suppose it follows that you'd use "CreationTimestamp" or something like "CreatedAt" – Jack Douglas♦ May 13 '11 at 11:33
up vote 5 down vote

I looked at your profile and it says you work with SQL Server and in SQL Server TIMESTAMP data type has nothing to do with date or time and its used to kind of version stamping the rows. This is very useful in identifying which rows have been modified from a given point of time.

If you use TIMESTAMP then you don't have to specify a column name and SQL Server will create a column "TimeStamp" for you. But it is recommended to use "ROWVERSION" data type and in this case you have to specify the column name.

What's the best name for a column like this? It depends, and I would use something like VersionStamp, RV etc... What I consider important is NOT how you name it but are you using that consistently across the board.

HTH

Ref: http://msdn.microsoft.com/en-us/library/ms182776(v=sql.90).aspx

http://msdn.microsoft.com/en-us/library/ms182776.aspx

share|improve this answer
1  
+1 for recommendation (rowversion). I am old-school sql-man, so it was useful. – garik May 12 '11 at 20:10
up vote 5 down vote

I use:

  • created_at
  • updated_at
share|improve this answer
up vote 4 down vote

How about xyz_at for a timestamp and xyz_on for a date field - eg start_at or start_on?

Normally I'd avoid including the data type in the field name - much better if you can infer what you need to know about the type from the name of any field (a field called description is unlikely to be an integer) - but being able to tell the difference between a timestamp and a date is often helpful.

share|improve this answer
up vote 2 down vote

I prefer to use a prefix of DT for date stamps. For example: DTOpened, DTClosed, DTLastAccessed. This lets me list out all the DTxxxx for a quick reference of all date stamps in a given table.

share|improve this answer
up vote 1 down vote

I would used a meaningful prefix and _TSMP as a suffix e.g. CREATION_TSMP or LAST_UPDATE_TSMP

share|improve this answer
4  
−1, the data type shouldn't be part of the name. – bignose May 13 '11 at 1:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.