Category: Software Design

Should you store dates & times in your database as UTC? It’s pretty standard advice if you’re working in a system that needs to record dates and times from many different time zones. But this advice doesn’t hold true when dealing with dates and times in the future; here are some things you need to consider.

YouTube

Check out my YouTube channel, where I post all kinds of content accompanying my posts, including this video showing everything in this post.

https://www.youtube.com/watch?v=Bxf_HMs7SeQ

Just store UTC? Not really.

You’ll hear/read pretty standard advice to store all dates/times as UTC when storing dates/times in a database. As users enter data into your system, you might convert their specific local date time to UTC and then save that in your database.

For example, say you have a web application where the user must enter a date/time value. They would be specifying it in their local time. This date/time in ISO format would be sent to your Server/API as 2022-08-02T18:00:00-400. This is their local date time with their current time zone offset. Your app would then convert this to UTC, which would be 2022-08-02T22:00:00Z

Then when we need to return data to the client, we fetch it out of the database, which is in UTC, and send that UTC date/time to the client, where it can then convert it to its local date/time.

Sounds straightforward, right?

There are two reasons why I think this is the standard advice given on storing date/times. The first is because you’re standardizing your date/times in your database, which means you can query your data and compare it against the UTC. This allows you to sort, filter, etc., all on a standardized date/time. You won’t need any conversion at runtime to do any sorting and filtering.

The second reason I think this is standard advice is that people are only thinking about date/times that are in the past. And that’s where the most significant problem lies, date/times in the future.

Time zones & Daylight Saving Time

A date/time in the future, converted to UTC as of right now, might change. How? Because Time Zone rules can change. Daylight Saving Time rules can also change.

When you do a conversion from a local date/time as of right now, at this very moment, you’re applying the current timezone and daylight saving time rules. But that’s not to say these rules will always be like this. They change more than you think!

This means that when you convert a local date time that’s in the future to a UTC date/time and then save that to your database, you’re basing on the rules at the time of persistence, not what it would be in the future when that date/time is realized.

So what should you be saving in your database? Let’s walk through an example.

Here is an object with a future date/time with a time zone offset and a location in Toronto, Canada.

As mentioned, if the timezone rules or daylight saving time rules change, this could be incorrect as the time zone offset could be wrong. How would we correct or convert this date time to the correct value? How would we know what the correct value should be? You wouldn’t be able to, as you have no information about what time zone was used. All you have is the time zone offset.

If we were storing UTC instead, we still have the exact same problem. We cannot convert it to the correct time if there are any rule changes.

Time Zone Database

One solution is using the Time Zone Database. If you’re using a library for handling dates and times (eg, JodaTime, NodaTime), it is likely already using the Time Zone Database (tzdb) under the hood. The Time Zone Database contains all time zone boundaries, UTC offsets, and daylight-saving rules.

It also contains a time zone identifier (IANA) that you can use to reference any time you specify a local date. In your database, you can then persist this time zone identifier with your local date/time.

You’ll notice the dateTime property doesn’t have the time zone offset anymore. It’s just the literal date/time the user specified. This is because now we have the IANA time zone name (America/Toronto) that relates to that literal date/time.

As mentioned earlier, the reason to standardize to UTC is so you can query your database effectively for sorting and filtering. We can still do this at the time of persistence in addition to the literal local date/time and time zone name.

So as mentioned, time zone and daylight-saving time rules can change. So what happens if they do? Well, what we can also record is the version of the Time Zone Database. This way, we know what version and rules were used when we converted to UTC.

When rules change, the time zone database will get updated. We can query our database looking for future date/times where the tzdb is an older version and then redo the conversion of the dateTimeLocal using the IANA name to convert back to the correct UTC and update that value in our database.

Just store UTC?

While the standard advice of “just store UTC” can work, it will only work if you’re storing date/time that is in the past. If you need to store date/time in the future, then you want to record the time zone name and the version of the tzdb. And for querying purposes, you’ll want to convert to UTC. And if there ever is a change to any rules, you can update the UTC date/times.

Join!

Developer-level members of my YouTube channel or Patreon get access to a private Discord server to chat with other developers about Software Architecture and Design and access to source code for any working demo application I post on my blog or YouTube. Check out my Patreon or YouTube Membership for more info.

You also might like

• Multi-tenant Architecture for SaaS
• SOLID? Nope, just Coupling and Cohesion

Follow @CodeOpinion on Twitter

Software Architecture & Design

Get all my latest YouTube Vidoes and Blog Posts on Software Architecture & Design

Leave this field empty if you're human:

