How to get DateTime with a UTC datetime and a particular timezone?

Question

My data in SQL database looks like this:

PubDateUTC              PubDateUTCOffset
----------------------- --------------------
2011-08-04 10:02:50.000 +8:00:00
2012-04-23 02:32:25.287 +8:00:00
2010-09-26 04:23:00.000 +8:00:00

What I want is to get a DateTime based on PubDateUTC and PubDateUTCOffset, for example:

2011-08-04 10:02:50.000, +8:00:00 should result in 2011-08-04 18:02:50:000

I have tried with TimeZoneInfo class, but I don't know hot to create a instance of TimeZoneInfo with a string like "+8:00:00", which would be the CreateTimeZoneInfo method below

var tz = CreateTimeZoneInfo(post.PubDateUTCOffset);
return TimeZoneInfo.ConvertTimeFromUtc(post.PubDateUTC, tz);

Is there anyway to do this?

Note: I cannot change the data in SQL database.

Save the dates as DateTimeOffset in database this is very helpfull shall I write for you an example! or you cannot do that now — Bassam Alugili, Sep 10 '13 at 08:07
Is your goal to get the local time ? Or the local time in the timezone specified by the offset ? Or some other timezone ? — Tommy Grovnes, Sep 10 '13 at 08:23
Should "2011-08-04 10:02:50.000, +8:00:00" actually result in "2011-08-04 2:02:50:000"? This is the equivalent time in GMT. — FarmerBob, Sep 10 '13 at 08:24
Had the same thought as Leonid, are you sure the result you are asking for is the right one. — Tommy Grovnes, Sep 10 '13 at 08:26
@LeonidTsybert - the value in the db is *already* in UTC/GMT. If it was stored as `datetimeoffset` then it would be `2011-08-04T18:02:50.000+08:00`, but since it is stored at UTC then the offset applies in the other direction to get back to local time. — Matt Johnson-Pint, Sep 10 '13 at 13:01

score 1 · Answer 1 · answered Sep 10 '13 at 08:03

1

I think you need to use DateTimeOffset class. This thread may be helpful.

http://msdn.microsoft.com/en-us/library/bb546101.aspx

answered Sep 10 '13 at 08:03

Krishna Sarma

1,852
2
29
52

xanatos · Accepted Answer · 2013-09-10T08:22:40.993

1

You could try something like:

var date = post.PubDateUTC.Add(
                TimeSpan.Parse(post.PubDateUTCOffset.Replace("+", ""))
           );

The .Replace("+", "") is because TimeSpan will handle -01:00:00 but will choke on +01:00:00

edited Sep 10 '13 at 08:22

answered Sep 10 '13 at 08:07

xanatos

109,618
12
197
280

score 0 · Answer 3 · answered Sep 10 '13 at 08:32

This works, remove any leading "+" from the offset ( "-" are ok)

var d = new DateTimeOffset(DateTime.Parse("2011-08-04 10:02:50.000"), 
                           TimeSpan.Parse("08:00:00"));

d.DateTime - the time in db = 10:02:50
d.LocalDateTime - the time according to your servers timezone
d.UtcDateTime - the time at GMT = 02:02:50

I'm not sure you want 18:02:50 since it is the time at GMT+16 (+16:00:00), unless of course that is how it's encoded in the db, then just ignore this post :)

score 0 · Answer 4 · answered Sep 10 '13 at 12:58

You should change your post class to have one property:

public DateTimeOffset Published { get; set; }

Then when you read from the database (assuming you have datetime and varchar types in your database):

DateTime utc = DateTime.SpecifyKind(
                       (DateTime) reader["PubDateUTC"], DateTimeKind.Utc);

TimeSpan offset = TimeSpan.Parse(
                       ((string) reader["PubDateUTCOffset"]).Replace("+", ""))

post.Published = new DateTimeOffset(utc).ToOffset(offset);

Then when you need to consume it, you have all of the options of a full DateTimeOffset:

DateTime local = post.Published.DateTime;  // with your offset applied

DateTime utc = post.Published.UtcDateTime; // the original utc value

string s = post.Published.ToString("o");   //  2011-08-04T18:02:50.0000000+08:00

How to get DateTime with a UTC datetime and a particular timezone?

4 Answers4