SQL Server Timestamps: A Detailed Introduction

By Charles Mahler / Use Cases
Jan 23, 2023

Accurate data is one of the most important aspects of any organizational function. It helps in decision-making and planning, and for most businesses, it also helps in generating revenue.

The data can be anything from a list of clients and products to an inventory list. Nothing comes close to SQL timestamps regarding data accuracy, timeliness, and management.

SQL Server timestamp is a critical component of relational databases, but they aren’t used on a daily basis by most database professionals.

This post will look at different aspects of SQL timestamps and how one can use them in real-world scenarios.

What are SQL Server timestamps?

SQL Server timestamps are a data type used to store a date and time value. The timestamp data type stores a date and time value in the format YYYY-MM-DD HH:MM:SS. The timestamp data type is an 8-byte value representing a date and time value in the YYYY-MM-DD HH:MM:SS format.

SQL Server timestamps allow for the efficient storing and retrieval of data that changes frequently. Timestamps are stored as a sequence of 8 bytes and can be used to store dates and times with a precision of 1/300th of a second.

Timestamps can be used to track changes to data and can be used to efficiently retrieve data that has been updated since a given point in time.

The format of any general timestamp is YYYY-MM-DD HH:MM:SS.NNN.

Prerequisites

When it comes to working with data, one of the most important aspects is ensuring that you’re working with accurate and up-to-date information. This is where timestamps come in.

Timestamps are a way of tracking when data was created or updated, and they can be beneficial in a .NET application.

In this post, we’ll be understanding how you can use SQL Server timestamps with a .NET application. Still, before we get started, it’s important for you to checkmark the following prerequisites to avoid any issues. Please make sure you have the following:

• Understanding of .NET, C#, SQL Server, and Entity framework

• Config for SQL Server (DB name, username, password, and host)

• Visual Studio IDE setup for running the program

You’re ready to begin if you have all of the above. If not, please take care of the prerequisites and then come back. We’ll be here waiting for you.

How do you create a timestamp in SQL Server?

There are multiple ways you can store the current date and time in SQL. A few of them are:

• CURRENT_TIMESTAMP: select CURRENT_TIMESTAMP

• GETDATE(): select GetDate()

The output of the methods shown above will be 2022-11-25 16:00:43.947, and it derives the current time from the operating system of the computer on which the instance of SQL Server is running.

In the example above, you see we’re getting a complete date with time, including nanoseconds. But there are different types, as well, in a timestamp, depending on the data type we use to store. This includes:

• time (hh:mm:ss[.nnn])

• date (YYYY-MM-DD)

• smalldatetime (YYYY-MM-DD hh:mm:ss)

• datetime (YYYY-MM-DD hh:mm:ss:[.nnn])

Let’s see an example of using a timestamp in a C# code where we connect an SQL Server using the Entity framework of .NET.

Example

CREATE TABLE [UserProfiles] (
    [Id] uniqueidentifier NOT NULL,
    [Name] nvarchar(max) NULL,
    [Email] nvarchar(max) NULL,
    [Phone] nvarchar(max) NULL,
    [FirstCreatedTime] datetime NOT NULL,
    [LastUpdatedTime] datetime NOT NULL,
    CONSTRAINT [PK_Users] PRIMARY KEY ([Id])
);

This is the table that we’ll be using, named UserProfiles. Here, FirstCreatedTime and LastUpdatedTime will store the timestamp when the account was created for the first time and the account’s last updated time, respectively.

Here is the Model Class that will be mapped to our UserProfiles table from the database.

public class UserProfile
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public string Phone { get; set; }
    public DateTime FirstCreatedTime { get; set; }
    public DateTime LastUpdatedTime { get; set; }
}

Below is the Context Class to connect the SQL Server.

public class UserContext : DbContext
{
    public DbSet "UserProfile" UserProfiles { get; set; }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"Server=(LocalDb)\\MSSQLLocalDB; Database=User; Trusted_Connection=True;");
    }
}

Run the following Migration commands in the Package Manager Console to sync our models with database tables.