Can we, as application developers, stop over-engineering software? I hate to use the term engineering even to describe it! I’m guilty of it too. I was writing “clever” code that was overly complex, hard to understand, and hard to change. Here are some of the pitfalls I see and what I think about to guide me down a more straightforward path.

YouTube

Check out my YouTube channel, where I post all kinds of content accompanying my posts, including this video showing everything in this post.

https://www.youtube.com/watch?v=BPSuWUXyA58

“What if” Game

One of the traps I think developers can get into is playing the “what if” game. When we, as developers, gain more insights into the domain we’re working in and start making assumptions. The issue is then we don’t validate those assumptions, which can be widely incorrect. Often this occurs when we discover an edge case we think we need to solve within code. In reality, it’s so infrequent that the business doesn’t need it to be automated or handled by our code. Instead, it could produce some notification or indication to someone in the business, and they can manually resolve it. Not everything needs to be handled in code, especially when the solution is complex and the case of it occurring is very low. This is just a low-value, high-cost edge case. Validate assumptions, and talk with the business! Don’t write complex code when you don’t need to. Stop over-engineering.

Caring too much about tech

Developers love everything code, especially new and shiny libraries, frameworks, and tools. We can lose sight of why we’re writing code in the first place. It’s to solve business problems and create value. Yes, we want to stay up-to-date with the latest technology so we can improve our systems. However, we have to have an equal amount of focus on the domain we’re working in.

Care about the domain as much as the tech.

We’re writing software to solve business problems, and you need to understand your domain. If all you care about is the latest tech, you’ll be adding libraries, frameworks, and tools that are not required, and only making the solution more complex is just over-engineering. Use the tech that best solves the problems that you have. Stop chasing the new and shiny if it doesn’t provide any value to your system.

Don’t roll your own!

It’s unlikely that you’ll genuinely need to write your own library or framework. But how often have you worked in a system with its own web framework, ORM, etc.? Likely you have. Was it documented? Absolutely not. It was some homegrown framework that someone who is no longer at the company wrote years ago, and now you’re stuck with it. I get it; we love tech but all your adding is complexity when you go down the road of writing some fundamental framework that your system depends on.

A good example is writing your messaging library on top of RabbitMQ/AWS SQS/Azure ServiceBus. People often do this without realizing all of the different patterns and concepts they will need to implement, and before long, they have their own homegrown messaging library. Instead, you could use a battle and production-tested messaging library that has all the features & patterns that are commonly required with messaging. Buy, don’t build complexity by over-engineering.

Not Enough Up-Front Design

Not understanding the domain enough will lead to making an overly complex system, likely because of over-engineering. How? This relates to the “what-if” game. Developers will often add layers of abstraction and useless indirection because of “what-ifs.”

“What if we need to do X” or “What if X changes, let’s add Y.”

YAGNI (You aren’t gonna need it). Adding useless layers of abstraction indirection that you’ll never need.

I’m not talking about BIG upfront design in a waterfall-style specification. But exploring the domain, especially in a green-field project by leveraging something lightweight like event-storming. Understand the workflows and the different perspectives of different people within the business.

DRY per Boundary (be explicit)

Don’t repeat yourself has caused a ton of confusion to developers. A great example of DRY gone wrong is when things get overly generic trying to accommodate different use cases, ultimately leading to a ton of complexity.

Entity Services are another example of DRY gone wrong. Entity services are what I call services that operate on a single entity or a set of entities. And that entity only ever exists within that boundary. An example of this is a ProductService in a warehouse system. A ProductService contains all the product information, such as the name, price, cost, quantity on hand, etc. and all the various ways to change that data.

The problem with this is that you do want to repeat yourself. The concept of a product in a warehouse exists in multiple different boundaries. Sales would care about the price; Purchasing would care about the cost; Shipping & Receiving handle the quantity on hand. There is no single Product Entity, but that rather each boundary would have its own concept of a Product exposing the functionality and data that is relevant to it.

Over Abstraction

It seems pretty typical to want to abstract any 3rd party dependency, library or tool. On the surface, this makes sense, and you create your own abstraction so that your application code depends on it rather than the 3rd party. That way, if you ever need to change that dependency, you don’t have to change all your application code, you change the abstraction you created. Sounds reasonable? Not entirely, it can be over-engineering.

It comes down to coupling and managing it. The point of the abstraction is to simplify the underlying concepts best suited for your use case. Creating an abstraction will limit your ability to leverage all the dependency has to offer. Manage coupling! How? Vertical slices and focus on features. Grouping functionality by features allows you to narrow your focus and make localized decisions about what you depend on per feature (or feature set).

Join!

