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?
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
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.
xyz_at for a
xyz_on for a
date field - eg
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.
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.
There is no exact right way to do it, only important thing is to be consistent across your code base and databases so developers are not confused.
Exact naming depends on the language conventions but I would choose one of the following below:
Python, Java, ORACLE/Postgresql created_at, createdAt, CREATED_AT // Time when the record was created updated_at, updatedAt, UPDATED_AT // Time when the record was last modified started_at, startedAt, STARTED_AT // Some other record time Or create_time, createTime, CREATE_TIME update_time, updateTime, UPDATE_TIME start_time, startTime, START_TIME
Pretty much all fields are stored as timestamp, there will some fields required as date but usually they are more related to the business domain and could be named as per coming request
All timestamp fields to be stored in UTC.
I prefer using conventions that already exist.
Unix and programming languages have a widely accepted convention of
mtime for Modification Time
For creation time,
- BSD and Windows use birthtime
- Windows also uses Creation Time
- xstat uses
- ext4 uses
- JFS and btrfs use
otime(don't ask, guessing "origination").
So for me, I pick
crtime for meta data.
For user supplied data, I go with what the field represents. If it's a birthday, I just say
As far as precision, for some it seems to hang them up on too much precision. You can store your
birthdate as a timestamp (after all you were technically birthed at a time of day), but the SQL spec has casts from higher-precision to lower precision so if you're using a decent database this shouldn't be an issue. In your app itself, you can always truncate when needed. That is to say, I would never go
As suggest by @Evan Carroll, go with the existing standards unless you have strong reason to break the pattern.
If this is something new then you can follow any answer which best suits you.
I use *_on and *_by because it helps me to keep it consistent for when and who of the row:
- created_on & created_by - updated_on & updated_by - deleted_on & deleted_by -- soft delete - approved_on & approved_by
It's concise and if used as a standard there's not confusion.
The same comment from timestamps. It's concise and when used as a standard it's not confusing.
For times without dates which is generally used for scheduling more than recording a time something occurred as that would be a timestamp:
Since this is not used nearly as frequently in my experience as
time is sufficient, plus it's short enough.
There was a comment about
_at being used for locations. While I can understand that I haven't worked at a business where locations of events was needed. For something like user's ip information we would just have something
ip_city, etc. For other types of locations we would use
location_id. If you need locations and
_at is confusing then by all means use a different standard.
I personally care more about consistency and having a standard than a particular standard, as long as there's backing for that standard and not something made up randomly.