Should I really save the table booking time in utc?

Given that you said:

... but given the business context, should I really need to be saving the table booking time in utc in the database and worry about converting it to local time on the web client?

... This is not an event that will happen online, its an in-person event for a restaurant.

You should not use UTC for this use case. You aren't recording an absolute point in time that something happened (present or past), but rather you are capturing the intent about when and where a future event is expected to take place.

• When entering the booking date and time, the customer supplies the "when" part of the intent. They supply it in terms of local time zone of the restaurant. Converting it to UTC or some other time zone could possibly lose that intent.

  • Assuming this is a single booking (not a recurrence rule), then in .NET, you can use a DateTime type for this, with its Kind property set to DateTimeKind.Unspecified (which is the default in many cases).

• The "where" part of the intent is supplied by you, when you're setting up the system or adding a new restaurant location. It is better to keep a time zone identifier for each with the restaurant's settings or record in your system, rather than having it implied.

  • In .NET, you should use the string value that corresponds with the Id property of a TimeZoneInfo object. This is either an IANA time zone ID (such as "Europe/London") or a Windows time zone ID (such as "GMT Standard Time"). Depending on implementation approach and version of .NET, you may be able to support either on any platform. (I highly recommend using the IANA time zone IDs if you have a choice.)

There is only one edge case you should consider in this scenario. Ask yourself, is it likely that your restaurants might have bookings near the time of day that a fall-back transition might occur?

• This happens when the local offset from UTC decreases, such as when DST ends (for example BST to GMT in London), or occasionally can happen if a government modifies the standard offset for a time zone.

• If the answer is "no", then you don't need to do anything else. Since most restaurants don't book tables in very late hours when these transitions occur, I suspect this is likely the case for you.

• If the answer is "yes", then you need some way to disambiguate when you place the booking. For example, if someone booked a table for 2021-10-31 at 01:00, you might want to ask them whether their intent was 01:00 BST (the first occurrence) or 01:00 GMT (the second occurrence) - since that is the day the clocks go backward at the end of daylight saving time. A simple boolean can store this intent.

If you're thinking about using a UTC-based DateTime or a DateTimeOffset, I would ask you to consider my point about capturing intent. For example, you might think there's nothing wrong with a restaurant (in London) recording a booking time of 2021-10-30T18:00:00+01:00 in a DateTimeOffset, or its equivalent UTC value 2021-10-30T17:00:00Z in a DateTime. The problem becomes - what if the rules change between when the event was booked and when the event takes place? Perhaps the government ends DST a week early, or stops using it altogether. Such irregular changes have happened many times in various time zones around the world, and it is always possible they can happen again - anywhere. In general, the more amount of time in the future an event is, the less likely we can predict what the offset from UTC will be at that time. (For the example given, you should capture: 2021-10-30T18:00:00 and "Europe/London".)

This issue is exacerbated if you are dealing with recurring events, as then the offset could regularly change just to the start or stop of DST. Though I'm not sure if restaurants book recurring tables, this does happen with recurring meetings, daily alarm clocks, and other cyclical events.

I'll end this just by saying that there will always be those that say "always use UTC", but that advice is shortsighted. There will always be use cases where UTC isn't appropriate, and future scheduling is one of them for sure.

I'll give what may be a slightly unpopular opinion here and say that you don't need to worry about time zones if you're sure you're writing an application that will only ever handle bookings for a single location. You can just set your server's and/or database's time zone to the location's time zone and forget about it.

The one caveat here is if that time zone is subject to daylight savings time. In that case, then I would recommend storing booking times in UTC and converting to the location's time zone when necessary; otherwise, your bookings could be an hour off when the DST switch occurs.

In general, I think storing times in UTC is the best practice, because you know they're always correct, but I do understand that doing time zone conversions is a pain that may not really be worth the effort in some applications.

Well, it may seem not needed for this business case, but when you take a closer look, having that date in UTC may help in the future, for exemple if you need to implement a feature that allow the restaurant owners to sync the bookings into a different system (calendar...).

For "technical" date fields (CreatedDateTime, UpdatedDateTime), you will need to store them on UTC time zone because they belong to your system and not to the booking so you need to have consistency over the different time zone of your clients. So to not confuse your team, I would save all dates in UTC.