• Add-Migration InitialMigration

• Update-Database

Below, the CreateUser function will be used to create a user for the first time in a database.

public static void CreateUser(UserProfile user)
{
    using (var context = new UserContext())
    {
        context.UserProfiles.Add(user);
        context.SaveChanges();
    }
}

Here, in the UpdateUserName function, we fetch a record using an email and update its name and LastUpdatedTime accordingly.

public static void UpdateUserName(string email, string updatedName){
    using (var context = new UserContext())
    {
        var list = context.UserProfiles.ToList();
        var user = list.Where(l => l.Email == email).FirstOrDefault();
        if (user != null)
        {
            user.Name = updatedName;
            user.LastUpdatedTime = DateTime.Now;
            context.SaveChanges();
        }
    }
}

Finally, the Main function is used to call Create and Update functions simultaneously within a 5-second gap to observe the difference in the timeline stored.

static void Main(string[] args)
{
    var user = new UserProfile
    {
        Name = "XYZ Singh",
        Email = "[email protected]",
        Phone = "9999999999",
        FirstCreatedTime = DateTime.Now,
        LastUpdatedTime = DateTime.Now,
    };
    CreateUser(user);
    Thread.Sleep(5000);
    UpdateUserName("[email protected]", "XYZ Sharma");
}

Output

In the table, as you can see, the timeline stored has a 5-second difference in creating and updating a record.

In the UserProfiles table created above, let’s see an example of if we want to insert a record using SQL Query:

insert into UserProfiles values (NEWID(), 'XYZ', '[email protected]', '9999999999', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

Here, instead of CURRENT_TIMESTAMP, we can also use GETDATE (). Both work absolutely the same in this case.

Understanding the use of timestamps in SQL Server

The timestamp is a database management system’s most essential feature, as it helps prevent concurrency issues. Concurrency occurs when multiple users try to change the same record simultaneously, which can lead to one of them losing their changes.

The timestamp ensures that each user is working on the most up-to-date record version and helps avoid any conflicts.

Benefits of using timestamps in SQL Server

There are many benefits of using timestamps in the SQL Server. Timestamps provide an easy way to track changes to data over time. They can track when data was last updated and by whom. Timestamps can also be used to provide an audit trail of data changes.

Another benefit of using timestamps is that they can be used to help ensure data integrity. Timestamps can track when data was last read and by whom. Companies can use this information to help prevent data corruption.

Timestamps can also be used to track when data was last written and by whom. This information can help ensure that data is not overwritten by accident.

In addition, timestamps can be used to help improve performance. Developers can use timestamps to track when data was last accessed. Developers can also use this information to help determine which data is most likely to be accessed and can be used to help improve caching strategies.

Understanding the need for time series databases

In the event your application heavily relies on timestamps, it’s recommended to try out time series databases.

A time series database is a database that is optimized for storing and querying data that changes over time. Time series data is often stored in a columnar database, making it easy to query for data over time.

Time series databases are often used for application performance monitoring and general data analysis, as they can provide insights into how data changes over time. Time series databases are often used by companies working with IoT data, for tasks like real user monitoring, edge computing workloads, and for storage in SCADA systems.

Some of the benefits of using a time series database include the following:

• TSDBs are explicitly designed for handling time-based data, which means they can be more efficient and accurate than other types of databases regarding timestamp-related tasks.

• TSDBs typically offer a higher level of granularity than other databases, meaning you can more easily retrieve the specific data you need.

• TSDBs often come with built-in tools for analyzing, creating forecasts based on data, and visualizing time-based data, which can help understand your application’s performance over time.

Conclusion

It’s always helpful to know how to get information that you need out of a database, but it’s even more helpful to know why you need it. Timestamps are a great way to get information about when a particular action occurred, which is helpful for auditing or other purposes.

This post has covered an overview of SQL Server timestamps and how they work. We’ve explained what timestamp type to use, as well as when and how to create a timestamp in an SQL Server with a .NET application.

We hope you enjoyed this blog post. Please feel free to leave any comments or questions in the InfluxData community site or Slack channel.