42

In a typical web application, dates are retrieved from the database layer strongly typed (e.g. in c# as a System.DateTime as opposed System.String).

When a date needs to be expressed as a string (e.g. displayed on a page), the conversion from DateTime to string is done in the presentation tier.

Why is this? Why is it a bad thing to convert the DateTime to a string on the database tier?

See also the heated debate in chat, and the original question that started all of this.

  • 72
    Let me ask you this: would you then just convert every single type to a string? What makes Date any different? – gardenhead Sep 21 '16 at 21:45
  • 7
    Good question! Please see the heated debate in progress, here. – John Wu Sep 21 '16 at 21:47
  • 8
    Well, it seems pretty obvious that the other guy is wrong, and everyone else is right. Not really a question here – gardenhead Sep 21 '16 at 21:51
  • 7
    Sometimes you need to do date math outside of the database. Considerably harder if all you have is strings. – Eric King Sep 21 '16 at 21:59
  • 14
    Another problem--what kind of string do you need? There are a LOT of ways to represent a datetime as a string. What if i had a database that only returned the current time, represented as # of seconds since the epoch, as a string (for example, the current time is "1474496980"). Would that be useful? Would you want to use a database like that? – riwalk Sep 21 '16 at 22:30
169

Dates, DateTimes and really any other typed object, should generally be left in their properly typed format until the moment you need them to be made into some other type - especially when that type is a human readable form, and especially when it's a lossy/one-way sort of conversion.

Why? Because it is assumed that the type provides you with lots of handy built in functionality, like proper equality testing, addition and subtraction, comparison (greater than, less than), time zone and locale functionality (especially important for anything time-related), etc. If you decide you want to support Americans and the "Month Day[th], Year" format as well as the common British style of "Day Month Year", or the ISO standard of "Year-Month-Day"? What would you do if it was a string and you needed to make that change, parse it back into a Date? Ugh, no thanks - there are many evils and dastardly bugs that way, which are best avoided entirely.

More specifically, you mentioned tiered architecture, which has the presentation layer separate from the data later. This is actually the other big reason to pass a Date as a Date and not a string - because what type of string formatting should the date be put into? English, Chinese, with or without seconds/milliseconds, full month name or digits, will you want to sort on the date field later (sorting on a string demands a certain string format if you want it to work right), etc? This is all a question of presentation - how the user should view the data - and putting that logic anywhere else would limit the advantage of having tiered architecture in the first place. The database should not need to know or care how you'll want to view date in the future.

Finally, nearly all complex applications (which is what tiered architectures are for) that care about time will inevitably use times/dates in many, many different ways, and often at all different levels of the architecture. The typed objects related to times and dates exist for a really good reason: time itself, and especially human calendar systems, are weird and hard. Ultimately times and dates are not strings for the same reason that integers and floating points aren't strings, and it will only make your life harder if you try to pretend they are really just arrays of characters, because they just aren't.

  • 26
    +1 just for using the word dastardly. I agree with your compelling arguments and comprehensive explanation, but that's why I had to log in and vote for you. – Adrian Larson Sep 22 '16 at 3:59
  • 1
    Representing the datetime as seconds since a defined time in the past is also robust among different calendars. E.g. Islamic and Chinese calendars do not use any of the Greogrian months, year number etc. I would consider handling this at database level as bad practice. – rexkogitans Sep 22 '16 at 6:58
  • Dates are often presented as "X days ago". Good luck parsing that back to original value. – Agent_L Sep 22 '16 at 12:08
  • 5
    Let's also not forget the DST change (and other similar) issues. Will "Nov 6, 2016 1:30:26AM" be the first time or the second time that this date and time will have happened? UTC DateTime is at least unique and you can always translate it into the local representation for that time - going back the other way is not always possible. – J... Sep 22 '16 at 15:55
  • 3
    Why? Because it is assumed that the type provides you with lots of handy built in functionality In my opinion this is just secondary. The real reason is that the type tells you what something is. A date is not a string, it just happens to translate easily into a human-readable string. – Doval Sep 23 '16 at 15:21
53

He is saying to use the web server to convert the data time to a string. I am saying do it on the database server and not the web server. Why do you think that is better? - M T Head

I want to know the type.

I really don't care if your database stores information in a string, some ints, or bytes, because, well in the end it's always bytes anyway. That string taking up more space than is needed in your database doesn't bother me. What bothers me is running into dates like this:

11/10/2016

And not knowing if that's the eleventh month or the tenth month.

But it's validated you say. Sure you put it through a validation processes. The date is perfectly correct. But here I am maintaining this thing and all I know is the date is a string. I can't even tell you what date this is.

"Tenth day of November in the two thousand and sixteenth year of our lord."

That's a string. One of our presentations needs it in that format. You said the database converts all dates to strings right? Have fun with that.

The database's job is to store data not present data. Sure, you could do that in strings but then you have to parse it out to make it useful to present for other formats. Storing it in a standard parsed form for whatever type the DB offers gets us as close to ready to present as we can be without having made a presentation decision. It really doesn't matter to me if the DB backs that type with a string or ints or bytes. So long as it knows what it's doing.

But when you don't let the DB know we're dealing with a date and store a date as a string you are prematurely presenting and favoring one presentation over all others. This forces all other presenters to parse before converting. No, the database is not a part of the presentation layer. Don't ask it to be.

Likewise the presentation layer is not part of the database so it's not wise to couple a report to database details. It's far more robust to act on types.

  • This answer addresses storage as strings. However, it doesn't address the common pattern of storing dates in a native date type, but then formatting that to a string in the SQL query, using functions such as CONVERT (T-SQL), nor that a DBMS typically serializes its dates into a string in a configurable format whatever the query. For example: postgresql.org/docs/9.5/static/… – dcorking Sep 22 '16 at 9:16
  • That's a report. It happens after storage. Like converting my birthdate into my age. – candied_orange Sep 22 '16 at 12:04
  • 2
    I merely wanted to encourage you to extend your answer, as the topic of the OP is how "dates are retrieved from the database layer". There is a well-established, though arguably deprecated, pattern, where a report queries the database for formatted and localized date strings. I think the OP would like to hear those deprecation arguments. I know I would. – dcorking Sep 22 '16 at 12:09
  • @dcorking note update. – candied_orange Sep 22 '16 at 12:22
  • +1 adding more water to the mill : just create a system on an installed base that spans multiple timezones where the absolute instant is paramount and see how well you do with string<->timestamp conversions everywhere. Worst, make an extention point for people to create their own plugins and give them timestamps as strings see how consistant these timestamps will be ! – Newtopian Sep 22 '16 at 13:49
19

Locale

Conversion of date to string for presentation purposes requires knowing the user preferences, since the exact same date generally should be displayed differently for users in different locales. Even if you use a single locale in your application, proper behavior should use the locale of the application instead of the database server; and they're not guaranteed to be identical even if at this moment they coincidentally match.

The conversion from a universal date datatype to a locale-specific string should happen in the presentation layer because it's the layer that knows how that conversion should be performed.

  • 3
    For a real life example of a locale mismatch, imagine writing an app for Maine, US users and then it's hosted in Amazon's west coast server farm. ;) This isn't that unlikely a situation, actually. – jpmc26 Sep 22 '16 at 5:53
  • @jpmc26 I don't understand the difference - does Maine use a different date format to the rest of the US? – Pete Kirkham Sep 23 '16 at 9:46
  • 2
    @PeteKirkham Maine and the US west coast use time zones that are 3 hours apart. – jpmc26 Sep 23 '16 at 13:54
  • 1
    Or another real life scenario: Imagine running a server in Switzerland that has to serve clients in four (German, French, Italian, English) different languages with different locales (and slightly different formatting rules). Good luck picking the right locale for your server in such a situation. – Voo Sep 23 '16 at 18:01
  • 1
    @jpmc26 timezones and locales are not the same thing. For example, we have offices in Glasgow Scotland, Atlanta USA and Pune India. Consultants in these offices in turn monitor sites (campuses, hospitals, hotels etc) around the world around the clock. The application database works in UTC but displays times in local time for the site being monitored. The USA consultants have dates localised to MM/DD/YYYY but UK and India locales are DD/MM/YYYY - this depends on locale, not timezone of the site or user. – Pete Kirkham Sep 25 '16 at 20:27
9

This is undesirable for the same reason you wouldn't just want to blindly convert any type to a string as soon as it hits the application tier. There is a high likelihood you're going to want to use that object in some manner before presenting it to the user (if you even do present it to the user). For this specific example, imagine you needed to do some date math on the object. There is no downside to just converting the object to a string precisely before you display it.

4

Types exist for a reason, if they added no benefit then we would not have them and would not use them and we would just have "the type" and everything would be that. They are not only convenient they also add safety and efficiency. The following is a list of why you should always persist types in their native format and not as strings. I used DateTime as an example most of the time but the same principles apply for any primitive type like integers, decimals, binary, etc.


Data Store

Constraints

Type Constraint

Almost all data stores allow to specify constraints on the data, this includes type constraints. One of the main benefits of specifying a DateTime instance is that the stored data will be constrained to that type. It will never be possible to enter anything other than a date time regardless of how the data was inserted into the store. The latter is important for larger systems where there are multiple processes that interact directly with the store. This also includes trying to add faulty dates like February 30, (of any year) as February can only have 29 days on a leap year and 28 days for non leap years.

Validation Constraints

There are also validation constraints which can be implemented in the Data Store like ensuring that a inserted date does not exceed the current date or that a start date occurs before an end date.

Operations

Most data stores also have built in operations / functions like DateAdd or DatePart in MS Sql Server. This allows you to start filtering or selecting specific data while the data is still in the store (not retrieved to the application yet).

Universally Accepted Format

By using the native type other developers or systems that also interact with the store do not have to be informed on the minute details of how that primitive type is stored. This is not the case if that type was stored as a string, then you have to ensure that everyone understands the format of that DateTime string representation. This system becomes fragile when dealing with data that spans locales, regions, and cultures in data origin, the physical location of an application, and the attributes of the end user/system that is interacting with that data. Example: the date format in one country might be MM/dd/yyyy (like in the US) but in another it could be dd/MM/yyyy, detecting that difference becomes almost impossible.

Speed

Speed of retrieval, speed of validation, speed of operations and storage efficiency are all also important factors. Example of the retrieval speed: data stores allow for indexes on columns and these indexes can generally be more efficiently used if the type is stored in its native format.

Application

Data Access

Executing queries against the store becomes simpler using the native type system as developers, once again, do not have to guess as to the storage format. Almost all data store application providers (example: ado.net) provide mechanisms for creating the proper parameterized queries based on the native types passed in. Here is an example of adding the Date part to a ado.net query against a Sql Server store, doing the same with strings would be very cumbersome and fragile/error prone.

command.Parameters.Add(new SqlParameter("@startDate", SqlDbType.Date) {Value = myDateInstance.Date});

Operations

The native types in code also provide for standard operations like the .net type System.Date. Operations are usually mathematical in nature like adding dates, finding the difference between dates, etc. Again, this is not possible to do easily on string types.

Presentation layer

Locale

When a primitive type is finally converted to a string in the presentation layer (the correct location in the program stack to do so) the programmer now has various options to display it correctly according to the context in which it is presented. This context generally consists of the actual meaning of the data and the locale of the user.

Example 1

A datetime instance can be automatically formatted based on the locale of the user.

DateTime.Now.ToString("D", CultureInfo.GetCultureInfo(userContext.Culture))
Example 2

A decimal instance could be representing an amount (currency) and the locale of the user should then also display the amount according to their preference. A c# application might then display the value using

amount.ToString("C", CultureInfo.GetCultureInfo(userContext.Culture))

This could be critical as different cultures display numbers differently. In the US period (.) and comma (,) have the exact reverse meaning as in the Netherlands.

Location

This is very specific to DateTime instances. A date and time represents an occurrence at a specific moment in time but this usually has to be conveyed/presented to the user depending on their own time zone. Example: a DateTime instance 2016-09-21T23:38:21.399Z could be displayed as 9/21/2016 5:21 PM for a user in the Eastern Timezone in the USA. There are many ways to accomplish this but it becomes next to impossible if the date time instance is kept in memory as a string type or in the data store as a string type.


General Rule

The general 2 rules for an application follow when it comes to converting any primitive type to a string representation are as these

  • When accepting input convert that input to the correct primitive type as early as possible in the program stack (usually in the presentation layer)
  • When retrieving data to be displayed, convert that data to the string representation as late as possible in the program stack (again, usually in the presentation layer)
0

There's really nothing wrong with doing this (it's done all the time in services) as long as you are using a non-ambiguous format for your date. By unambiguous, I mean not only is the date clear (e.g. MM/DD vs. DD/MM) but also what timezone it is in. So up-front, if you are going to represent your dates as text, use an ISO format. I strongly prefer UTC based time strings.

Pros:

  • Standards based date/time Strings are portable and easy to understand
  • Often dates in DBs contain a time component. If this is not meaningful to your data, this can actually simplify things.

Cons:

  • Data size. The internal format of a date in a DB will generally use much less space than the String rendering of that date.
  • You are generally going to want to get it into a real date or time structure on the client so there may be extra time in parsing.

If someone said they wanted to do this, I would ask "why?" because there's not really much point to it. If the reason that someone wants to return the date as a String is because they will just display it directly, this is not a good reason to use Strings from the DB.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.