Saving DateTime as string in database

Question

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?

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 stackoverflow.com/questions/4715620/… — Jasen
– Jasen, Commented Dec 12, 2017 at 2:31
You'll thank yourself later if you choose a different schema design, and least for a basic set of fields. — Joel Coehoorn
– Joel Coehoorn, Commented Dec 12, 2017 at 2:47
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
– Joel Coehoorn, Commented Dec 12, 2017 at 2:57
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
– Joel Coehoorn, Commented Dec 12, 2017 at 3:03

Backs · Accepted Answer · 2017-12-12 03:00:52Z

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:

edited Dec 12, 2017 at 3:00

answered Dec 12, 2017 at 2:33

Backs

25k5 gold badges66 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mohammed Noureldin Over a year ago

Could you explain the format of the strings? And how does DateTimeOffset differ from DateTime?

Backs Over a year ago

@MohammedNoureldin added some info and links

Mohammed Noureldin Over a year ago

@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).

Collectives™ on Stack Overflow

Saving DateTime as string in database

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related