CODEWITHSUNDEEP
c#.net.net-core
Mohammed Noureldin
Mohammed Noureldin

Reputation: 16926

Saving DateTime as string in database

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?

Upvotes: 2

Views: 6202

Answers (1)

Backs
Backs

Reputation: 24913

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:

Upvotes: 3

Related Questions