Developer-level members of my YouTube channel or Patreon get access to a private Discord server to chat with other developers about Software Architecture and Design and access to source code for any working demo application I post on my blog or YouTube. Check out the YouTube Membership or Patreon for more info.

You also might like

• Restructuring to a Vertical Slice Architecture
• SOLID? Nope, just Coupling and Cohesion

Follow @CodeOpinion on Twitter

Software Architecture & Design

Get all my latest YouTube Vidoes and Blog Posts on Software Architecture & Design

Leave this field empty if you're human:

Should you delete records from your database or instead use a soft delete? I was recently asked my view on this question by a follower on Twitter. So what’s my answer? Well, I’m not usually thinking about “deleting” anything. Instead, I’m thinking about adding.

YouTube

Check out my YouTube channel, where I post all kinds of content accompanying my posts, including this video showing everything in this post.

https://www.youtube.com/watch?v=lDYXtj95Jy8

Hard Delete

So we’re all on the same page, let me take a step back and discuss deleting data in general. Hard deletes refer to removing the data from the database.

For example, with a table in a relational database, there are two records in the Employee table.

Doing a hard delete would mean executing a delete statement that would physically remove the record from the table. Only one record would be remaining.

Give yourself a high five if you got the movie reference to the remaining employee!

Soft Delete

Typically if you’re using a relational database, you might have foreign key constraints that prevent you from deleting rows to enforce data integrity. You don’t want to have a reference to EmployeID123 in another table when it no longer exists.

Soft deletes solve this issue by often adding a column to indicate if the record/row is “deleted” or inactive.

For example, the table has an IsDeleted column deleted to False.

Instead of hard deleting, records are updated to have IsDeleted set to true.

Similarly, a property indicates if the document/object is deleted if you were working with a document database.

Business Concepts

With a line of business and enterprise-style system, when working in the core part of a system, I’m often not thinking about deleting or soft deleting anything. This is because there are business concepts and business processes that don’t involve deleting data.

People within the domain don’t generally think about “deleting” data, nor would they use the term “delete” when talking about a business process or workflow.

Jumping back to my earlier example of employees, let’s say we were working within an HR system. An employee is a key aspect of the system, and there are different workflows around them. One of those concepts would be their employment history.

When an employee was terminated or quit, we wouldn’t just do a soft delete and mark them as “IsDeleted”. That doesn’t make sense. Employees would have a lifecycle of being hired and their employment terminated.

When an employee’s employment is terminated, other data is likely relevant to that event. When was it terminated, when was it effective, and what was its reason?

The key is that this is a business event that occurred, and we want to capture all the relevant data with that event.

Maybe we represent this in our document/object as a collection of hiring and terminations with the relevant data. It’s possible that an employee is re-hired at a later date and has multiple periods of employment.

Again the key is focusing on the business events that occur. If you think about events as a primary driver of your system, you’ll likely land into thinking about persisting events as a state. This is Event Sourcing. If you’re unfamiliar with the concept of Event Sourcing, check out my post Event Sourcing Example & Explained in plain English.

CRUD

Now I can hear people screaming already; it’s just CRUD! While this can be true on the outer edge of a system that is more in a supporting role. In a large system, you’ll have various boundaries that act purely in supporting roles and are mostly reference data. And yes, this can be CRUD and would likely just soft delete.

However, at the core of your domain, as mentioned, you won’t hear people that understand the domain talk about “deleting.” Almost all events have some type of compensating action that ends the life cycle of a process or “undoes’s” or voids a previous action. Capturing those business events and concepts is key to building workflow within your system. If you’re focusing on CRUD, all business processes, workflows and understanding live entirely in the end-users head. Your system, at that point, is nothing more than a UI to a database with no real capabilities.

GDPR

I can also hear people screaming, but… GDPR! I have to delete the data!

You’re missing the point.

If you need to delete data, delete it. The point isn’t about not deleting data; the point is that if you’re “soft deleting” data, you’re losing information about business concepts/events that have likely occurred as part of a business process or workflow. The events that occurred, and why they occurred can be incredibly valuable in building a robust system that can evolve as requirements change.

Join!

Developer-level members of my YouTube channel or Patreon get access to a private Discord server to chat with other developers about Software Architecture and Design and access to source code for any working demo application I post on my blog or YouTube. Check out the YouTube Membership or Patreon for more info.

You also might like

• Workflow Orchestration for Resilient Systems
• Event Sourcing Example & Explained in plain English
• Decomposing CRUD to a Task Based UI

Follow @CodeOpinion on Twitter

Software Architecture & Design

Get all my latest YouTube Vidoes and Blog Posts on Software Architecture & Design

Leave this field empty if you're human: