2

I have a table in my database where I save my user's profile data as Key-Value pair. This pair is always string-string. Example:

| Id |    Name   |  Value  | UserId |
|:--:|:---------:|:-------:|:------:|
|  1 | FirstName |   Some  |   55   |
|  2 |  LastName |   One   |   55   |
|  3 | Birthdate |    ?    |   55   |
| 4  | FirstName | Another |   88   |

I am a bit concerned about saving DataTime data as string. Is saving the DataTime.Ticks.ToString() (and then reconverting it again to DateTime when needed to be used) safe and a good idea? or is there any better approach?

Mohammed Noureldin
  • 14,913
  • 17
  • 70
  • 99
  • 2
    I've worked with a database that saved ticks. Impossible to read and reason about the data without converting it to a human-readable format. Then there's the differences in ticks between JavaScript and .NET. ISO8601 string or DateTimeOffset is better. See https://stackoverflow.com/questions/4715620/storing-datetime-utc-vs-storing-datetimeoffset – Jasen Dec 12 '17 at 02:31
  • You'll thank yourself later if you choose a different schema design, and least for a basic set of fields. – Joel Coehoorn Dec 12 '17 at 02:47
  • @JoelCoehoorn, you mean using own column for birthdate? – Mohammed Noureldin Dec 12 '17 at 02:49
  • 2
    Yes, and other fields like name, in a dedicated table for profiles so you don't repeat the data with additional attributes. What you have is called the Entity-Attribute-Value pattern, and (while it has it's place) it's often considered an anti-pattern to be avoided. This will let you use an actual Date column for the birthdate data. – Joel Coehoorn Dec 12 '17 at 02:57
  • @JoelCoehoorn, sorry for asking this as it is out of question topic, but in this case there will be a lot of columns with null value if the user did not enter values for that. Is having many null columns better than using `Entity-Attribute-Value` pattern? – Mohammed Noureldin Dec 12 '17 at 03:02
  • 2
    Yes, it is better. SQL databases are good at keeping this efficient. You don't have to encode all possible attributes this way... just have a set of core attributes in the table for your profile base data, and additional, less common attributes can be handled in your existing EAV table. – Joel Coehoorn Dec 12 '17 at 03:03

1 Answers1

3

No, it's bad idea. Becase converting to ticks you lose timezone. If you need to store DateTime as a string, convert it with timezone.

Example:

DateTime.UtcNow.ToString("yyyy-MM-ddTHH:mm:sszzz")          // "2017-06-21T14:57:17-07:00"
DateTime.UtcNow.ToString("yyyy-MM-ddTHH:mm:ssK")            // "2017-06-21T14:57:17Z"
DateTimeOffset.UtcNow.ToString("yyyy-MM-ddTHH:mm:sszzz")    // "2017-06-21T14:57:17+00:00"

"zzz" - Hours and minutes offset from UTC.

DateTime date1 = DateTime.UtcNow;
Console.WriteLine(String.Format("{0:%z}, {0:zz}, {0:zzz}", date1));
// Displays -7, -07, -07:00                     

DateTimeOffset date2 = new DateTimeOffset(2008, 8, 1, 0, 0, 0, new TimeSpan(6, 0, 0));
Console.WriteLine(String.Format("{0:%z}, {0:zz}, {0:zzz}", date2));
// Displays +6, +06, +06:00

DateTime vs DateTimeOffset:

Backs
  • 24,430
  • 5
  • 58
  • 85
  • Could you explain the format of the strings? And how does `DateTimeOffset` differ from `DateTime`? – Mohammed Noureldin Dec 12 '17 at 02:37
  • @MohammedNoureldin added some info and links – Backs Dec 12 '17 at 02:49
  • @Backs, sort of, actually I decided to save them as DateTime as you and Joel Coehoorn said. However, this is answer is also valuable. I still need to read about DateTimeOffset before accepting it (I have not had till now time for that). – Mohammed Noureldin Dec 24 '17 at 21:37