It can be tempting to just default the data type to date/time, because you could always put the time portion as 00:00:00.000 or 23:59:59.999 or the current time. But as your product grows and branches out into more time zones and connects with other software products, you will mightily regret this decision.

As your software begins to exchange information with other software, you can get into situations where the other software only wants a date – not a date/time. And – the other software is located in a different time zone. Or one uses UTC and the other uses local time.

So when the date converts, due to the time zone difference, it can cross midnight. If the receiving software is using a date, instead of a date/time – it chops off the time portion and now you have a date that is a day later or earlier than it was in the originating piece of software.

The time to catch this is when you are designing the database schema in the first place. What kind of a date are you dealing with? Is it some kind of date that determines whether the data falls into one accounting period or another? If so, you want 22 March 2026 to be 22 March 2026 whether the database saving the date is in Hawaii, India or the Shetland Islands. Or is it the point in time when the record was saved to the database? In this case, you want time parts. And when this date is displayed (if it ever is) it will convert to the local time, which is fine. No matter who is looking at this date in any part of the world, they will understand the same point-in-time that the date/time is representing.

If you just set everything to date/time thinking “What the hey it covers all the bases”, then when your accounting application is 10 years old and running all over the globe, you will be angry with your former lazy, schema-designing self.

And save your dates in UTC. It will make everything easier in the long run.

• Uncategorized